Re: Nest Lookup With IF AND Functions

Thanks AAE!

It's a good thing you asked that question because I just noticed something. I am not comparing the sale date and the purchase date for each row. But before it says "possible" the sale date for that row must be greater than the purchase date for another row that has a d cell that matches d2 and also has a c cell that is a positive quantity. So, row 2 should match rows 47 to 99 (the highlighted rows). How will this formula change?

Thanks again!

Quote from AAEDisplay MoreYour formula:

The wrong result is returned because the first VLOOKUP is not comparing the D2 to the match, but is comparing the match found (D4) to D3 (cell below your reference row). The matched value in cell D4 is equal to D3.

Use this: =IF(LEN(B2)=0,"",IF(C2>0,"",IF(AND(VLOOKUP(A2,$A3:$D$99,4,FALSE)=D2,VLOOKUP(A2,A3:$D$99,3,FALSE)>0,B2>A2),"Possible","")))

Could be shortened slightly to:

=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",""))

Note that I've omitted the test for C2<0 from the AND(). It is not needed because you already test it for being greater than 0.

Why do you need to test if the sale date is greater than the original purchase date. Will it not always be greater? (how can you sell something BEFORE you buy it?)

Also, It looks like Excel is intepreting your values in column D as weekdays. You should change the format to text or enter the value preceded by a single quote.