In a prior post, I talked about using IndexMatch to do a VLOOKUP to the left. It's a handy trick to accommodate the major limitation of VLOOKUP -- that it only works to find data "to the right" of your source data.
MSFT recently came out with a new function - XLOOKUP - which is a much easier way to solve this issue. And in fact... it can replace VLOOUP altogether.
Consider this data source which we previously looked at:
What if we were given the EIN of 28-8573644 and needed to find the associated CustomerID? VLOOKUP is not your friend since the CustID is "to the left" of the EIN. And IndexMatch works... but requires a fairly complex Excel formula. Let's try XLOOKUP.
XLOOKUP uses the following format:
dataValue = XLOOKUP (LOOKUP_VALUE, LOOKUP_ARRAY, RETURN_ARRAY)
The LOOKUP_VALUE is the data item you've been given (the EIN), the LOOKUP_ARRAY is the column where the LOOKUP_VALUE can be found, and the RETURN_ARRAY is the column where you're trying to find your data. For the example above, we would use:
CustID = XLOOKUP ("28-8573644",G:G,B:B)
CustID = QEX711
Of course, you can use a cell reference for the LOOKUP_VALUE. If the EIN you're searching for is in another table and the data is in cell Z9, your formula could be:
CustID = XLOOKUP (LOOKUP_VALUE, LOOKUP_ARRAY, RETURN_ARRAY)
CustID = XLOOKUP (Z9, G:G,B:B)
CustID = QEX711
The great thing about XLOOKUP is that you can use it to find data either "to the left" or "to the right." The downside is that it doesn't work in older versions of Excel.
Comments