How to use XLOOKUP in Excel to change the way you use data

by Ecommerce success
xlookup in excel

You’ve got a list of product IDs, but you want to know the cost price so you can calculate the profit. The old way is searching each one manually with copy/paste or thinking your crafty with VLOOKUP and your counting of columns. STOP. Its time to catchup and start using XLOOKUP to make things easier.

Want to see it in action? Check out our Tiktok video.

What is XLOOKUP?

XLOOKUP is the cooler younger sibling of the VLOOKUP function, and removes the complexities and limitations of its predecessor. It can return any value, provide default values and mange not found values.

=XLOOKUP (lookup, lookup_array, return_array, [not_found], [match_mode], [search_mode])

Definitions of XLOOKUP

lookup_value Required*The value to search for
*If omitted, XLOOKUP returns blank cells it finds in lookup_array.   
lookup_array RequiredThe array or range to search
return_array RequiredThe array or range to return
[if_not_found] OptionalWhere a valid match is not found, return the [if_not_found] text you supply.If a valid match is not found, and [if_not_found] is missing, #N/A is returned.
[match_mode] OptionalSpecify the match type:0 – Exact match. If none found, return #N/A. This is the default.-1 – Exact match. If none found, return the next smaller item.1 – Exact match. If none found, return the next larger item.2 – A wildcard match where *, ?, and ~ have special meaning.
[search_mode] OptionalSpecify the search mode to use:1 – Perform a search starting at the first item. This is the default.-1 – Perform a reverse search starting at the last item.2 – Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned.-2 – Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned

How to use XLOOKUP?

Lets look at a simple example of where we have a list of Item Ids and we want to know what the cost price is, so that we can work out the profit.

xlookup example in excel

In cell C2, we want the cost price for itemID: 2, so we insert the formula XLOOKUP:

xlookup example in excel

As we are doing a simple match, we will focus on the first three inputs on lookup_value, lookup_array and return_array. We will be looking up the value of A2 in column E, and if found, return the same line value in column F.

Simple.

=XLOOKUP(A2,E:E,F:F)
xlookup example in excel

The end result once we drag it across all our item IDs with all the selected costs using the XLOOKUP function.

xlookup example in excel

For other tips and tricks that are useful for your business, click here!

Related Posts

Leave a Comment