# VLOOKUP with partial match

1. ## 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.

2. ## Re: VLOOKUP with partial match

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. ## Re: VLOOKUP with partial match

there are a few ways here is one

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. ## 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. ## 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.

7. ## 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))

8. ## Re: VLOOKUP with partial match

Thank you! That works perfectly.

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