VLOOKUP Matches in Text Strings

tsfall

New Member
Joined
Nov 3, 2020
Messages
1
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi All,

Have used many solutions that have been posted on this forum for other tasks before, but the first time I'm posting as I can't find quite what I need!

Two sheets to my workbook: Sheet One contains single reference identifiers in each cell of column A (i.e. A1 = C10, A2 = R4). Column B is for returning the Part Number from the second sheet (where I need a formula)

Second sheet (BoM) contains column A full of cells with text strings containing the identifiers (i.e. A1 = R23 R37 R41 R43, A2 = R4, R61, R18) and column B containing the Part Numbers relating to those references.

Basically I need a formula to find the part number for the single reference identifier. I have tried: =VLOOKUP("*"&A2&"*",BoM!$A$2:$B$62,2,FALSE) but the problem is that it finds the first instance of a string. Using the cell contents examples above, that means if I was searching for R4 then the Part Number in B1 would be returned as the formula sees R41 before it sees R4.

Hopefully that makes sense? I can't attach the actual document but have attached a couple of pics for reference!


Cheers,

Tom
 

Attachments

  • Sheet 1.png
    Sheet 1.png
    27.3 KB · Views: 10
  • Sheet 2.png
    Sheet 2.png
    62 KB · Views: 10

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

jardenp

Active Member
Joined
May 12, 2009
Messages
367
What about adding spaces? =VLOOKUP("*"&" "&A2&" "&"*",BoM!$A$2:$B$62,2,FALSE)
 

Rav_Singh

New Member
Joined
Jun 29, 2019
Messages
28
I'm encountering the same issue as highlighted by the originator of this ticket. The above solution i'm afraid didn't help...I get #N/A if I run that formula. If I close the spaces then I get a single value back (the first value returned) rather than the multiple values I hope to return into the cell.

Any help greatly appreciated.
 

ozloz

New Member
Joined
Aug 29, 2018
Messages
5
If this is similar to the result that you want
1605841694847.png
1605841715355.png


I based the answer on JardenP's post.
=VLOOKUP("*"&F11&" *",$F$3:$G$8,2,FALSE)

Make sure that the last entry in each List item (e.g. C9, C19 and C29) have a space following otherwise these particular entries will be missed.
Otherwise to highlight use a comma instead.
e.g.
1605841908356.png
1605841931470.png


Uses the following formula
=VLOOKUP("*"&F11&",*",$F$3:$G$8,2,FALSE)

If I am missing the point,sorry
 

Watch MrExcel Video

Forum statistics

Threads
1,123,250
Messages
5,600,539
Members
414,386
Latest member
PARAMATHMA SENTHILNATHAN

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