Forum Discussion
Solved
XLOOKUP on several rows and columns
Hi All,
I want to use XLOOKUP on several rows and columns, but that does not seem to work.
=XLOOKUP(F10:F357,Q10:T96,Q9:T9)
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,
Martin
In G10:
=INDEX($Q$9:$T$9,SUMPRODUCT(($Q$10:$T$96=F10)*COLUMN($Q$10:$T$10))-COLUMN($Q$10)+1)
Fill down to G357.
6 Replies
The same result is returned with this formula:
=IFERROR(INDEX({"A"."B"."C"."D"},SUMPRODUCT(MMULT(N($Q$10:$T$96=F10),TRANSPOSE(COLUMN(Q:T))))-16),"")
or
=IFERROR(INDEX($Q$9:$T$9,SUMPRODUCT(MMULT(N($Q$10:$T$96=F10),TRANSPOSE(COLUMN(Q:T))))-16),"")
- OliverScheurich
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"}.
=BYROW(F10:F357,LAMBDA(row,IFERROR(INDEX({"result1"."result2"."result3"."result4"},SUMPRODUCT(MMULT(N($Q$10:$T$96=row),TRANSPOSE(COLUMN(Q:T))))-16),"")))
In G10:
=INDEX($Q$9:$T$9,SUMPRODUCT(($Q$10:$T$96=F10)*COLUMN($Q$10:$T$10))-COLUMN($Q$10)+1)
Fill down to G357.
- HansVogelaar
Thanks a lot for your provided solution.
To have only one formula for the range F10:F357, I changed it to
=BYROW(F10:F357,LAMBDA(array,INDEX($Q$9:$T$9,SUMPRODUCT(($Q$10:$T$96=array)*COLUMN($Q$10:$T$10))-COLUMN($Q$10)+1)))
