CODEWITHSUNDEEP
excelvbaexcel-formulaexcel-2010
Vignesh N H
Vignesh N H

Reputation: 13

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

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.

Upvotes: 0

Views: 548

Answers (2)

SHAIK GOUSE PASHA
SHAIK GOUSE PASHA

Reputation: 11

Please find the attachment. =INDEX(Sheet1!B:B,MATCH(Sheet2!A2,Sheet1!A:A,0))

Upvotes: 1

RichieV
RichieV

Reputation: 5183

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.

Upvotes: 3

Related Questions