I want to extract a substring from a cell in a column based on search match

[andrebooyzen]

Hi All.
Great forum.
I have a column of data and I want to search the whole column for a match to name and then extract the first 5 characters from the cell that has a match. I have triend INDEX and MATCH and VLOOKUP with varying results.
Please help
Thanks

Typical cell in the column looks like this

09:00-10h30
Andre
Peter

I want to search all the cells in a specified column for a name eg Peter, and if it finds a match then return the first 5 characters of the cell.

 

[DanteAmor]

Try:

=IFERROR(LEFT(VLOOKUP("*"&D2&"*",B4:B6,1,0),5),"")

 

[Peter_SSs]

It found 2 matches correctly in the column I searched. However it returned a 1 in one match and a 0 in the other match.

Could you give a small set of sample data where that issue arises and explain in relation to that?

However, when there is no match it returns a #N/A. Any way I can get rid of this?

You can target that error more specifically like this

=IFNA(LEFT(VLOOKUP("*"&D2&"*",B4:B6,1,0),5),"")

 

[andrebooyzen]

Never mind, I used IFERROR and problem is solved . Thanks for the help guys.

 

[DanteAmor]

=IFERROR(LEFT(VLOOKUP("*"&D2&"*",B4:B6,1,0),5),"")

I used IFERROR and problem is solved

Im glad to help you. Thanks for the feedback.