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 Required||The array or range to search|
|return_array Required||The array or range to return|
|[if_not_found] Optional||Where 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] Optional||Specify 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] Optional||Specify 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.
In cell C2, we want the cost price for itemID: 2, so we insert the formula XLOOKUP:
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.
The end result once we drag it across all our item IDs with all the selected costs using the XLOOKUP function.
For other tips and tricks that are useful for your business, click here!