+ Reply to Thread
Results 1 to 8 of 8

Using VLOOKUP to fill data with a partial string match

  1. #1
    Registered User
    Join Date
    05-25-2011
    Location
    Arlington, TX
    MS-Off Ver
    Excel 2003
    Posts
    7

    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. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    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?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    05-25-2011
    Location
    Arlington, TX
    MS-Off Ver
    Excel 2003
    Posts
    7

    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. #4
    Registered User
    Join Date
    05-25-2011
    Location
    Arlington, TX
    MS-Off Ver
    Excel 2003
    Posts
    7

    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. #5
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    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)
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  6. #6
    Registered User
    Join Date
    05-25-2011
    Location
    Arlington, TX
    MS-Off Ver
    Excel 2003
    Posts
    7

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

    Thank you! Worked perfectly.

  7. #7
    Registered User
    Join Date
    05-25-2011
    Location
    Arlington, TX
    MS-Off Ver
    Excel 2003
    Posts
    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. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    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)

+ 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