+ Reply to Thread
Results 1 to 8 of 8

VLOOKUP with partial match

  1. #1
    Registered User
    Join Date
    06-28-2012
    Location
    hki, Finland
    MS-Off Ver
    Excel 2003
    Posts
    4

    VLOOKUP with partial match

    Hello!

    I have a worksheet where column B has a list of serial numbers like X130V440B002. In column E, there's similar numbers like D230Y588B1003. I have used VLOOKUP to find which numbers have a perfect match. They are printed in the column C. The actual problem is that I also have to find numbers which are partially matched. In the column E the last three numbers are rolling numbers so there could many partial matches like X130V440B002 in column B and X130V44B055 and X130V44B026 in column E. So I would need a function which can find if there's a serial number in the column E where the first 8 digits match with the original (column B). I don't know if it's possible to make a function that can print all the matches somehow but even function that can tell that there's a 8 digit match in the column E list would be helpful.

    Thanks for your help!

  2. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: VLOOKUP with partial match

    Quote Originally Posted by question
    So I would need a function which can find if there's a serial number in the column E where the first 8 digits match with the original (column B).
    You can cut the right end of an arbitrary string of characters with LEFT(text_string, how_many_characters_to_keep)

    Please Login or Register  to view this content.
    Cell F1 will check if the first 8 characters (reading from the left) of cell E1 are the same (equal to) the first 8 characters (also from the left) of B1. If they are exactly the same, TRUE. If they are different, FALSE.

    If it's multiple checks, either this way or with VLOOKUP, I would myself prefer to make a table, and each cell of the table performs one check.

  3. #3
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: VLOOKUP with partial match

    there are a few ways here is one
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Registered User
    Join Date
    06-28-2012
    Location
    hki, Finland
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: VLOOKUP with partial match

    The problem is that the serial numbers which I want to match are not in the same row. So I need to use VLOOKUP, right? I have to check that if LEFT(B2,8) -> is in the whole list of like E$2:$E$10000 including only the first 8 characters of the list.

  5. #5
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: VLOOKUP with partial match

    did you look at my example?
    depends what you want to do exactly
    post a workbook
    you could simply custom auto filter with "begins with" and type in the first 8 characters

  6. #6
    Registered User
    Join Date
    06-28-2012
    Location
    hki, Finland
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: VLOOKUP with partial match

    ^I didn't really get it , but huge thanks for your effort. Let's make my problem a little bit simpler. I need a function to column C which tells that can the first 8 characters of column B found anywhere in the column E. Something like true or false.
    Attached Files Attached Files

  7. #7
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: VLOOKUP with partial match

    in c2
    =SUMPRODUCT(--(LEFT($E$2:$E$300,8)=LEFT(B2,8)))>0
    or
    =ISNUMBER(MATCH(LEFT(B2,8)&"*",$E$2:$E$300,0))
    Last edited by martindwilson; 06-28-2012 at 11:44 AM.

  8. #8
    Registered User
    Join Date
    06-28-2012
    Location
    hki, Finland
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: VLOOKUP with partial match

    Thank you! That works perfectly.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1