"tilde" in vlookup formula

    Hi. I saw this post from 2005 and would like to know if you can give me the correct sum for my document. I do not understand the formula "Batman" posted. I do not want to remove the tilde as the document has to have the exact code we use in Pastel.
    http://www.ozgrid.com/forum/showthread.php?t=36548


    forum.ozgrid.com/index.php?attachment/71895/


    "SubstoreProductMaster/Description (B2; B27 etc)" is reading from "SubstoreBuyPlan/PastelCode/"

    Re: "tilde" in vlookup formula


    Hello,


    In your cell B2 ... have you tested the Index() Match() combination ...


    Code
    =INDEX(SubstoreBuyPlan!$I$2:$I$5,MATCH(A2,SubstoreBuyPlan!$E$2:$E$5,0))


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Re: "tilde" in vlookup formula


    Hello again,


    Understood your problem was in fact ... all cells colored in yellow ... :wink: (took me a while to guess ...)


    Attached is your workbook ...

    Re: "tilde" in vlookup formula


    Thank you - when I click in the cell with your formula and then click out of it I get an error #VALUE
    I notice if I click on the cell with your formula you have these brackets in front and after { }
    When I click in the sell it goes away. If I then click enter - I get a #VALUE error. Replacing it in front and back does not resolve the problem.


    I want to edit the formula to read the full column instead of set cells.


    Please advise and thank you for assisting me.

    Re: "tilde" in vlookup formula


    Hello again,


    Glad the formula solved your problem ...


    When entering or editing an Array Formula ... instead of the Enter key ... you need to use simultaneously the three keys Control+Shift+Enter ...


    This will automatically add the curly brackets { } at the beginning and at the end of the formula ..... :wink:



    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Re: "tilde" in vlookup formula


    Hello,


    When editing in array formulas ... you should have defined ranges ... instead of entire Columns ...( with over 1 million rows...)


    HTH


    See attached file

    Re: "tilde" in vlookup formula


    Quote from Anri;788674

    Noted. Thanks.


    What does the "8" and "6" mean that you used?


    The number of characters before the " ~" ....

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Re: "tilde" in vlookup formula


    Oh, okay.


    What do I do if that is constantly changing. People who will be working in this document will not know how to change it.

    Re: "tilde" in vlookup formula


    Well,


    You can replace 6 by =FIND("~",A2,1)-1


    HTH

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Re: "tilde" in vlookup formula


    I have found an easier way to look for values with a the tilde in VLOOKUP:


    =VLOOKUP(SUBSTITUTE(A2,"~","~~"),Table_array,Col_index_num,Range_lookup)


    This is a bit less complex, but your way works as well.


    Thank you very much for all your help.

    Re: "tilde" in vlookup formula


    Glad you could fix your problem ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

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

Register Yourself Login