VLOOKUP NO MATTER WHERE DATA IS IN TARGET STRING

[JuicyMusic]

Hello again,

I have a formula that includes a VLOOKUP with a WildCard. It works beautifully, but I need to tweak the VLOOKUP portion. This formula currently brings back an employee name if the employee name is at the beginning of the target "text string".

I need the Vlookup to bring back the employee name no matter where within the target test string it is.

This formula brings back OsirusB for the following scenario:
Example employee name: OsirusB Example target text string: OsirusB, PhilipG, BreffuQ, HorusH, NannyQ, ColletteX, (employee last names are abbreviated and each separated by a comma)

This formula brings back #N/A for the following scenario:
Example employee name: OsirusB Example target text string: PhilipG, BreffuQ, OsirusB, HorusH, NannyQ, ColletteX, (employee last names are abbreviated and each separated by a comma)

FORMULA:

=IF(OR($G9="Off/Vacation",$G9="--"),"--",
VLOOKUP("*"&$C9&"*",Thursday120320!$B:$C,2,0))

Thank you so much,
Juicy

 

[JuicyMusic]

Never Mind. My formula does work. I don't know how to delete this post.

 

[JuicyMusic]

I'm sorry if I'm causing confusion. I checked the results of my formula again and I DO need to tweak this formula.
I would like to remove the WildCard part of the formula, because I can see that I should not have that. I need exactly what I wrote in my original post. See below please.

I need the Vlookup to bring back the employee name no matter where within the target test string it is.

This formula brings back OsirusB for the following scenario:
Example employee name: OsirusB Example target text string: OsirusB, PhilipG, BreffuQ, HorusH, NannyQ, ColletteX, (employee last names are abbreviated and each separated by a comma)

This formula brings back #N/A for the following scenario:
Example employee name: OsirusB Example target text string: PhilipG, BreffuQ, OsirusB, HorusH, NannyQ, ColletteX, (employee last names are abbreviated and each separated by a comma)

FORMULA:

Excel Formula:

=IF(OR($G9="Off/Vacation",$G9="--"),"--",
VLOOKUP("*"&$C9&"*",Thursday120320!$B:$C,2,0))

Thank you again,
Juicy

 

[JuicyMusic]

I must not be feeling well because it does work. My apologies to whoever sees this post.

My formula works just fine.

=IF(OR($G9="Off/Vacation",$G9="--"),"--",
VLOOKUP("*"&$C9&"*",Thursday120320!$B:$C,2,0))

 

[AhoyNC]

Maybe use INDEX & MATCH instead VLOOKUP.

Book1
	A	B	C
1	OsirusB		2
2
3		SamS, BillD, SamR	1
4		PhilipG, BreffuQ, OsirusB, HorusH, NannyQ, ColletteX	2
Sheet1

Cell Formulas
Range	Formula
C1	C1	=INDEX(C:C,MATCH(TRUE,ISNUMBER(SEARCH(A1,B:B)),0))

 

[AhoyNC]

Since you have Excel 365 another option would be the FILTER function.

Book1
	A	B	C
1	OsirusB		2
2
3		SamS, BillD, SamR	1
4		PhilipG, BreffuQ, OsirusB, HorusH, NannyQ, ColletteX	2
Sheet1

Cell Formulas
Range	Formula
C1	C1	=FILTER(C:C,ISNUMBER(SEARCH($A$1,B:B))=TRUE,"Not Found")

 

[JuicyMusic]

AhoyNC, thank you for coming to my post. I did want to use INDEX/MATCH but this type of formula intimidates me for some reason, and so I use my go-to, VLookup.
I will be absolutely happy to try this tomorrow morning. I've got to get comfortable with it. I'll respond back tomorrow.

 

[JuicyMusic]

I'm in right now trying your index/match formula.....i'll be back in a few minutes.