Hey Friends,
Welcome back to The Sheet!
It’s a short one this week. We're going to talk about how to use the XLOOKUP function in Excel and why it is preferable to VLOOKUP.
=💡TheSheetTip(XLOOKUP: A Better Alternative to VLOOKUP)
XLOOKUP is a new and improved version of the VLOOKUP function that allows you to search for a specific value in a range or array of values and return the corresponding value from another column in the same row.
⚠️ Please note: XLOOKUP is only available for more recent versions of Excel (Microsoft 365, Excel 2021, and Excel Online)
☑️The advantages of XLOOKUP over VLOOKUP are:
It can search for values from left to right or right to left, making it more flexible than VLOOKUP
It can handle errors more effectively than VLOOKUP, including returning a custom error message
It can perform exact or approximate matches and return the closest match to the search value
It can return values from multiple columns or rows, which is not possible with VLOOKUP
Here is an example of how to use the XLOOKUP function:
Suppose we have a list of currencies and their conversion rate to GBP. We want to look up the conversion rate of a specific currency in the list to calculate our sales from different regions into GBP.
To use XLOOKUP, we need to specify the search value, the array or range of values to search, the column or row from which to return the result, and any optional arguments such as the match mode and custom error message (we won’t cover the optional arguments this time).
=XLOOKUP("Currency", $B$3:$B$5, $C$3:$C$5)
In this example, "Currency" is the search value, B3:B5 is the range to search, and C3:C5 is the column from which to return the conversion rate.
Don’t forget ⚠️ to add $ signs to ensure you are getting an absolute not a relative reference. If you’d like some more information on the difference between absolute and relative references, please 📨 drop us a message so we know it’s something you’re interested in having covered in a future edition of The Sheet.
Until next time,
Functional Excel Team