Arrayformula not working with index and match google sheets formula

0
votes

I have data in three columns. Column A contains a list of fruits. The second column the rank (1,2,3...) and the third column a list again but this time ordered by preference.

I want to return the rank in the fourth column. I have tried this formula which works as it should but it's returning just one value yet it's an array formula. What could be missing?

=ARRAYFORMULA(index(B2:B11,match(A2:A,C2:C11,0)))

Link to my spreadsheet. https://docs.google.com/spreadsheets/d/1e7xCcdPa3MywDVs70o2kXAwMnzJRMDuucktWPowS_MY/edit?usp=sharing

1
google-sheetsarray-formulas

1 Answers

1
votes

Index doesn't work with array formulas so you have to use Vlookup instead:

=ArrayFormula(if(C2:C="","",vlookup(C2:C,A2:B,2,false)))

enter image description here