does a cell contain (in any part of it) values from a list - THEN - paste the value in next column.

[jhbloemarts]

I'm looking for a formula to:

Column A - contains list of names. Column a has 1000+ values
A1 : jjjj john jjj
A2 : JOHN djfkdjf
A3 : kdfjkdjf John
A4 : sdslkdl dave
A5 : ddsjkdave
A6 : terry
A7 : kjdkfjd Terry 030

Column C - contains lookup values (column c contains 50 values)
C1 : john
C2 : steve
C3 : dave
C4 : terry

In column E - i want to look in column A and confirm a match to column C.
Answers in column E would be:
E1 : john
E2 : john
E3 : john
E4 : dave
E5 : dave
E6 : terry
E7 : terry


It's basically a VLOOKUP but then not an exact match.
I should not do a best guess because EXCEL really sucks at this.
Maybe the wildcard/mid function.
I found 1 other person looking for the exact same but no trail yet entered with answer.
I've been at it for about 5 nights in a row on multiple multiple forum. It's killing me

Good luck.

Joost Removed E-Mail Address - Moderator

 

[jhbloemarts]

Thanks.
WORKS PERFECTLY.

(PS: to further readers. The red text in the marco "Option compare text" is part of the CODE !!!!!)

 

[Teethless mama]

Try this:

C2:C7 - hold criteria


=LOOKUP(1E+99,SEARCH(C$2:C$7,A2),C$2:C$7)

copy down as far as needed