1

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?

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),))}
  • Thanks Aurielle, I like the structure of your formula starting with the heading. Out of curiosity, do you know if the same could be done using INDEX MATCH? Whenever I replace the VLOOKUP with INDEX MATCH I get the whole column filled up with the look up against the second row? – George S Mar 9 '17 at 11:14
0

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)))))
  • Does B:B contain the number 1 in any cell, or are you trying to create a heading in that column in row 1, followed by the vlookup array formula? – Aurielle Perlmann Mar 8 '17 at 15:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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