XLookup Variable Return Array Needed Similar to Vlookup & Match or Index & Match

[eicherbiker]

Hi, I would like to use the XLookup function; however, I keep stumbling on the Return Array and how it is frozen to a column. Is there a way to have the Return Array determined by referring to another cell and looking for its contents in column headers?

This is how I handle it now:

I'd like to use the XLookup, it's easier with the concatenate, but this is as close as I can get. I can't figure out how to do something like the Match function:

 

[Tetra201]

Two options:

=XLOOKUP($J$2&$K$2,$B:$B&$C:$C,INDEX($D:$H,0,MATCH(L$1,$D$1:$H$1,0)),0,0)
=XLOOKUP($J$2&$K$2,$B:$B&$C:$C,CHOOSECOLS($D:$H,MATCH(L$1,$D$1:$H$1,0)),0,0)

 

[eicherbiker]

THANK YOU! I tried the ChooseCols function and it worked perfectly! This will be my new favorite nested function combination!

 

[Peter_SSs]

Yet another option would be

=XLOOKUP(J2&K2,B2:B5&C2:C5,FILTER(D2:H5,D1:H1=L1))

(I would recommend not using whole column references. I can see my worksheet pause when concatenating the entire columns)

I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)