Formula - Capture Multiple names into One cell

[PSU35]

I hope I can explain this clearly.

I need advise on how to write a formula that will:
1) look thru a string of cells in a column for a match to a number
specified in another cell
2)then return the person name located in the cell next to the match

Sounds simple except ther will be multiple matches to the number and each
match can have a different name. I need to show all the names that match,
seperated by a comma, in this one cell.

 

[Chip Pearson]

The VLOOKUP function should do this. If the value to be matched is in A1 and
the list of data is in B1:C100, then use

=VLOOKUP(A1,B1:C100,2,FALSE)

That will scan down column B until a value matches A1, go to the next column
and return the data there. If no match is found, the formula will return
#N/A. The FALSE in the formula indicates that VLOOKUP is to match the exact
value in A1.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[PSU35]

Chip
What is the FALSE for? Do I replace that with something ?

 

[PSU35]

Chip
Tried the formula with "False" and it returns only one name, the first one
it see's. Without "False" it return the last name it see's. It's not giving
me all the names.

 

[Don Guillett]

Use data>filter>autofilter on the number

 

[Don Guillett]

Oops. You said you want a list of all hits in ONE cell
Sub FiltertoOneCell()
mynum = 1 'put your number here

lr = Cells(Rows.Count, "a").End(xlUp).Row
Range("A1:B" & lr).AutoFilter Field:=1, Criteria1:=mynum
For Each c In Range("b2:b" & lr).SpecialCells(xlCellTypeVisible)
ms = ms & ", " & c
Next c
MsgBox Right(ms, Len(ms) - 1)
Range("c1").Value = Right(ms, Len(ms) - 1)
Range("A1:B" & lr).AutoFilter
End Sub