Search code examples
excelexcel-formula

EXCEL: How do I return multiple columns using XLOOKUP, when the lookup_value is a range?

Whenever I try to perform an XLOOKUP using a range for the "lookup_value" and a "return_array" that has multiple columns, Excel will only ever return the first column.

Here's the formula I'm using:

=XLOOKUP(E2:E6,A2:A11,B2:C11)

If the value is found, it ONLY ever returns the XLOOKUP value in column B. If I change the "lookup_value" to one cell instead of a range, it works.

How do I get it to return multiple columns?

I've attached an image here of what happens.

Solution

  • It does so because you referencing an array. The function then simply returns only a 1st column (just like TEXTSPLIT() a.o. would).

    To fix this and do this by row, use:

    =XLOOKUP(E2,A2:A11,B2:C11)

    If you want to do this in a single go, use:

    =CHOOSEROWS(B2:C11,XMATCH(E2:E6,A2:A11))