Arrayformula not populating all other rows when using index+match

Question

Sample data: https://docs.google.com/spreadsheets/d/10H7hToEek7XxXpPiAtlj6n58db_83Njdaxxspk4pIO8/edit?usp=sharing

Im trying to populate the 1st column of sheet 1 based on the data on sheet 2. VLOOKUP wouldnt work because im comparing the 2nd column of sheet 1 to the 2nd column of sheet 2 and vlookup will only allow it if im comparing to the 1st column of sheet 2. I tried index+match and it worked for 1 row but when I try to use arrayformula to populate all rows, it never runs for all the other rows.

Here is the index+match formula im using: INDEX(Sheet2!A2:A,MATCH(B2:B,Sheet2!B2:B,0))

Here is the formula I use with arrayformula: =ARRAYFORMULA(INDEX(Sheet2!A2:A,MATCH(B2:B,Sheet2!B2:B,0)))

How do I make this run for all rows and if this won't work is there a better way to go about this?

Answer 1

per slippy101: =ArrayFormula(VLOOKUP(FILTER(B2:B,B2:B<>""),{Sheet2!B:B,Sheet2!A:A},2))

per Dazrin: =ARRAYFORMULA(IF(B2:B="",,VLOOKUP(B2:B, {Sheet2!B2:B,Sheet2!A2:A}, 2, FALSE)))

Both formula works, the problem arises because INDEX doesn't work with ARRAYFORMULA pointed out by Dazrin as well.