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:


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:


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


Leave a Reply