Get multiple answers from a table

[Curt D.]

I have two columns, one (Column A) that repeats and the other (column B) that
has a none repeating number. I need to have a formula that will give me
result in column C that will display all the results from column B when
searching for a number is column A. Like when looking for the number 10 in
Column A, Column B has two numbers (1 & 7) that match that will be displayed
in Colomn C.
Does anyone have an idea on how to do this, I used Vlookup but it will only
give me the first answer. Any help would be appreciated, thanks in advance.
Here is an example:

Column A Column B Column C
10 1 1,7
11 2 2,8
12 3 3,9
13 4 4
14 5 5,11
15 12
10 7 1,7
11 8 2,8
12 9 3,9
13 4
14 11 5,11
15 12 12

 

[Teethless mama]

Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/english/

Assuming your data in A1:B12

In C1:
=SUBSTITUTE(TRIM(MCONCAT(IF(($A$1:$A$12=A1)*($B$1:$B$12<>""),$B$1:$B$12,"")&"
"))," ",",")

ctrl+shift+enter, not just enter
copy down

 

[Gord Dibben]

Curt

Have a look at Ashish's site for how to return multiple values from a VLOOKUP

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

May get you on the track you need.


Gord Dibben MS Excel MVP

 

[Curt D.]

I appreciate the help teethless mama, I tried your formula and get "#Name?"
as a result. I don't see what part is causing the problem. Any idea?

 

[Teethless mama]

First: Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/english/

then use my formula