Vlookup but with multiple results
You can set up another column to give you a unique reference. For
example, put this formula in E1:
=IF(C1="","",C1&"_"&COUNTIF(C$1:C1,C1))
then copy this down to cover the values in column C.
Then, with 22 in A1, you can put this formula in B1:
=IF(ISNA(MATCH(A$1&"_"&ROW(A1),E:E,0)),"",INDEX(D: D,MATCH(A$1&"_"&ROW
(A1),E:E,0)))
Then copy this down as far as you think you might need it (i.e. to get
all the duplicates).
Hope this helps.
Pete
On Jan 2, 8:10*pm, ORLANDO VAZQUEZ
wrote:
Thank you for your support.
Can someone please help me with the following array? type of question.
What formula can I use to return a list of results when there is more than
one result?
If I use VLOOKup it returns only the first instance of the result.
For example: *Looking up cell a1 value of 22 should result in the following
list:
Dog *
Biscuit *
Steak
A * * * B * * * C * * * D
22 * * * * * * *34 * * *Apple
33 * * * * * * *34 * * *Fish
16 * * * * * * *22 * * *Dog
91 * * * * * * *1 * * * Orange
15 * * * * * * *3 * * * Tangerine
14 * * * * * * *22 * * *Biscuit
21 * * * * * * *1 * * * Tea
34 * * * * * * *5 * * * Salmon
17 * * * * * * *22 * * *Steak
* * * * * * * * 7 * * * Herring
* * * * * * * * 8 * * * Cod
* * * * * * * * 1 * * * Orange
* * * * * * * * 1 * * * Castle
|