How to fill a column based on adjacent column value in excel?

Question

For example let's say, in Sheet 1 I have the below Columns

Column A..........Column B

Apple..............Val01

Ball...............Val02 

Cat................Val03

Dog................Val04

In Sheet 2, I have the same two columns but with 5000 rows. Let's consider 5 rows for example,

Column A........... Column B
 
Dog

Ball

Apple

Cat

Cat

Now, in my Sheet 2, I have 5000 rows with Column A filled but I want to automate the filling of Column B based on the values present in Sheet 1. I have tried using the INDIRECT function but was not successful. I was using this formula =INDIRECT( "'test!B" & MATCH(A1, test!A:A, 0)) where test is my sheet name. Can somebody please tell me what's wrong in this or if there's any other method to solve this problem? Thank you.

=INDIRECT("'test'!B" & MATCH(A1, 'test'!A:A, 0)) or omit the ' surrounding the sheet name :-) — T.M.

RichieV RichieV · Accepted Answer · 2020-09-05T15:45:29

You are looking for INDEX instead of INDIRECT

In Sheet2!B1

=INDEX(test!B:B,MATCH(A1,test!A:A,0))

And copy down to all rows.

How to fill a column based on adjacent column value in excel?

2 Answers