VLOOKUP NO MATTER WHERE DATA IS IN TARGET STRING

JuicyMusic

Board Regular
Joined
Jun 13, 2020
Messages
122
Office Version
  1. 365
Platform
  1. Windows
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:
Excel Formula:
=IF(OR($G9="Off/Vacation",$G9="--"),"--",
VLOOKUP("*"&$C9&"*",Thursday120320!$B:$C,2,0))

Thank you so much,
Juicy
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

JuicyMusic

Board Regular
Joined
Jun 13, 2020
Messages
122
Office Version
  1. 365
Platform
  1. Windows
Never Mind. My formula does work. I don't know how to delete this post.
 
Last edited:

JuicyMusic

Board Regular
Joined
Jun 13, 2020
Messages
122
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jun 13, 2020
Messages
122
Office Version
  1. 365
Platform
  1. Windows
I must not be feeling well because it does work. My apologies to whoever sees this post.

My formula works just fine.
Excel Formula:
=IF(OR($G9="Off/Vacation",$G9="--"),"--",
VLOOKUP("*"&$C9&"*",Thursday120320!$B:$C,2,0))
 
Solution

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,760
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Maybe use INDEX & MATCH instead VLOOKUP.

Book1
ABC
1OsirusB2
2
3SamS, BillD, SamR1
4PhilipG, BreffuQ, OsirusB, HorusH, NannyQ, ColletteX2
Sheet1
Cell Formulas
RangeFormula
C1C1=INDEX(C:C,MATCH(TRUE,ISNUMBER(SEARCH(A1,B:B)),0))
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,760
Office Version
  1. 365
Platform
  1. Windows
Since you have Excel 365 another option would be the FILTER function.

Book1
ABC
1OsirusB2
2
3SamS, BillD, SamR1
4PhilipG, BreffuQ, OsirusB, HorusH, NannyQ, ColletteX2
Sheet1
Cell Formulas
RangeFormula
C1C1=FILTER(C:C,ISNUMBER(SEARCH($A$1,B:B))=TRUE,"Not Found")
 

JuicyMusic

Board Regular
Joined
Jun 13, 2020
Messages
122
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jun 13, 2020
Messages
122
Office Version
  1. 365
Platform
  1. Windows
I'm in right now trying your index/match formula.....i'll be back in a few minutes.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,122
Messages
5,640,243
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top