XLOOKUP Supersedes VLOOKUP Function
Great, you have been told you need to do a VLOOKUP. What is a VLOOKUP? When should I use it?
A VLOOKUP function in Microsoft Excel is a glorified copy and paste tool. It looks for a matching piece of information finds it in a table and brings back information it found from a specific column.
Imagine you have a massive set of data it goes on for ever, but you are only interested in a handful of columns from this data set. Use a VLOOKUP to make a smaller refined set of data and the data is still linked to the original so as updates occur you still have the latest information.
You could use a VLOOKUP for the purpose of making a smaller list and then using that list to create a PivotTable.
If you have an Invoice No, Order No, Product Id use the VLOOKUP function to bring back the description.
Well that’s all great but now there is a much better function called XLOOKUP.
Structure for VLOOKUP IS
=VLOOKUP(LookupValue, table array, column number, Match)
What makes XLOOKUP so great?
I got excited when I learned about XLOOKUP Function it is better than VLOOKUP or INDEX and MATCH Function put together. XLOOKUP is an improvement of the VLOOKUP functions inabilities.
I have included an Excel file so you can follow along and further below you will find a link to a YouTube example.
For example, with the VLOOKUP you can only find an exact match or nearest lowest match. In the past you would have had to add the MATCH function to be able to do nearest highest match.
VLOOKUP returns #N/A when it can’t find a match. You had to add the IFERROR function to return a comment instead, for example Not in stock. XLOOKUP function has this built in. So far you are saving nesting two other functions, the IFERROR and MATCH.
If you find yourself needing to create for example 3 VLOOKUP Functions where you need to return information from column 2, 3 and 4. First Name, Surname and Phone number. That’s 3 VLOOKUP Functions not anymore. XLOOKUP will prefill cells to the right by spilling the function.
What do I mean by that, watch the YouTube video for clarification?
So, exited on top of that it protects the cells when you use the spill feature.
Structure for XLOOKUP IS:
=XLOOKUP(LookupValue, lookup array, return Array, if not found, match mode, search mode)
(If you have used a VLOOKUP before the third item was “COLUMN NUMBER”. This is now an “ARRAY”.)
For better readability of a function use Range names.
If not found is your IFERROR function built in. This is where you can enter
- Function for example “discontinued” to display text.
XLOOKUP Match mode is either a:
0 for exact – you can still use the word False if you prefer
1 for exact or nearest lowest – you can sill use the word True if you prefer
-1 for exact or nearest highest
2 Wildcard character match (* ? ~)
XLOOKUP Search Mode
The * is used to represent any number of characters – for example * pty Ltd
The ? is used to represent one character – for example A?6 or A???6 each ? is one character in that position.
The ~ represents a literal character – for example *~? Returns Hello?
XLOOKUP Function YouTube video