# Thread: From cell in sheet 1 look up in sheet 2 and pull 2 cells

1. ## From cell in sheet 1 look up in sheet 2 and pull 2 cells

Excel 2010
I have two worksheets. I would like use each cell in a particular column and for each cell look up a match in sheet number 2 and pull information from two adjacent cells in the same row.

2. Captain,

Using Index and Match, you can do what you want to achieve:

In the first adjacent cell (B2) place the following formula:

=INDEX(Sheet2!\$A\$2:\$C\$7,MATCH(Sheet1!\$A2,Sheet2!\$A \$2:\$A\$7,0),2)

In the second adjacent cell (C2) place the following formula:

=INDEX(Sheet2!\$A\$2:\$C\$7,MATCH(Sheet1!\$A2,Sheet2!\$A \$2:\$A\$7,0),3)

Then copy down

Sheet 1:

Index_Match3.png

Sheet 2 with the data:

Index_Match4.png

HTH,
Maud

3. FWIW, if you have a lot of data, I would suggest putting the MATCH part into a separate cell and then refer to that from both INDEX formulas. I'd also only use one column for the INDEX part:
B2: =MATCH(\$A2,Sheet2!\$A\$2:\$A\$7,0)
C2: =INDEX(Sheet2!B\$2:B\$7,\$B2)
and then copy across to D2 and fill down.

4. Instead of having 3 columns (B,C,D) in addition to a column for the look up values (A) as suggested above, I think a better alternative would be to eliminate the match altogether.

Column A would have the lookup values (1,3,2,6,5,4)
B2 =INDEX(Sheet2!B\$2:B\$7,\$A2)
C2 =INDEX(Sheet2!C\$2:C\$7,\$A2)

Copy columns C and D down

Index_Match5.png

5. Agreed - as long as your lookup values are the positions, of course.