Using VLOOKUP to fill data with a partial string match Separated with a Dash

    Hi:


    I have a list of items in Column A and size info on Column B i want to being in the info to another sheet with a vlookup but the problem is that I have a lot of items with different colors so after the item no. it has a dash and a letter or two for the color so i want to bring into my new sheet all info. i have in my old sheet for all items regardless of color so for instance in my old sheet i will BR1000-EM and then in my new sheet i will have BR1000-R and BR1000-SA and BR1000-YC how do i make a vlookup it should only lookup the values in both sheets only till the dash (i cant use a certain no. like left,6 because the item no can have more then 6 but it always has a dash when it has a color code) also not all items have dashes so the dash is not always there but when its there i would like that the lookup should stop by the dash.

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


    Hi there, maybe more clear if we see the sample workbook, try to upload it at the forum, and describe the expected results too


    Thanks

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


    imwald


    Try the left & find functions.


    =VLOOKUP(LEFT(A2,FIND("-",A2)-1),H2:I8,2,FALSE)


    Change the H2:I18 to the location of your lookup table.


    Windy

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



    I did use your code its good but i am still missing some. first i also have some recorders that dosnt have the dash at all so it gives a value error but this i fixed with a iferror code but i also have a problem that the lookup results from the other sheet has dashes as well so in the lookup statement i also need to search only till the dash. I have attached a sample of my sheet

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


    Here is an option:


    First, create dynamic named ranges for the Sku and Width in Sheet2:


    Go to Formulas|Define Name
    Enter name: SKU
    Enter formula in Refers to box: =OFFSET(Sheet2!$A$1,1,,COUNTA(Sheet2!$A:$A)-1,1)
    Click Ok, then click New
    Enter name: Width
    Enter formula in Refers to box: =OFFSET(Sheet2!$B$1,1,,COUNTA(Sheet2!$A:$A)-1,1)
    Click Ok.


    Click Close.


    Now enter formula in B2:


    [COLOR="#0000FF"]=SUBSTITUTE(INDEX(Width,MATCH(TRUE,INDEX((TRIM(MID(SUBSTITUTE(SKU,"-",REPT(" ",100)),1,100))&"-"=TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",100)),1,100))&"-"),0),0)),"WIDTH:","")+0[/COLOR]


    copied down.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

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


    NBVC has a great solution for you


    or you can try with wildcards like this


    =SUBSTITUTE(IFERROR(INDEX(Sheet2!A:B,MATCH(LEFT(SUBSTITUTE(Sheet1!A2,"/","-"),FIND("-",SUBSTITUTE(Sheet1!A2,"/","-"))-1)&"*",Sheet2!A:A,0),2),INDEX(Sheet2!A:B,MATCH(A2,Sheet2!A:A,0),2)),"WIDTH: ","")


    Hope this Helps

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login