Combining ArrayFormula with Xlookup and Importrange in Google Sheets

Other Questions Google Sheets
1

I’m working on a spreadsheet that matches payment data with customer information from another sheet. The setup works perfectly with a regular formula, but when I try to use ArrayFormula, it only returns the first column instead of all the columns I need.

My current working formula:

=XLOOKUP(A5:A, IMPORTRANGE($F$1, "Customer Data!G:G"), IMPORTRANGE($F$1, "Customer Data!B:E"),"Not Found",0)

This formula looks up account names in column A against a customer database and pulls back multiple columns of info (name, address, phone, email). It works great for individual cells.

When I wrap it with ArrayFormula:

=ArrayFormula(XLOOKUP(A5:A, IMPORTRANGE($F$1, "Customer Data!G:G"), IMPORTRANGE($F$1, "Customer Data!B:E"),"Not Found",0))

The formula only returns data from the first column (column B) instead of all four columns (B through E). I need all the customer information to populate automatically as new payment records get added.

I tried removing the IMPORTRANGE functions to test with data on the same sheet, but the issue persists. Has anyone encountered this behavior before? I’m hoping to avoid creating separate formulas for each column since that would be messy.

What could be causing ArrayFormula to only pull the first column when XLOOKUP should return multiple columns?

2

Had this exact problem 6 months ago. XLOOKUP doesn’t play nice with ARRAYFORMULA when you’re pulling multiple columns - the formula engine gets confused because ARRAYFORMULA wants single values per row, but XLOOKUP tries to return column arrays. I switched to VLOOKUP with ARRAYFORMULA instead. Not as clean, but it actually works. You’ll need to restructure so your lookup column is leftmost in IMPORTRANGE, then use column index numbers 2, 3, 4, 5 for each column. Something like: =ARRAYFORMULA(VLOOKUP(A5:A, IMPORTRANGE($F$1, “Customer Data!G:K”), {2,3,4,5}, FALSE)) - just make sure your data spans the right columns.

3

Yeah, I’ve hit this same problem. ARRAYFORMULA doesn’t play nice with XLOOKUP when you need multiple columns back. Try switching to QUERY instead: =QUERY(IMPORTRANGE($F$1, ‘Customer Data!B:E’), ‘SELECT * WHERE Col1 MATCHES "’&TEXTJOIN(“|”, TRUE, A5:A)&‘"’) - works way better for this stuff.