Lookup function one row at a time

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sojo - 14 Sep 2007 16:38 GMT

I have the following data:

Sheet 1
    A    B    C    D
1    1-most    2    3    5-least
2    Traction    Flexibility    Durability    Weight
3    Weight    Flexibility    Flat sole    Traction
4    Weight    Flexibility    Shock ab    Support
5    Traction    Flexibility    Durability    Weight

Sheet 2

    A    B    C    D
1    Flexibility    Flat sole    Arch S    Support
2
3
4

In sheet 2 A2 I want I need a formula that looks up sheet 2 A1 in Sheet 1 A2
to D2 and returns the corresponding value from sheet 1 A1 to D1. So in this
case the answer would be 2. The result for Sheet 2 B3 will be 3.
I have 309 rows of data. So I need to be able to just drag the formula into
the rest of the cells.

Reply to this Message

Teethless mama - 15 Sep 2007 04:18 GMT

In Sheet2 A2:
=IF(ISNA(MATCH(Sheet2!A$1,Sheet1!$A2:$D2,0)),"",INDEX(Sheet1!$A$1:$D$1,MATCH(Sheet2!A$1,Sheet1!$A2:$D2,0)))

copy across from A2 to D2 and down as far as needed

> I have the following data:
>
[quoted text clipped - 19 lines]
> I have 309 rows of data. So I need to be able to just drag the formula into
> the rest of the cells.

Reply to this Message

Sojo - 17 Sep 2007 15:44 GMT

Fabulous!!!! Thanks a million!

> In Sheet2 A2:
> =IF(ISNA(MATCH(Sheet2!A$1,Sheet1!$A2:$D2,0)),"",INDEX(Sheet1!$A$1:$D$1,MATCH(Sheet2!A$1,Sheet1!$A2:$D2,0)))
[quoted text clipped - 24 lines]
> > I have 309 rows of data. So I need to be able to just drag the formula into
> > the rest of the cells.

Reply to this Message