Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup function one row at a time
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup function one row at a time
In Sheet2 A2:
=IF(ISNA(MATCH(Sheet2!A$1,Sheet1!$A2:$D2,0)),"",IN DEX(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 "Sojo" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup function one row at a time
Fabulous!!!! Thanks a million!
"Teethless mama" wrote: In Sheet2 A2: =IF(ISNA(MATCH(Sheet2!A$1,Sheet1!$A2:$D2,0)),"",IN DEX(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 "Sojo" wrote: 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
verify use of TIME Function, Find Quantity Level compare to time-d | Excel Discussion (Misc queries) | |||
how to combine an IF Function with a lookup function to determine | Excel Worksheet Functions | |||
Lookup function with condition in time series data | Excel Worksheet Functions | |||
time lookup | New Users to Excel | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |