Using VLOOKUP to fill data with a partial string match

1. Using VLOOKUP to fill data with a partial string match

Hello,

Lets say we have a two column table that has column A as:

SKU
1000
1000-yellow
1000-white
2000
2000-yellow
2000-white
3000
3000-yellow
3000-white

and so on...

I need to fill column B with size in each and every row from another worksheet in the same workbook where I have two columns. I have been able to use VLOOKUP to do this. The table array would look like this in column A on the second worksheet:

SKU
1000
2000
3000

and so on...with column B having the size

10
20
30

It is a simple VLOOKUP for matching SKU's 1000, 2000, 3000 etc because it is an exact match but how do I do a partial text match to match the first part of the cell value or even search the cells for the matching partial string and place the proper value 10 for size for SKU's 1000, 1000-yellow, and 1000-white, what would be value 20 for 2000, 2000-yellow, and 2000-white and so on?

2. Re: Using VLOOKUP to fill data with a partial string match

Is it possible that instead of a VLOOKUP you can just use:

=LEFT(A2,2) copied down

This will extract the left 2 most characters... so for 1000 and it's subs, it will give you 10, for 2000 and its subs, it will return 20, etc. Does that work for you? or is that not the pattern?

3. Re: Using VLOOKUP to fill data with a partial string match

What you saying would work however my numbers are hypithetical. I have over 10,000 SKU's all with different variables that are not as simple as 1000 sku being 10 size. The 1000 sku products may have any given size as well as all of the rest.

4. Re: Using VLOOKUP to fill data with a partial string match

I am having to grab data from one worksheet and plug in the pertaining information from the table array for sku 1000 into all area's where any variation of 1000, 1000-yellow, or 1000-white would show up and the same for any other sku.

5. Re: Using VLOOKUP to fill data with a partial string match

Hello,

If it is always the 1st 4 characters. try B2 copy down

=VLOOKUP(LEFT(A2,4)+0,Sheet2!A:B,2,0)

If you have different size of numbers & there is a "-" after the number, try

=VLOOKUP(LEFT(A2,FIND("-",A2&"-")-1)+0,Sheet2!A:B,2,0)

6. Re: Using VLOOKUP to fill data with a partial string match

Thank you! Worked perfectly.

7. Re: Using VLOOKUP to fill data with a partial string match

Now lets say I just want to match something to a string found in a title column A such as:

14K. SOLID GOLD BRACELET WITH BLUE TOPAZ & PERIDOTS

I want to put some "Bracelet" in column B from a table where Column A is

1
2
3
4

and column B is

Rings
Earrings
Bracelets
Necklaces

How would I search for Bracelet as a part of the string of Column A and place the pertaining data in column B?

8. Re: Using VLOOKUP to fill data with a partial string match

So you have a table say in K1:L4 of numbers and items, and you want to get the corresponding number from this table where one of the items matches a word in your string, say in A1?

If so, try:

=LOOKUP(9.999999E+307,SEARCH(" "&\$L\$1:\$L\$4&" "," "&A1&" "),\$K\$1:\$K\$4)

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