Lookup value in table with multiple results

  • Thread starter Thread starter GWM
  • Start date Start date
G

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
 
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
 
Thank you for your help. Worked perfectly

L. Howard Kittle said:
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




.
Click to expand...
 
Back
Top