EXCEL LOOKUP

W

Wallace

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.
 
T

T. Valko

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($B1:$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)
 
M

muddan madhu

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

T. Valko

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),"")
 
T

T. Valko

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


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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top