Microsoft Excel – XLOOKUP function – returning data from non-contiguous columns

In my recent blog on the XLOOKUP function I showed how useful it was rather than using the VLOOKUP function.

When training XLOOKUP recently, I was asked if it could return data on columns that were not next to each other.

Yes, you can, but it also involves using the CHOOSE function.

In the example below, the original column order was Wine Number, Wine Name, Quantity and Price:

I want to return Wine number, Price and Quantity – wine name Semillon 2009 is in cell G13, so I am putting my cursor in cell H13.

Going to XLOOKUP, the relevant syntax is

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found],[match_mode],[search_mode])

In this example, we need cell G13 for the lookup value and $B$2:$B$10 is the lookup array.

It is the return array that is more tricky. The CHOOSE function allows us to specify the order of columns so I need CHOOSE({1, 2, 3}, $A$2:$A$10, $D$2:$D$10, $C$2:$C$10). We want to return 3 columns, that is why the CHOOSE function has the 1,2,3; the squiggly brackets indicate that we are returning an array of three columns.

So my formula is:

=XLOOKUP(G13,$B$2:$B$10,CHOOSE({1,2,3},$A$2:$A$10,$D$2:$D$10,$C$2:$C$10),”Not in stock”)

Find out more about our Microsoft Excel training in the London area or our Microsoft 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, Uncategorized and tagged , , . Bookmark the permalink.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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