+ Reply to Thread
Results 1 to 19 of 19

Nest Lookup With If And Statements

  1. #1
    Registered User
    Join Date
    01-13-2004
    Posts
    42

    Nest Lookup With If And Statements

    Hello,
    I have the formula below:
    =IF(OR(LEN(B2)=0,C2>0),"",IF(AND(VLOOKUP(A2,$A3:$D$99,4,FALSE)=D2,VLOOKUP(A2,A3:$D$99,3,FALSE)>0,B2>A2),"Possible",""))

    It should bring back the result "possible" when C2 is negative and D2 (name) matches other D cells while B2 (date) is greater than the A cell (date) for the corresponding matched d cell also the corresponding c cell is positive.
    But I think the formula is not incorporating b2> corresponding matched column a.



    Thanks!

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    so do you need another Vlookup? It is difficult to understand what you are after without a sample. Please post a sample zipped sheet and show expected results.
    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
    01-13-2004
    Posts
    42
    Thanks, please see attached.

    I am not really sure what I need, could be a vlookup. Thanks again.
    Attached Files Attached Files

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Maybe =IF(OR(B3 <= A3, C3 > 0, VLOOKUP(A3, $A$4:$D$99,4,FALSE) <> D3, VLOOKUP(A3, $A$4:$D$99, 3, FALSE) <= 0), "", "Possible")

  5. #5
    Registered User
    Join Date
    01-13-2004
    Posts
    42
    hi Shg,

    I tried the formula suggested, but I noticed that when I delete rows 47 to 99, it still gives "possible", since b2 is greater than any of row 2 to 46, there should be no match. How will the formula change if B2 should be less than any column A date to get a match, I might have said it wrong earlier.
    Thanks again!
    Quote Originally Posted by shg
    Maybe =IF(OR(B3 <= A3, C3 > 0, VLOOKUP(A3, $A$4:$D$99,4,FALSE) <> D3, VLOOKUP(A3, $A$4:$D$99, 3, FALSE) <= 0), "", "Possible")

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    It's difficult to understand your logic....

    F2 should only be "possible" if b2 is greater than A for the matched d cell, i.e. the matched cell should be from rows 47 to 99.
    What does "if b2 is greater than A for the matched d cell" mean? Do you mean A2 or do you mean a cell within range A47 to A99 that matches date in A2?

    Please specify using cell references where a match occurs and what results we should see.

  7. #7
    Registered User
    Join Date
    01-13-2004
    Posts
    42
    Thanks NBVC.

    I mean that the date in b2 should be less than the dates in the rest of column A i.e. A3 to A99. In the data I attached, that would mean there would could only be a match from rows 47 to 99.

    I appreciate it, thanks!
    Quote Originally Posted by NBVC
    It's difficult to understand your logic....



    What does "if b2 is greater than A for the matched d cell" mean? Do you mean A2 or do you mean a cell within range A47 to A99 that matches date in A2?

    Please specify using cell references where a match occurs and what results we should see.

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    still a little confused..

    does this do what you want?

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    01-13-2004
    Posts
    42

    Thumbs up

    Hi NBVC, that actually almost did the trick (it's matching the correct dates now) except that when I make all the quantity in cells c47 to c99 negative, it still gives "possible", it should only give possible when b2>any a3:99 cell and when the matching c3:c99 cell is positive.

    So for example in the data I attached if I delete all the row 48-99. a47's date is greater than b2, and d2 = d47 so there is a match. But when I change c47 to negative, there will no longer a match since C47 has to be positive. How do I change the formula to incorporate the fact that the match c3:c99 has to be positive for there to be a match.

    Also, can you explain the B2<Max(A3:A99), is there a way to say B2<any A3:a99 cell, don't know if this is necessary though.

    Thanks so much!

    Quote Originally Posted by NBVC
    still a little confused..

    does this do what you want?

    Please Login or Register  to view this content.
    Last edited by opeyemi1; 02-14-2008 at 10:31 AM.

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    can you explain the B2<Max(A3:A99)
    That simply looks for the latest date in A3:A99 and checks that the date in B2 is less than that latest date....If so, it returns TRUE so that you get a "Possible" returned.

    is there a way to say B2<any A3:a99 cell?
    The way to do that is to add this statement....OR(B2<A3:A99)

    But this would turn your formula into an array formula and would have to confirmed with CTRL+SHIFT+ENTER keys not just the ENTER key with normal formulas....

    Formula would be:

    Please Login or Register  to view this content.
    confirmed with CTRL+SHIFT+ENTER

  11. #11
    Registered User
    Join Date
    01-13-2004
    Posts
    42
    Can you see my previous post, just before your response to the max date thing. I wanted help with the formula so that in addition to all the above, it would only match when C3:c99 is positive.
    Quote Originally Posted by NBVC
    That simply looks for the latest date in A3:A99 and checks that the date in B2 is less than that latest date....If so, it returns TRUE so that you get a "Possible" returned.



    The way to do that is to add this statement....OR(B2<A3:A99)

    But this would turn your formula into an array formula and would have to confirmed with CTRL+SHIFT+ENTER keys not just the ENTER key with normal formulas....

    Formula would be:

    Please Login or Register  to view this content.
    confirmed with CTRL+SHIFT+ENTER

  12. #12
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    I'm not sure those Vlookups() are appropriate here.... They are looking up the items in row 4, since the date there in A4 is the same as the date in A2...

    Try this formula instead:

    Please Login or Register  to view this content.
    Note: You must confirm this formula by holding the CTRL and SHIFT keys down and then pressing ENTER... you will see { } brackets appear around the formula...

    test it out and let me know.

  13. #13
    Registered User
    Join Date
    01-13-2004
    Posts
    42
    It is working perfect for row 2, but I am not sure why it is not giving me "possible" in row 3 since it meets the same criteria, I have attached the new spreadsheet. Is it because there is only one matched cell, i.e. row 47? (I deleted the other rows 48-99).

    Thanks!
    Attached Files Attached Files

  14. #14
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Your text strings in column D are inconsistent...

    The word "Sun" in D3 has a space after it (which obviously doesn't match what is in D47).

    Make sure you are consistent with the entries.

  15. #15
    Registered User
    Join Date
    01-13-2004
    Posts
    42

    Thumbs up

    You're so right.

    Thank you for your help! It's just perfect.
    Quote Originally Posted by NBVC
    Your text strings in column D are inconsistent...

    The word "Sun" in D3 has a space after it (which obviously doesn't match what is in D47).

    Make sure you are consistent with the entries.

  16. #16
    Registered User
    Join Date
    01-13-2004
    Posts
    42
    Hello,

    I hope by now you are not tired of my questions. I have just been told I have my date logic wrong, B2 actually has to be greater than A3:A99. The original formula is this:
    =IF(OR(LEN(B2)=0,C2>0,ISNA(MATCH(MIN(IF(A3:A99>B2,A3:A99)),A3:A99,0))),"",IF(AND(INDEX(D3:D99,MATCH(MIN(IF(A3:A99>B2,A3:A99)),A3:A99,0))=D2,INDEX(C3:C99,MATCH(MIN(IF(A3:A99>B2,A3:A99)),A3:A99,0))>0,MIN(IF(A3:A99>B2,A3:A99))>0),"Possible",""))

    I tried reversing the greater than sign but I am not that great with excel, so it's not returning "possible"

    =IF(OR(LEN(B2)=0,C2>0,ISNA(MATCH(MIN(IF(A3:A99<B2,A3:A99)),A3:A99,0))),"",IF(AND(INDEX(D3:D99,MATCH(MIN(IF(A3:A99<B2,A3:A99)),A3:A99,0))=D2,INDEX(C3:C99,MATCH(MIN(IF(A3:A99<B2,A3:A99)),A3:A99,0))>0,MIN(IF(A3:A99<B2,A3:A99))>0),"Possible",""))

    so sorry for the inconvenience, thanks!

  17. #17
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Perhaps:

    Please Login or Register  to view this content.
    This formula, in the case of your sample data, will look at the date in Row 45 (2/14/2006) since it is the latest date that is greater than the date in B2.

    Is that what the logic is supposed to be now?

  18. #18
    Registered User
    Join Date
    01-13-2004
    Posts
    42
    Your formula works and gives the result "possible", thanks much again!
    Can you please explain to me why we are using Max instead of Min. I changed formula to Min just to see what would happen and no "possible". If B2>min of A3:a99, A3 qualifies for example as the min, why it is not returning a result. Not that this matters since I am getting the result, I am just wondering if I will be missing something when I have a huge data and not able to look at each row.

    =IF(OR(LEN(B2)=0,C2>0,ISNA(MATCH(MIN(IF(B2>A3:$A$28,A3:$A$28)),A3:$A$28,0))),"",IF(AND(INDEX(D3:$D$28,MATCH(MIN(IF(B2>A3:$A$28,A3:$A$28)),A3:$A$28,0))=D2,INDEX(C3:$C$28,MATCH(MIN(IF(B2>A3:$A$28,A3:$A$28)),A3:$A$28,0))>0,MIN(IF(B2>A3:$A$28,A3:$A$28))>0),"Possible",""))

  19. #19
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    I used Max because I figured you wanted to compare to the latest date that is greater than the date in B2.... if you want to compare to the earliest date that is greater than date in B2, you can use MIN, but you will need to add another condition that ignores cells with no dates (which are interpreted as 0, e.g. cells A48:A99)...therefore, you can use:

    Please Login or Register  to view this content.
    where each MIN(IF()) function has an additional condition: IF(A3:$A$99>0)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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