 SOLVED

New Contributor

# 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?

Best regards,

Martin

6 Replies
best response confirmed by Martin_Gemen (New Contributor)
Solution

# Re: XLOOKUP on several rows and columns

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.

# Re: XLOOKUP on several rows and columns

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

# Re: XLOOKUP on several rows and columns

@Hans Vogelaar
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)))

# Re: XLOOKUP on several rows and columns

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.

# Re: XLOOKUP on several rows and columns

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),"")))`` 