I need an XLOOKUP formula with a variable Return Array

[GeorgieAnne]

Hello All,
Here is what I have to solve:
I get some data from another systems. This data changes every day every run. At times I will have a set of column headers something like @07, or @04, or @99. These could be in any column!

I need to construct an XLOOKUP with this kind of logic:
=XLOOKUP(lookup value, Lookup array, return array(find where the code @04 is for example, “not found, FALSE)

And at times code @04 may not be there, or it might be in column F, another day another run it maybe in column J

Thanks for any help

 

[Fluff]

How about using index/match instead

=INDEX(Sheet1!B2:Z100,MATCH(A2,Sheet1!A2:A100,0),MATCH("@04",Sheet1!B1:Z1,0))

 

[GeorgieAnne]

How about using index/match instead

=INDEX(Sheet1!B2:Z100,MATCH(A2,Sheet1!A2:A100,0),MATCH("@04",Sheet1!B1:Z1,0))

Thanks Fluff, I ended up using INDEX/MATCH combination even though I really wanted to use the new XLOOKUP. I guess INDEX/MATCH is still the Queen of looking up data. Thanks for yours example I tailored it to my needs and simplified my version.

 

[Fluff]

Glad to help & thanks for the feedback.