How to Return XLOOKUP column dynamically in Excel for Mac and Windows

Returning the XLOOKUP column dynamic is nothing but, making the return array dynamic. Which is similarly doing VLOOKUP column number dynamic based on the column header. This solution is applicable in Excel for mac and windows users.

Let say you have data set which you would like to extract the column results dynamically based of the header value

XLOOKUP INDIRECT DYNAMIC 01

The first thing we are going to convert our lookup table or source table into an Excel table by keeping the cell on the data and pressing cmd + t for mac and ctrl + t for windows

Give your table name which makes it very easy when you are writing a formula with structured references, to know more about excel table check this video

XLOOKUP INDIRECT DYNAMIC 02

Get Access to Your File

Enter your name and email to receive access to the file.

XLOOKUP formula would look like this, but the return array is not dynamic as you can see in the below figure it the return array is referencing the Name column

=XLOOKUP($B9,tbl_myData[ID],tbl_myData[Name],"NO DATA FOUND")
XLOOKUP INDIRECT DYNAMIC 03

Some how we need to make “tbl_myData[Name]” the column name in the box bracket to be dynamic

So here is the trick.

Your column heading must be exactly the same as per the source table, if not then you might run into #REF Error

Now we need to built the text exactly same for the return array as you can see in the below image

="tbl_mydata["C$8&"]"
XLOOKUP INDIRECT DYNAMIC 04

It is only a text now and needs to be converted into reference, for that we will wrap this formula into an INDIRECT function.

= INDIRECT("tbl_mydata["&B$2&"]",TRUE)
XLOOKUP INDIRECT DYNAMIC 05

With new dynamic array we can see the referenced column country names is spilled across the rows.

Wrap the formula into XLOOKUP

=XLOOKUP($B9,tbl_myData[ID],INDIRECT("tbl_myData["&C$8&"]",TRUE),"NO DATA FOUND")
XLOOKUP INDIRECT DYNAMIC 06
1 1 vote
Article Rating
Subscribe
Notify of
guest
0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x