XLOOKUP on several rows and columns

New Contributor

Hi All,
I want to use XLOOKUP on several rows and columns, but that does not seem to work.


Range F10:F357 contains numbers that can be found in Q10:T96 as well.

Depending on the position of lookup value it should return the respective header which is contained in range Q9:T9.

Unfortunately this does not work as XLOOKUP cannot work on arrays (rows >1, columns >1).

Does anybody have a solution for this?
Thanks in advance.

Best regards,


6 Replies
best response confirmed by Martin_Gemen (New Contributor)


In G10:


Fill down to G357.


The same result is returned with this formula:









@Hans Vogelaar
Thanks a lot for your provided solution.
To have only one formula for the range F10:F357, I changed it to
Thanks a lot for your provided solution.
The first one did not work in my case. The part ... {"A"."B"."C"."D"} is not recognized.
The second one did work.
For now I go for the other posted solution.


You are welcome. I should have mentioned that {"A"."B"."C"."D"} has to be replaced with the values from range $Q$9:$T$9 as shown in the example in the screenshot e.g. {"result1"."result2"."result3"."result4"}.



Ah, Ok. Thanks for your update.
Best regards,