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
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
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")
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&"]"
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)
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")