Learn the Differences, Strengths, and Best Use Cases for Excel's Top Lookup Functions
Hello my friend! Welcome to this Excel tutorial. Check this out.
Overview of Excel’s Popular Lookup Functions
The three most popular lookup functions in Excel are VLOOKUP, INDEX MATCH, and XLOOKUP. VLOOKUP was created in 1985 and is the most widely used, though I wouldn’t say it’s the best. Personally, I’d give that title to XLOOKUP. Let’s look at how to use each and understand when each might be helpful.
Using VLOOKUP
Imagine I have a list of items with average prices, like milk at 85p and eggs at £1.50. This list is simplified, so please forgive the lack of units or additional data!
To find the expense for an item like milk using VLOOKUP, I’d set up a formula: =VLOOKUP(B2, A:B, 2, FALSE), which looks up milk in column B and retrieves the price from the second column in the range A:B. If milk isn’t found, an error will display. Just remember, VLOOKUP was created in 1985, and it does have some limitations.
Limitations of VLOOKUP
If I swap the order of columns—putting items on the right and prices on the left—VLOOKUP can no longer function properly. To handle this, we need a different method, like INDEX MATCH.
Using INDEX MATCH
To use INDEX MATCH, I’d set up a formula: =INDEX(Sheet2!A:A, MATCH("milk", Sheet2!B:B, 0)). This searches for milk in column B and returns the corresponding price from column A. INDEX MATCH allows us to search in either direction, making it more flexible than VLOOKUP.
Using XLOOKUP
Next up is XLOOKUP, which is similar to INDEX MATCH but even more powerful. I’d use =XLOOKUP("milk", B:B, A:A, "No item found"). Unlike VLOOKUP, XLOOKUP doesn’t require that the lookup column is to the left of the return column. It also has an “if not found” argument, allowing me to display custom messages like “No item found” instead of an error.
If I type in an item like "jelly" that isn’t listed, XLOOKUP displays "No item found" instead of showing a hash symbol (#N/A).
Choosing the Best Lookup Function
So, there we go! We’ve got VLOOKUP, INDEX MATCH, and XLOOKUP. But if they were in a contest, which one would win? I’m leaning towards XLOOKUP, but I’d love to know your opinion. Comment below and let me know what you think!
Thanks for watching, and I hope this helps you decide on the best function for your needs. Job done! I’ll see you next time.