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
|