Lookup value in table with multiple results

[GWM]

I have a table of names with corresponding amounts as follows:
Note: I prefer using functions and not code for this project.

A B
abc 1000
def 5000
ghi 3000
abc 4000
def 7000
abc 9000

Example: I need to retrieve all of the "abc" names and the amounts, and
place the amounts in 3 consecutive cells, which correspond to "abc."
The range is dynamic, with a maximum of 15 rows, 5 different names (in
column A), and 3 amounts per name.
Thank you in advance for your help

 

[L. Howard Kittle]

Try this and pull down two more rows. Now change the very last number in
the second and third formulas to a 2 and a 3.

Select each cell and array enter, CTRL+SHIFT+ENTER. If you make changes to
the formula you will need to array enter again.

=INDEX($B$1:$B$13,LARGE(($A$1:$A$13=$C$1)*ROW($C$1:$C$13),COUNTIF($A$1:$A$13,$C$1)+1-1))

The lookup value is in C1 and you will note that in the formulas there is a
reference to C1:C13. I believe that is because all the ranges in the
formula must be the same size, even though it only uses C1 where the lookup
value is.

I don't remember where I got the formula and cannot explain it in much
detail. The very last number can also be a cell reference. So if in
another situation you would need to look up only the second instance of the
lookup value you could just change the referenced cell to a 2.

HTH
Regards,
Howard

 

[Gary''s Student]

You can either use AutoFilter to select all the abc's or the technique
described in:

http://office.microsoft.com/en-us/excel/HA012260381033.aspx

 

[GWM]

Thank you for your help. Worked perfectly

Try this and pull down two more rows. Now change the very last number in
the second and third formulas to a 2 and a 3.

Select each cell and array enter, CTRL+SHIFT+ENTER. If you make changes to
the formula you will need to array enter again.

=INDEX($B$1:$B$13,LARGE(($A$1:$A$13=$C$1)*ROW($C$1:$C$13),COUNTIF($A$1:$A$13,$C$1)+1-1))

The lookup value is in C1 and you will note that in the formulas there is a
reference to C1:C13. I believe that is because all the ranges in the
formula must be the same size, even though it only uses C1 where the lookup
value is.

I don't remember where I got the formula and cannot explain it in much
detail. The very last number can also be a cell reference. So if in
another situation you would need to look up only the second instance of the
lookup value you could just change the referenced cell to a 2.

HTH
Regards,
Howard




.