Search a string from a column, and return multiple values

[newroads]

Hi,

I have a long long list of users in a column A (some are in lastname, firstname format & some are in firstname lastname format, and some are probably different, they just have first name & last name in them in some format). I have another list where I have lastnames for users in column B.

My requirement is: that I search each lastname(from column B) in column A and if there is a match (partial it will be I guess, and not exact) then return value(s) of column A, all in the same row as column B value is.

I tried reading & implementing VLOOKUP, and also INDEX & MATCH however could not get things to work. Not sure if this will require to write a MACRO instead.

Thanks for any pointers or help in advance. I am happy to do the hardwork, however need some guidance to start.

Column A Column B Return1 Return2 Return3
Taylor, Mike Vaughn Michael Vaughn
Vaughn, Michael Taylor Taylor, Mike Taylor, Ross
Taylor, Ross Vettory

Regards,
Rohit

[newroads]

No replies :-(

[ChemistB]

It's hard to read how your data is separated from the text. Upload an example worksheet showing what you have and what you want the final result to look like (Go Advanced>Manage Attachments)

[newroads]

Thanks, here is the attachment.

I want to search column B values for a partial match in column A, and if there are matches then that should be displayed in the same row eg. under column Return1, Return2, Return3 etc.

[ChemistB]

With your data in A8:B10, in C8 copied across and down, this ARRAYED Formula

=IFERROR(INDEX($A$8:$A$10, SMALL(IF(ISNUMBER(SEARCH($B8,$A$8:$A$10)), ROW($A$8:$A$10)-ROW($A$8)+1), COLUMNS($A$1:A$1))),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.