| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup value in col D when Col A & B are listed multiple times
I want to capture info from col C when values I enter equal values listed in
col A and col B (example - I enter 2.000 and 20-25 and it returns 1.550). I have data as follows: 1.500 5-10 .896 1.500 15-20 .875 1.500 20-25 .866 2.000 15-20 1.750 2.000 20-25 1.550 3.500 15-20 2.420 3.500 20-25 2.300 3.500 25-30 2.260 3.500 35-40 2.140 Any suggestions? Thanks in advance. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup value in col D when Col A & B are listed multiple times
Hi,
with your data in columns A,B & C and the lookup values in d1 & E1 D1= 20-25 E1= 2.0 =INDEX(A1:A9,MATCH(1,(B1:B9=D1)*(C1:C9=E1),0)) 'This is an array formula which must be entered with CTRL+Shift+Enter and NOT 'just enter. If you do it correctly then Excel will put curly brackets around 'the formula{}. You can't type these yourself. If you Edit the ranges 'then you must re-enter as An array Mike "HammerD" wrote: I want to capture info from col C when values I enter equal values listed in col A and col B (example - I enter 2.000 and 20-25 and it returns 1.550). I have data as follows: 1.500 5-10 .896 1.500 15-20 .875 1.500 20-25 .866 2.000 15-20 1.750 2.000 20-25 1.550 3.500 15-20 2.420 3.500 20-25 2.300 3.500 25-30 2.260 3.500 35-40 2.140 Any suggestions? Thanks in advance. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup value in col D when Col A & B are listed multiple times
I miread you column requirements, try this instead
=INDEX(C1:C9,MATCH(1,(A1:A9=D1)*(B1:B9=E1),0)) still an array Mike "Mike H" wrote: Hi, with your data in columns A,B & C and the lookup values in d1 & E1 D1= 20-25 E1= 2.0 =INDEX(A1:A9,MATCH(1,(B1:B9=D1)*(C1:C9=E1),0)) 'This is an array formula which must be entered with CTRL+Shift+Enter and NOT 'just enter. If you do it correctly then Excel will put curly brackets around 'the formula{}. You can't type these yourself. If you Edit the ranges 'then you must re-enter as An array Mike "HammerD" wrote: I want to capture info from col C when values I enter equal values listed in col A and col B (example - I enter 2.000 and 20-25 and it returns 1.550). I have data as follows: 1.500 5-10 .896 1.500 15-20 .875 1.500 20-25 .866 2.000 15-20 1.750 2.000 20-25 1.550 3.500 15-20 2.420 3.500 20-25 2.300 3.500 25-30 2.260 3.500 35-40 2.140 Any suggestions? Thanks in advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How many times is a name listed | Excel Discussion (Misc queries) | |||
| How can I sort multiple times | Excel Worksheet Functions | |||
| sorting multiple minimum times | Excel Discussion (Misc queries) | |||
| Delete rows listed less than 8 times??? | Excel Worksheet Functions | |||
| How do I add multiple times together | Excel Discussion (Misc queries) | |||
Linear Mode
