CODEWITHSUNDEEP
excel
user2532928
user2532928

Reputation: 69

Give column A when you find text string in column B

I have two sheets. One sheet contains column A and column B. A contains names, B contains a list of names. My second sheet contains names where I need to find if any of the names in my second sheet appear in column B, and if they do, what is the name in column A.

For example:

Sheet1:

A1: Nancy
B1: Kyle (243), Jack (524), Foster (4352)
A2: Bill
B2: Cathy (23423), Tim (1019)

Sheet2:

A1: Kyle (243)
B1: Nancy
A2:Tim (1019)
B2:Bill

I know how to use vlookup/index/match when the text matches exactly. But I can't get it to approximate search for text string correctly. Countif can only tell me, yes, that search string exists in that column but not which row. Any assistance would be greatly appreciated!

Upvotes: 0

Views: 203

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

Use MATCH with wildcards:

=INDEX(Sheet1!A:A,MATCH("*"& A1 &"*",Sheet1!B:B,0))

enter image description here

Upvotes: 1

Related Questions