Arrayformula for entire column in Google Sheets with a lookup not working

Question

I have been working on automating a reporting sheet I have and discovered the very handy use of arrayformula in Sheets that autofills the column with your formula based on data in another column.

Anyway, whenever I try this approach with a lookup formula it only uses the second row of the column it looks up against and then fills up all rows with the result of the lookup against that first row.

I tried using both INDEX MATCH and VLOOKUP.

With INDEX MATCH the formula was actually looking up against the heading so was returning no results.

With VLOOKUP I get the aforementioned issue where it only looks up against second row.

The VLOOKUP formula I curently have is:

=arrayformula( if(B:B<>"",IF( row(B:B)=1, "Country",vlookup(D:D,FBlookup!$A$3:$B$20,2))))

Am I doing anything wrong or are lookups, especially against a table in another sheet just not working as an arrayformula autofilling a column?

Answer 1

It looks like you're trying to create a heading in row 1, then below it, run a vlookup if there is a value in column B - This works

={"Country";arrayformula(if(len(B2:B)>0,vlookup(D2:D,FBlookup!$A$3:$B$20,2,false),))}

Answer 2

On closer look I realised I missed the FALSE argument at the end of the vlookup formula which was the issue. That's what happens when you begin only using INDEX MATCH...

Still doesn't explain why the INDEX MATCH formula only looks at the heading instead of dynamically taking the relevant row of data to look up against.

=arrayformula( if(B:B<>"",IF( row(B:B)=1, "Country",index($O$2:$O$19,match(D:D,$N$2:$N$19,0)))))