Lookup function one row at a time

[Guest]

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.

 

[Guest]

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

 

[Guest]

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)))

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