Microsoft Excel – XLOOKUP

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.

About jdonbavand

I am a trainer of Microsoft Office, Microsoft Project and Crystal Reports. I have called my blog "If Only I'd Known That...." because I hear it so many times in training sessions. In fact, if only I had a £100 for every time someone says "If only I'd known that." ....
This entry was posted in Excel 365, Microsoft Excel and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s