If you are using Excel 365 or Excel 2019, you will be able to use the new XLOOKUP function. This has many uses – I am outlining one of them here.
Previously if I was using a VLOOKUP function and wanted to use the function across several columns, I would use a VLOOKUP function combined with a COLUMN function. And if I also wanted to add in a message if there was no match, I also included the IFERROR function.
So if my original date looked like
and I had a list of Wine numbers that a client had asked for and I wanted to find the relevant Name, Quantity and Unit Price, my formula, in cell H2 if the Wine number that I was trying to locate in this data set was in cell G2 would be:
=IFERROR(VLOOKUP($G2,$A$2:$D$10,COLUMN(B1),FALSE), “Not in stock”)
To achieve a similar result with an XLOOKUP, the formula would be:
=XLOOKUP(G2,$A$2:$A$10,$B$2:$D$10,”Not in stock”)
Which is rather easier. When I use this in cell H2, it automatically also fills in I2 and J2 as it is looking at the information in 3 columns, B to D.
The syntax for the XLOOKUP used here is
=XLOOKUP(lookup value, lookup array, return array, if not found)
There are other options for closest rather than exact match, etc, but I’ll look at them at another time!
Find out more about our Excel training in the London area or our Excel training in Wiltshire, Dorset, Somerset and Hampshire.