LinkBack URL
About LinkBacks
opeyemi1 
Thanks, please see attached.
I am not really sure what I need, could be a vlookup. Thanks again.
Thanks, please see attached.
I am not really sure what I need, could be a vlookup. Thanks again.
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")
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!
Originally Posted by shg
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?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.
Please specify using cell references where a match occurs and what results we should see.
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.
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!
still a little confused..
does this do what you want?
=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<MAX(A3:A99)),"Possible",""))
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!
still a little confused..
does this do what you want?
=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<MAX(A3:A99)),"Possible",""))
Last edited by opeyemi1; 02-14-2008 at 10:31 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks