Page 1 of 2 12 LastLast
Results 1 to 10 of 12

VLOOKUP part of a text string.

This is a discussion on VLOOKUP part of a text string. within the Excel Questions forums, part of the Question Forums category; Hi all, Is there any way I can Vlookup for just part of a text stiring in a cell. I ...

  1. #1
    Board Regular
    Join Date
    Mar 2005
    Posts
    69

    Default VLOOKUP part of a text string.

    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

  2. #2
    Board Regular Airfix9's Avatar
    Join Date
    Sep 2005
    Location
    I was born under a wandrin' star
    Posts
    790

    Default

    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.
    The more you learn, the more you learn that there's more to learn.

    Every time I make it more idiot-proof, they build a better idiot!

  3. #3
    Board Regular
    Join Date
    Mar 2005
    Posts
    69

    Default

    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

  4. #4
    Board Regular Airfix9's Avatar
    Join Date
    Sep 2005
    Location
    I was born under a wandrin' star
    Posts
    790

    Default

    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?
    The more you learn, the more you learn that there's more to learn.

    Every time I make it more idiot-proof, they build a better idiot!

  5. #5
    Board Regular
    Join Date
    Mar 2005
    Posts
    69

    Default

    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.

  6. #6
    Board Regular Airfix9's Avatar
    Join Date
    Sep 2005
    Location
    I was born under a wandrin' star
    Posts
    790

    Default

    Ah! I seeeee. I'm looking at it the wrong way around.... hang on a sec.
    The more you learn, the more you learn that there's more to learn.

    Every time I make it more idiot-proof, they build a better idiot!

  7. #7
    Board Regular
    Join Date
    Mar 2005
    Posts
    69

    Default

    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

  8. #8
    Board Regular Airfix9's Avatar
    Join Date
    Sep 2005
    Location
    I was born under a wandrin' star
    Posts
    790

    Default

    Is your part number always in the same format? If so, what format is that?
    The more you learn, the more you learn that there's more to learn.

    Every time I make it more idiot-proof, they build a better idiot!

  9. #9
    Board Regular
    Join Date
    Mar 2005
    Posts
    69

    Default

    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.

  10. #10
    Board Regular Airfix9's Avatar
    Join Date
    Sep 2005
    Location
    I was born under a wandrin' star
    Posts
    790

    Default

    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...
    The more you learn, the more you learn that there's more to learn.

    Every time I make it more idiot-proof, they build a better idiot!

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com