Lookup value in table with multiple results
 

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

Lookup value in table with multiple results
 

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

"GWM" wrote in message
...
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

Lookup value in table with multiple results
 

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

http://office.microsoft.com/en-us/ex...260381033.aspx
--
Gary''s Student - gsnu201002


"GWM" wrote:

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

Lookup value in table with multiple results
 

Thank you for your help. Worked perfectly

"L. Howard Kittle" wrote:

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

"GWM" wrote in message
...
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


.