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.
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)))
