VLOOKUP part of a text string.

[jonwhite]

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

 

[Airfix9]

Hi Jon,

Modify this:

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

The asterisks either side mean that the text string appears anywhere in the table.

 

[jonwhite]

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

 

[Airfix9]

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?

 

[jonwhite]

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.

 

[Airfix9]

Ah! I seeeee. I'm looking at it the wrong way around.... hang on a sec.

 

[jonwhite]

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

 

[Airfix9]

Is your part number always in the same format? If so, what format is that?

 

[jonwhite]

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.

 

[Airfix9]

I think I've cracked it (though it requires that the H of HFR is the first H in the part number...)

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

Try that...