check if string is contained in other

[joharmenezes]

I have 2 sheets.

one sheet has a column of names say Alex, Bob, Chad.

I have another sheet with 2 columns:
1st Column has names Alex Michael, Chad Rogers, Bob Stevens,
2nd column has codes: 009, 007, 003, etc...

Now I would like to check if a name in sheet1 is contained in sheet2 and paste its corresponding code next to it

so sheet1 should look like

Alex 009
Bob 003
Chad 007

How do I do so?

Thanks

 

[Balli]

Hi joharmenezes,

You can use vlookup. Like:
=VLOOKUP(A2,Sheet2!$A$2:$B$4,2,0)

 

[joharmenezes]

I don't think vlookup will work as vlookup will give results even for partial matches which can be a disaster. I think it can be done with SEARCH and SUMPRODUCT but I can't seem to get it done

 

[sgmpatnaik]

@joharmenezes

Not sure what you are trying but you can try this

=IFERROR(VLOOKUP(A1&"*",Sheet3!$A$1:$B$3,2,FALSE),"")

or

=IFERROR(VLOOKUP("*"&A1&"*",Sheet3!$A$1:$B$3,2,FALSE),"")

for your knowledge i attached the sample file

Hope it will solve other wise please inform

Thanks

 

[Somendra Misra]

@joharmenezes

Use this formula

=INDEX(Sheet3!$B$2:$B$5,SMALL(IF(Sheet2!A2=MID(Sheet3!$A$2:$A$5,1,FIND(" ",Sheet3!$A$2:$A$5,1)-1),ROW(Sheet3!$A$2:$A$5)-ROW(Sheet3!$A$2)+1),1))

I had extracted data on sheet 2 and my main data is in sheet 3. You can change the reference as per your req.

Tell me if this works as you need.

Somendra.

 

[Somendra Misra]

@joharmenezes

I had a query...does your list don't have duplicate first name, i.e. can there be 2 Alex?

Somendra.

 

[joharmenezes]

Hi sgmpatnaik your formula is working. I didn't know that u could use a "*" in vlookup. Thanks.

Hi Somendra I have not tested your formula but I think it should work. Regarding your query, no there is only 1 alex. but thanks anyway

 

[Somendra Misra]

@joharmenezes ...one thing I forgot to mention enter the formula with Ctrl+Shift+Enter and not just Enter as mine formula is an Array Formula.

Somendra.