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?

2 Answers 2

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),))}
1
  • 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, 2017 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)))))
1
  • 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? Mar 8, 2017 at 15:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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