Using if, and, statements with Vlookup

[rogihead]

I have a need to write a formula the if a cell equals a specific range of values and another cell, via vlookup on another sheet equals a specific value, then make the active cell a ZERO, other wise complete a calculation.

For instance I need a composite of this

=IF((K248=####) "Where the #### is equal to a range of 9001-9005"

and

=VLOOKUP(M248,ACQ_COST,3,FALSE) is equal to ACCESSORY

then

,"0",((P248-Q248)*O248))

I can seem to get the individual pieces of the thing together, exception of the 9001-9005 range but not all as one statement.

 

[Joe4]

Maybe something like this:

=IF(AND(K248>=9001,K248<=9005,VLOOKUP(M248,ACQ_COST,3,FALSE)="ACCESSORY"),0,(P248-Q248)*O248)

 

[rogihead]

Thank you much!

 

[rogihead]

There is one more criteria that was just added this morning. If there is data in D2 then this cell should also be a zero.

So in plain text, if K2 is between 9001 and 9005 and the vlookup of M2 on ACQ_COST 3 is ACCESSORY, OR if there data in D2 then this cell should be 0. I have tried using an OR statement with the ISTEXT and NOT(ISTEXT) and I get a formula that gives a result but they are all zero if if there is data in D2 or not.

 

[Joe4]

Try:

=IF(OR(LEN(D2)>0,AND(K248>=9001,K248<=9005,VLOOKUP(M248,ACQ_COST,3,FALSE)="ACCESSORY")),0,(P248-Q248)*O248)

 

[rogihead]

Thank you again.

As I try to grasp the nesting and the if, and ,or I have a question going forward. Should I need to add a criteria, because I know this will happen, to include wound care as well as accessory to the vlookup portion of the statement, so

=VLOOKUP(M248,ACQ_COST,3,FALSE) is equal to ACCESSORY or WOUND CARE

Would the formula be something like this:
=IF(OR(LEN(D257)>0,AND((K257>=9001,K257<=9005,(OR(VLOOKUP(M257,ACQ_COST,3,FALSE)="ACCESSORY"),(K257>=9001,K257<=9005,VLOOKUP(M257,ACQ_COST,3,FALSE)="WOUND CARE")),0,(P257-Q257)*O257) ERROR

=IF(OR(LEN(D257)>0,AND(K257>=9001,K257<=9005,OR(VLOOKUP(M257,ACQ_COST,3,FALSE)="ACCESSORY"),VLOOKUP(M257,ACQ_COST,3,FALSE)="WOUND CARE")),0,(P257-Q257)*O257) DOES NOT GIVE A 0 FOR THE CELL BUT DOES THE CALCULATION SO I THINK IT IS THE PLACEMENT OF THE OR STATEMENT

=IF(OR(LEN(D256)>0,AND(K256>=9001,K256<=9005,VLOOKUP(M256,ACQ_COST,3,FALSE)="ACCESSORY", "WOUND CARE")),0,(P256-Q256)*O256 GIVES #VALUE ERROR

 

[Joe4]

It is really easy to get lost in nesting when dealing with large formulas. I recommend breaking it down like this:
List out all your conditions individually (i.e. Condition1, Condition2, etc).

Then, write the your formula just using those to start, just to get the logic/flow down, i,.e.

=IF(OR(Condition1...,AND(Condition2,Condition3),...

Then, substitute the formulas in for each condition.

Now, if you just wanted to include "or WOUND CARE" in to your logic, you would simply replace this part of your formula:

VLOOKUP(M248,ACQ_COST,3,FALSE)="ACCESSORY"

with this:

OR(VLOOKUP(M248,ACQ_COST,3,FALSE)="ACCESSORY",VLOOKUP(M248,ACQ_COST,3,FALSE)="WOUND CARE")

 

[rogihead]

Thanks I will use those tips definitely.

I was pretty close on the one try.