Lookup, Index, Match ... Not sure which combo will do it?

**nikko4239** · 06-08-2010, 01:17 PM

I cannot figure this one out. I have a column of text which corresponds to a column of numbers. Some of the numbers are the same. I need a formula which will list all of the text corresponding to the number I tell it to search for.

Example:

-------A-------B
1----Bob-----3
2----Sue-----8
3----Mike----4
4----Jim------3
5----Tom-----9
6----Pam----2
7-----Pat-----3

I need a formula which would provide me a result of "Bob, Jim, Pat" when I search for "3". Or even a combination of formulas which would give me
Bob
Jim
Pat
... and I could use the concatenate formula to put them into one field.

Please help!!!

**JBeaucaire** · 06-08-2010, 01:27 PM

String Concatenation is not a builtin Excel function, but it is easily added. This is VBA, though. (User Defined Functions, actually).

Based on your needs, I'd suggest the CONCATIF() User Defined Function would do the trick. Would you be OK adding a new function to your workbook? This would require you to "enable macros" each time you open the workbook. If that's OK, then I can walk you through it.

=======
ConcatIf
http://www.excelforum.com/excel-prog...in-cell.html#2 'explanation
http://www.excelforum.com/attachment...if-example.xls 'sample
http://www.excelforum.com/attachment...tif-sample.xls 'sample

Your usage would be like this in a cell:
=ConCatIf(B1:B10, 3, A1:A10,", ",TRUE)

**nikko4239** · 06-08-2010, 02:50 PM

I'll likely be one of many (of varying experience levels) utilizing this worksheet so I'd like to keep the front end as clean and easy as possible. I'm a little concerned about using macros considering many would likely say "No" or close out of the window when the macro warning came up upon program launch ...

Is this really my only option? If so, I'll probably need to come up with some sort of work-around to get the result I need...

**ChemistB** · 06-08-2010, 04:12 PM

Here's another option. If you enter the value you want to look up (i.e. 3) in cell E2, then in F2 and dragged to the right enter as an array (CNTRL SHFT ENTER)

=IF(COLUMNS($B2:B2)>COUNTIF($B$1:$B$7,"="&$E2),"",INDEX($A$1:$A$7,SMALL(($B$1:$B$7=$E2)*ROW($A$1:$A$ 7),COLUMNS($B$2:B$2)+COUNTIF($B$1:$B$7,"<>"&$E2))))

If it's entered correctly, you'll see brackets {} around it.

See attachment. Does that work for you?

**shg** · 06-08-2010, 06:32 PM

Welcome to the forum, nikko.

Please take a few minutes to read the forum rules, and then amend your thread title accordingly. A list of guesses is not a problem description.

Thanks.