VLOOKUP part of a text string.

jonwhite

Board Regular
Joined
Mar 29, 2005
Messages
69
Hi all,

Is there any way I can Vlookup for just part of a text stiring in a cell.
I am trying to vlook if a part number contains the letters “HFR” if it does I want
It to refer to the comment I have made in the second column of my vlook table against HFR.

Thanks in advance for all your help.

Cheers

Jon
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Hi Jon,

Modify this:

Code:
 =VLOOKUP("*"&A2&"*",Sheet1!A:B,2,FALSE)

The asterisks either side mean that the text string appears anywhere in the table.
 
Upvote 0
HThanks for that. I have now changed my code to the following =IF(D6="","",(VLOOKUP("*"&D6&"*",$A$117:$B$180,2,FALSE))) the first part number in D6 id 214HFD but it still does not look it up...

Thanks

Jon
 
Upvote 0
Hmmm, works fine for me. I've checked and it always searches for the string and returns the value in column B.

I note that you've not given a sheet name for the table - is it on the same worksheet?
 
Upvote 0
hi Arfix 9,

Yes it is on the same sheet. I undersatnd what your vlookup is doing but it note quite what i need.

If my partnumber is lets say 1234HFR5678 i need it to lookup just HFR I have tried a search function and an if function but can't get it to work.
 
Upvote 0
maybe you would know how i can use the if function

this function =IF(D6="hfr",1,0) looks only for HFR can i change it so it looks for HFR within my part number 1234hfr5678?

Thanks again


Jon
 
Upvote 0
no they are all different formats, I have a production sheet that vlooksup each part number and puts a comment against it if applicable. In this case any part number containing HFR I need to put the comment "Make sure link wire is present" because I have approximately 100 part number that contain HFR I didn’t want to type them all in on my look up table.
 
Upvote 0
I think I've cracked it (though it requires that the H of HFR is the first H in the part number...)

Code:
 =IF(D6="","",VLOOKUP(MID(D6,SEARCH("H",D6),3),$A$117:$B$180,2,FALSE))

Try that...
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top