Hello All. In this tutorial let’s look into Xlook up function.
Xlookup is a new function introduced in Excel 365 only which would potentially replace the Vlookup / Hlookup functions. Xlookup searches the range or an array for a match and return the appropriate value from the 2nd array or the return array. Xlookup, in addition to approximate and exact matching in the traditional lookup functions, also support wildcard for partial matches.
Syntax: =Xlookup(lookup_value, lookup_array, return_array, [not_found],[match_mode],[search_mode])
Lookup_value = the value we are looking for
Lookup_arrray = the array where the lookup_value is being searched
Return_array = the array where the appropriate value is picked against the searched value
[not_found] = optional value to be specified when there is 0 search results
[match_mode] = 0 -> exact match; 1 -> exact match or next larger; -1 -> exact match or next smaller; 2 -> wild card
[search_mode] = 1 -> search from first; -1 -> search from last; 2 -> binary search ascending; -2 -> binary search descending
Let’s start off with a simple example and then explore how useful xlookup can be in situations
In the below table, let’s take the serial number as the lookup value and need the total to be returned.
We use the formula =xlookup(K4,B4:B8,G4:G8)
Here, K4 is the lookup value and B4:B8 is the lookup array and G4:G8 is the return array.
One may argue how this is different from traditional vlookup. In the vlookup function, the lookup value has to be in the left most column of the lookup table, else it wont here. Here, in Xlookup, there is no condition like this. In the same table above, one can have Name as the lookup value also (which is not the left most column of the table).
In this example, let’s see how Xlookup returns multiple values (from different column ranges) based on a single lookup_value.
Let’s say we need all the details about an employee like the first/last name, department, shifts, last year rating – all this in a single formula based on a single lookup value
If you notice, the formula is written on C13, however, the values returned are 5 columns that are highlighted by a light blue box above. Also note the return array in the formula is not a single column but C3:G7 that is combination of 5 columns.
In this example, let’s look at how to use nested Xlookup. We are using the same table used in 1st example – we are just making the formula complex. We need to lookup 2 x values – serial number and the subject and return the values accordingly i.e, the formula should return the score of a particular subject based on the serial number and the subject that that’s been looked upon.
Here, the inner lookup will first retrieve all the column values for 1st value (serial number 103). Then the result will serve as the return array for the outer lookup which looks for the 2nd value i.e., Maths.
Example no. 4
In this final example, let’s use Xlookup to check multiple look up values at once. In the table below is the menu of an online learning platform which trains its students on multiple streams with an option to choose the main subject and the add on. Here, we use Xlookup to combine the stream, main subject and the add-on to get the cost for the combination:
I hope you liked this tutorial. Please provide us with your valuable feedback to improve the content. Thanks for reading.