Google Sheets - Array Formula with Xlookup and Importrange

Question

I have a Sheet which basically adds name and address info from a membership list to a list of bank payments.

It uses an Xlookup (in Column G) to compare the Bank Account Name (Column B) with the list of Bank Account Names on the membership sheet (Gift Aid List). If it finds a match it uses ImportRange to import columns C thru F from the membership list. If it can't find anything it writes "No GA"

=XLOOKUP(B4:B, IMPORTRANGE($G$2, "Gift Aid List!H:H"), IMPORTRANGE($G$2, "Gift Aid List!C:F"),"No GA",0)

This works well, but I want to use an Arrayformula to copy this down the column (rather than doing it manually - the bank data is actually another Importrange, so I want the formula to automatically expand down the column as more data is added.)

I tried this:

=ArrayFormula(XLOOKUP(B4:B, IMPORTRANGE($G$2, "Gift Aid List!H:H"), IMPORTRANGE($G$2, "Gift Aid List!C:F"),"No GA",0))

which works, except it only seems to bring the first column (C) of data from the Gift Aid List and not the others.

I realised that, in this example, the Importranges are not required, but in the real world the Gift Aid List info is in a separate Sheet. Anyway, I tried taking them out, but it still only imports the first column of data.

So I'm thinking it might be something to do with the XLOOKUP, but before I start trying to find a workaround for that I thought I'd ask on here.

(The obvious workaround, I realise, would be to just have a separate copy of the formula in each column and just bring one column of data at a time, but that's not as elegant)

Any ideas why this is or how I can fix it?

The sample sheet linked has four tabs:

with the non-arrayed formula,
with the arrayed formula,
with the arrayed formula with the Importrange removed
the GIFTAIDLIST data to be imported. (This would normally be in a separate spreadsheet)

Many Thanks in advance Tom

rockinfreakshow · Accepted Answer · 2025-03-28 18:14:51Z

1

Use map/lambda in place of arrayformula to populate all target columns:

=map(B4:B,lambda(Σ,if(Σ="",,xlookup(Σ,importrange(G2,"GiftAidList!H:H"),importrange(G2,"GiftAidList!C:F"),"No GA"))))

answered Mar 28 at 18:14

rockinfreakshow

30.7k4 gold badges19 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tom Albu Mar 28 at 18:20

Oh Fantastic - that works (and such a quick response - thank you.) But can you explain what it does please

rockinfreakshow Mar 28 at 18:32

map/lambda helps in applying your existing single cell formula(which already works) to the entire given range of B4:B with ease.

Tom Albu Mar 28 at 18:37

But how Mr Rockinfreakshow?, How? Oh never mind, I'll Google it. Thank you most sincerely for your help though.

Collectives™ on Stack Overflow

Google Sheets - Array Formula with Xlookup and Importrange

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related