It is often common that users refer to different tables or sources during entry in Excel. For instance, Product Sales report would refer to the product price list using unique product code. This referencing ensures fast data entry and minimize error.
If you are a novice user, you probably know VLOOKUP as use Excel function used for referencing. VLOOKUP function works by looking in a specific column of a table for the value you specify and then looks across the appropriate number of columns (which you specify) and returns whatever value it finds there.
However, VLOOKUP has some limitations and that why the Advanced users hardly use it. VLOOKUP function cannot look to the left of the referenced column and it also breaks if someone inserts a new column in the middle of the lookup table. The advanced users often combine two other functions; MATCH & INDEX to manage these limitations.
On 28 August 2019, Microsoft Excel team officially resolved the VLOOKUP limitations by releasing a new function called XLOOKUP.
The XLOOKUP function searches a range or an array and returns an item corresponding to the first match it finds.
In addition to solving the limitations of VLOOKUP, XLOOKUP has a number of other benefits:
- It can find the first or last match of the lookup value
- It has possible speed improvement as you can the two columns needed and not the whole lookup table
- XLOOKUP can also return a range of column value instead of one column only
The XLOOKUP Syntax is:
XLOOKUP(Lookup_Value, Lookup_Array, Results_Array, [Match_Mode], [Search_Mode])
1. lookup_value – The lookup value
2. lookup_array – The array or range to search
3. return_array – The array or range to return
4. [match_mode] – Specify the match type:
- 0 – Exact match. If none found, return #N/A. This is the default.
- -1 – Exact Match or Next Smaller.
- 1 – Exact match or next larger.
- 2 – A wildcard match.
5. [search_mode] – Specify the search mode to use:
- 1 – Perform a search starting at the first item. This is the default.
- -1 – Perform a search starting at the last item.
- 2 – binary search, first to last (requires lookup_array to be sorted).
- -2 – binary search, last to first (requires lookup_array to be sorted)
XLOOKUP Replaces VLOOKUP
Assuming you have a lookup table in K3:N6 from which you want to look for the product price.
You can use XLOOKUP in C3, to pick the product price from the table with formula =XLOOKUP(A3,L3:L6,M3:M6,0)
The L3:L6 is the lookup array.
The M3:M6 is the results array.
Similarly, we can get the product name in B3 using function XLOOKUP(A3,L3:L6,K3:K6,0)
With XLOOKUP, you can look in lookup column for a search term and return a result from the same row in any column, regardless of which side the return column is on. The above example enables referencing to the left of the lookup value column L3:L6. A major limitation of VLOOKUP.
Multiple Columns of XLOOKUP
XLOOKUP can also return multiple column values instead of one through the Dynamic Array. You would have used two VLOOKUP If you want to look for product code and return both the matching price and tax rate from the table. XLOOKUP now allows you to write one formula and it return value from two columns.
First, go to cell C3 and enter formula =XLOOKUP(A3,L3:L6,M3:N6,0)
Hit Enter key and the function will insert value in the two columns.
XLOOKUP is able to return an array with multiple items, which allows a single formula to return both Unit Price and Tax rate.
A temporal limitation
Due to Microsoft’s policies, XLOOKUP feature may not be available everyone now. However, if you have Office 365 subscription, then enjoy the XLOOKUP.
This article is just a tip of what XLOOKUP function can do. The rest depends on your imagination.