The XLOOKUP Function
Chahine Atallah
Senior Electrical Engineer | PMP | Project Coordinator | Excel Expert | Power BI | Power Query | Data Analysis
Many excel users face a problem when extracting data when using VLOOKUP /HLOOKUP Functions, as these functions have the following limitations
1. It only searches in First Column/Row, so sometimes you need to rearrange your Data
2. It extracts columns only to the right of the Lookup Column or Below Lookup Row
3. It only extracts data from one column/one row which is specified by a number
4. Column index/Row index is a number, where you might simply count it wrong and get wrong results
5. Lookup is done from Top to bottom & only first match is extracted
These limitations are solved by the new XLOOKUP function (currently available to office 365 users), XLOOKUP has a very simple syntax (Lookup value, Lookup array/range, return array/range, if not found, match type, search mode), I highly Recommend you learn & practice this function
Ill explain them one by one,
1. lookup value is the value that you are searching
2. Lookup array is the array in which the search is to be performed
3. Return array is the array from which the data to be extracted
4. If not found, in case your lookup value is not found in lookup array, you can specify some text/number to be returned instead of #N/A
5. Match type, it is a number (-1,0,1,2), below list explains (from Microsoft support page), most of the times you will use only 0 or exact match
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
6. Search mode, it is a number (-2,-1,1,2), you can simply ignore it, but below are the details which are self explanatory (from Microsoft support page)
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.
Below snapshot for extracting customer name from sales table and the lookup is Customer code, you can see in the example that the lookup column is not the first column as opposed to using VLOOKUP where lookup always should be first column & Return Array is also to the left of the Lookup array , which is not possible in VLOOKUP
Start
Final
