Google Sheets - Array Formula with Xlookup and Importrange - Stack Overflow

admin管理员组

文章数量:1357098

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

LINK TO EXAMPLE SHEET

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:

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

Many Thanks in advance Tom

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

LINK TO EXAMPLE SHEET

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:

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

Many Thanks in advance Tom

• google-sheets-formula
• array-formulas
• xlookup

Share Improve this question Follow asked Mar 28 at 18:06 Tom AlbuTom Albu 1344 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 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"))))

本文标签： Google SheetsArray Formula with Xlookup and ImportrangeStack Overflow