if statement with vlookup - returning false

[jgold20]

I have 3 different conditions and I am trying to use vlookup. Only the 1st if(and statement works. The 2nd and 3rd if(and return FALSE from the vlookup (***note each if(and statement works by its self***). Can I use if(and vlookup with the statements below?. The 2nd set of code works

=IF(OR(A9=""),"",
IF(AND($D$3="yes"),IF(OR($A$3={"D","DT","V","X"}),(J9*5%+125)),
IF(AND($D$3="yes"),IF(OR($A$3={"GE","SR","VP"}),VLOOKUP($A$3,'Company Info'!$A$1:$L$25,8,FALSE)*D9),
IF(AND($D$3="yes"),IF(AND(D9<3333.34),VLOOKUP($A$3,'Company Info'!$A$1:$L$25,8,FALSE)*D9,200),0))))


WORKS
=IF(OR(A9=""),"",
IF(AND(D3="yes"),IF(OR(A3={"D","DT","V","X"}),(J9*5%+125),0))+
IF(AND(D3="yes"),IF(OR(A3={"GE","SR","VP"}),(D9*E6),0))+
IF(OR(A3={"B","H","L","M","MD","MN","N","NO","Q","R","W","WG","Y"}),
IF(AND(D3="yes"),IF(AND(A9<3333.34),(D9*E6),200))))

 

[Dave Patton]

Try building your formula one step at a time.
I looked at the first few parts of your formula.
The second alternative yields a 0 for a False evaluation.
The 99 is just a dummy result until formula replaces that part.

Excel 2010
	A	B
1	130	130
4d

Cell Formulas
Range	Formula
A1	=IF(A9="","",IF(OR($A$3={"D","DT","SR","V","X"}),IF($D$3="YES",J9*5%+125,99)))
B1	=(OR($A$3={"D","DT","SR","V","X"}))*($D$3="YES")*(J9*5%+125)

 

[jgold20]

I have tried one at a time. I am now receiving - [FONT=&quot]You've entered too many arguments for this function. On the last statement:

[/FONT]=IF(AND(A9=""),"",
IF(OR($A$3={"D","DT","SR","V","X"}),
IF(AND($D$3="YES"),(J9*5%+125)),
IF(OR($A$3={"GE","SR","VP"}),
IF(AND($D$3="YES"),VLOOKUP($A$3,'Company Info'!$A$1:$L$25,8,FALSE)*D9)),
IF(and($D$3="YES"),IF(and(D9<3333.34),VLOOKUP($A$3,'Company Info'!$A$1:$L$25,8,FALSE)*D9,200)))

 

[JackDanIce]

I think it's over complex which may be why you're struggling to get the logic to work.

The first line

=IF(AND(A9=""),"",

What is the other logical test paired for the AND? Your formula suggests none; usually AND has two tests, e.g. =IF(AND(A9=1, D10=0),TRUE,FALSE)

You may be able to reduce the number of tests you're doing, try this by itself and see if you can include into yours:

=LOOKUP(D3&A3,{"YESGE","YESSR","YESVP";1,2,3})

That is similar as

IF(OR($A$3={"GE","SR","VP"},IF(AND($D$3="YES")

but again it seems like you only have one test for both OR and AND when they should have at least 2

I think reason for the error is you haven't got the number of arguments correct, due to the incorrect use of AND and OR in your functions.

 

[Dave Patton]

Why are you using "And" with one ONE item to evaluate?

try
IF(D9<3333.34,VLOOKUP($A$3,'Company Info'!$A$1:$L$25,8,FALSE)*D9,200)

N.B. We do not know what you are trying to achieve and we cannot see your sheet.

 

[jgold20]

The first if is in error, it should be just an "if" statement which I have corrected. Here is what I am trying to accomplish:

=IF((A9=""),"",
IF(OR($A$3={"D","DT","SR","V","X"}),
IF(AND($D$3="YES"),(J9*5%+125)),

If a3 = any of the above and d3=yes, than J9*5%+125



IF(OR($A$3={"GE","SR","VP"}),
IF(AND($D$3="YES"),VLOOKUP($A$3,'Company Info'!$A$1:$L$25,8,FALSE)*D9)),

If a3 = any of the above and d3=yes, vlookup * d9


IF(and($D$3="YES"),IF(and(D9<3333.34),VLOOKUP($A$3,'Company Info'!$A$1:$L$25,8,FALSE)*D9,200)))

If D3 = yes and D9<3333.34, Vlookup *d9 if it is > 3333.34, the value should be 200

if D3=NO, the value should be 0 for all if statements

 

[jgold20]

D3 has to be yes along with D9<3333.34 for the Vlookup*d9. If D9>3333.34 the value is 200.

If D3=NO for all the value should be 0

 

[JackDanIce]

In your very first IF, you are only doing one logical test inside the OR and one logical test inside the AND. This is not how OR or AND works in my experience.

This is one way to rewrite your first IF:

=IF(A9=")",IF(AND(ISNUMBER(MATCH($A$3,{"D","DT","SR","V","X"},0)),$D$3="YES"),J9*5%+125))

 

[jgold20]

The code returned a false

 

[Dave Patton]

With 100 in J9

Excel 2010
	A	B	C	D	E
1	130	130		130
4d

Cell Formulas
Range	Formula
A1	=IF(A9="","",IF(OR($A$3={"D","DT","SR","V","X"}),IF($D$3="YES",J9*5%+125,99)))
B1	=(OR($A$3={"D","DT","SR","V","X"}))*($D$3="YES")*(J9*5%+125)*(D9<3333.34)
D1	=IF(A9="","",IF(AND(ISNUMBER(MATCH($A$3,{"D","DT","SR","V","X"},0)),$D$3="YES"),J9*5%+125))