4

I'm using XLOOKUP to return a row with 4 columns. At the moment I have to copy/paste the formula every time new rows are inserted, so I thought of using ARRAYFORMULA to skip this step.

The problem is when I implement this, the result is a single column per row instead of the 4 columns I get by having a XLOOKUP per row; The result seems to be trimmed to only the first column.

My XLOOKUP looks something like this:

=XLOOKUP(A2,Sheet2!A2:A,Sheet2!D2:G)

And applying ARRAYFORMULA this is how it looks:

=ARRAYFORMULA(XLOOKUP(A2:A,Sheet2!A2:A,Sheet2!D2:G))

Any ideas on how can I make it return the full 4 columns?

0

2 Answers 2

5

Use map(), like this:

=map(A2:A, lambda(a, xlookup(a, Sheet2!A2:A, Sheet2!D2:G)))

See map() and lambda().

3

Further to doubleunary's answer, the reason why xlookup doesn't work in the expected way with arrayformula is because, as per the documentation: 'If result_range is more than one row or column, then the output will be the entire row/column at the index a match was found in the lookup_range', i.e. it returns an array by default, so the desired arrayformula result would be an array-of-arrays which isn't supported by arrayformula. Doubleunary's answer is effectively generating an array-of-arrays using map which does allow this.

You could also try =arrayformula(vlookup(A2:A,{Sheet2!A2:A,Sheet2!D2:G},{2,3,4,5},0)) which works because vlookup does not return arrays by default so arrayformula works on both the search_key and index arguments to return a contiguous 2D array (which is allowed).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.