Help in Lookup Formula Please

  • I have this formula to lookup a value from another sheet
    But I would like to exclude some data, for example cell A1, cellA12, cellA22, cell56 or more
    IF(C2=Data!$A$1;"None";(LOOKUP(A2;LUV!$F$2:$F$500;LUV!$H$2:$H$500)))


    How can I use multiple cells or a Range like A1:A20 in Data criteria instead of Only one Cell. (A1)
    Is there another way to right this formula ?:puzzled:

  • =IF(ISNUMBER(MATCH(C2;Data!$A$1:$A$20;0))=FALSE;"None";LOOKUP(A2;LUV!$F$2:$F$500;LUV!$H$2:$H$500))


    should do it....


    MATCH(C2;Data!$A$1:$A$20;0) should bring back the position of what's in cell C2 in your list Data!$A$1:$A$20


    ie 7 if it's 7th in the list or


    2 if it's second in the list


    ISNUMBER then checks that this value (ie 7 or 2) is a number. If it's FALSE, then it will return your first arguement of "none", if it's TRUE (ie it's found a match) then the second arguement comes into play


    a simpler example :


    where cell A1 equals 5 and there's a list of numbers 1 to 20 on sheet2 :


    =IF(ISNUMBER(MATCH(A1,Sheet2!A1:A20,0))=FALSE,"none","proceed with my lookup")


    or with your seperators :


    =IF(ISNUMBER(MATCH(A1;Sheet2!A1:A20,0))=FALSE;"none";"proceed with my lookup")

  • Since i've posted my question i have received only one answer with a solution that didn't quite work out!Does anybody have some kind of solution that will actually work?!?!?:question::(

  • what error message were you getting with my method ? it worked in my simulation, so we must be missing something crucial.......


    what is your *actual* formula ? what are you *actual* sheet names and ranges ?


    (substituting these with example names sometimes causes problems in giving a solution....)

  • Hi there Chris!
    there was no error message.it just wouldn't work.i have a sheet with three columns :1.clients 2.discount 3.murchandise. my problem is that there are many murchandises that shouldn't have a discount and i want to exclude them from the discount process.these murchandises are too many and not one after the other(not in order)they belong to various cells of the same column.i'm using "if" together with "if"("if" is text=murchandise).
    have i managed to confuse you yet?:rolleyes:

  • yes, but don't worry, I'm easily confused


    :)


    is it possible to attach a section of your worksheet that shows what you're trying to achieve ?


    (I'm a bit concerned that nobody else has chipped in, so don't want you only relying on my answers.... hopefully a picture or attachment will help )


    Chris
    :)