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. D...
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.
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),"")))
