XLOOKUP using column header when it's NOT a table?

[adambc]

Is this possible?

I am working with an extract from a source system that presents data in a range that is NOT a table - and at this stage I do not want to convert the range into a table (in VBA or manually) before I do the XLOOKUP (long story!) ...

I am currently using XLOOKUP(lookup_value, A1:A999, B1:B999) but I need to extend this to multiple extracts where the lookup_array and return_array may not be in column A & B respectively but do have the same column header name (in row 1) ...

If the data WAS in a table, I know exactly how to do this, but it's NOT (in a table) eg =XLOOKUP(lookup_value,Table1[Header1],Table1[Header3])

But is there a way to do this using Header1 & Header2 when I don't have a table name to refer to?

Thanks ...

 

[Fluff]

What version of Excel are you using, as neither 2016 nor 2019 has Xlookup?

 

[adambc]

What version of Excel are you using, as neither 2016 nor 2019 has Xlookup?

365

 

[Fluff]

Ok, you may want to update your profile, if that is all you use..
How about

=XLOOKUP(A2,XLOOKUP("Header1",B1:Q1,B2:Q100),XLOOKUP("Header2",B1:Q1,B2:Q100))