Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
EXCEL LOOKUP
my excel matrix is set up as such:
A1=BOB; A2=TIM; A3=BOB; A4=SUE; A5=JOHN; A6=BOB; B1=100; B2=101; B3=102; B4=103; B5=104; B6=105; D1=BOB; Which function should I use in E1 that will lookup BOB in the matrix above and, if first value is found, it will list the second value for BOB in cell E2 and, if first value is found, it will list the third value for BOB in cell E3. Thx. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
EXCEL LOOKUP
Try one of these array formula** :
This one is specific to the sample data you posted where the values to be returned are numbers and it will *only return numbers* (in ascending order): Array entered** in E1: =IF(COLUMNS($E1:E1)<=COUNTIF($A1:$A6,$D1),SMALL(IF ($A1:$A6=$D1,$B1:$B6),COLUMNS($E1:E1)),"") This one is generic and will work with both text and numbers. Also array entered**: =IF(COLUMNS($E1:E1)<=COUNTIF($A1:$A6,$D1),INDEX($B 1:$B6,SMALL(IF($A1:$A6=$D1,ROW(B1:B6)),COLUMNS($E1 :E1))-MIN(ROW(B1:B6))+1),"") Copy which ever formula you use across until you get blanks. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Wallace" wrote in message ... my excel matrix is set up as such: A1=BOB; A2=TIM; A3=BOB; A4=SUE; A5=JOHN; A6=BOB; B1=100; B2=101; B3=102; B4=103; B5=104; B6=105; D1=BOB; Which function should I use in E1 that will lookup BOB in the matrix above and, if first value is found, it will list the second value for BOB in cell E2 and, if first value is found, it will list the third value for BOB in cell E3. Thx. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
EXCEL LOOKUP
try this
in E1 put this formula ( use ctrl + shift + enter ) and drag it =IF(ISERROR(INDEX($B$1:$B$6,SMALL(IF($A$1:$A$6=$D$ 1,ROW($A$1:$A $6)),ROW(1:1)),0)),"",INDEX($B$1:$B$6,SMALL(IF($A$ 1:$A$6=$D$1,ROW($A $1:$A$6)),ROW(1:1)),0)) On Sep 26, 1:40*pm, Wallace wrote: my excel matrix is set up as such: A1=BOB; A2=TIM; A3=BOB; A4=SUE; A5=JOHN; A6=BOB; B1=100; B2=101; B3=102; B4=103; B5=104; B6=105; D1=BOB; Which function should I use in E1 that will lookup BOB in the matrix above and, if first value is found, it will list the second value for BOB in cell E2 and, if first value is found, it will list the third value for BOB in cell E3. Thx. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
EXCEL LOOKUP
Ooops!
I see you wanted to list the results down a column not across a row. Try one of these (still array entered): Specific version: =IF(ROWS(E$1:E1)<=COUNTIF(A$1:A$6,D$1),SMALL(IF(A$ 1:A$6=D$1,B$1:B$6),ROW($E1:E1)),"") Generic version: =IF(ROWS(E$1:E1)<=COUNTIF(A$1:A$6,D$1),INDEX(B$1:B $6,SMALL(IF(A$1:A$6=D$1,ROW(B$1:B$6)),ROWS(E$1:E1) )-MIN(ROW(B$1:B$6))+1),"") -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try one of these array formula** : This one is specific to the sample data you posted where the values to be returned are numbers and it will *only return numbers* (in ascending order): Array entered** in E1: =IF(COLUMNS($E1:E1)<=COUNTIF($A1:$A6,$D1),SMALL(IF ($A1:$A6=$D1,$B1:$B6),COLUMNS($E1:E1)),"") This one is generic and will work with both text and numbers. Also array entered**: =IF(COLUMNS($E1:E1)<=COUNTIF($A1:$A6,$D1),INDEX($B 1:$B6,SMALL(IF($A1:$A6=$D1,ROW(B1:B6)),COLUMNS($E1 :E1))-MIN(ROW(B1:B6))+1),"") Copy which ever formula you use across until you get blanks. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Wallace" wrote in message ... my excel matrix is set up as such: A1=BOB; A2=TIM; A3=BOB; A4=SUE; A5=JOHN; A6=BOB; B1=100; B2=101; B3=102; B4=103; B5=104; B6=105; D1=BOB; Which function should I use in E1 that will lookup BOB in the matrix above and, if first value is found, it will list the second value for BOB in cell E2 and, if first value is found, it will list the third value for BOB in cell E3. Thx. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
EXCEL LOOKUP
Try it like this. It's more efficient and more robust:
=IF(ROWS(E$1:E1)<=COUNTIF(A$1:A$6,D$1),INDEX(B$1:B $6,SMALL(IF(A$1:A$6=D$1,ROW(B$1:B$6)),ROWS(E$1:E1) )-MIN(ROW(B$1:B$6))+1),"") -- Biff Microsoft Excel MVP "muddan madhu" wrote in message ... try this in E1 put this formula ( use ctrl + shift + enter ) and drag it =IF(ISERROR(INDEX($B$1:$B$6,SMALL(IF($A$1:$A$6=$D$ 1,ROW($A$1:$A $6)),ROW(1:1)),0)),"",INDEX($B$1:$B$6,SMALL(IF($A$ 1:$A$6=$D$1,ROW($A $1:$A$6)),ROW(1:1)),0)) On Sep 26, 1:40 pm, Wallace wrote: my excel matrix is set up as such: A1=BOB; A2=TIM; A3=BOB; A4=SUE; A5=JOHN; A6=BOB; B1=100; B2=101; B3=102; B4=103; B5=104; B6=105; D1=BOB; Which function should I use in E1 that will lookup BOB in the matrix above and, if first value is found, it will list the second value for BOB in cell E2 and, if first value is found, it will list the third value for BOB in cell E3. Thx. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matrix lookup/mulitple criteria lookup | Excel Discussion (Misc queries) | |||
Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |