Formula help
 

Why does this formula not work? If I have a product named ABC, DEF, etc, I
want it to use the formula at the end
HLOOKUP(U14,FORMULAS!$T$3:$BF$11,9,FALSE)*U15, but it only uses it for
product OPQ. I think I have the parenthases in the wrong place, but I can't
figure it out.

=IF(U14<"ABC",IF(U14<"DEF",IF(U14<"GEH",IF(U14< "IJK",IF(U14<"LMN",IF(U14<"OPQ",0,HLOOKUP(U14,F ORMULAS!$T$3:$BF$11,9,FALSE)*U15))))))

Formula help
 

Simply put, because you do test for all the conditions, but does nothing when
a condition is true, except right at the end.

=IF(U14<"ABC",IF(U14<"DEF",IF(U14<"GEH",IF(U14< "IJK",IF(U14<"LMN",IF(U14<"OPQ",0,HLOOKUP(U14,F ORMULAS!$T$3:$BF$11,9,FALSE)*U15))))))

Iow, you say that if U14 <ABC, then check if U14<DEF, then check, and so
on. But what must it do if in fact U14 =ABC, or U14 = DEF, or U14 = GEH etc.
Also, with so many checks, it is better to use VLOOKUP.

=IF(ISNA(VLOOKUP(U14,FORMULAS!$T$3:$BF$119,9,0)),0 ,VLOOKUP(U14,FORMUALS!$T$3:$BF$119,9,0)*U15) is maybe what you are looking for.

--
HTH

Kassie

Replace xxx with hotmail


"klappin" wrote:

Why does this formula not work? If I have a product named ABC, DEF, etc, I
want it to use the formula at the end
HLOOKUP(U14,FORMULAS!$T$3:$BF$11,9,FALSE)*U15, but it only uses it for
product OPQ. I think I have the parenthases in the wrong place, but I can't
figure it out.

=IF(U14<"ABC",IF(U14<"DEF",IF(U14<"GEH",IF(U14< "IJK",IF(U14<"LMN",IF(U14<"OPQ",0,HLOOKUP(U14,F ORMULAS!$T$3:$BF$11,9,FALSE)*U15))))))

Formula help
 

Try it like this:

=IF(OR(U14={"ABC","DEF","GEH","IJK","LMN","OPQ"}), HLOOKUP(U14,FORMULAS!$T$3:$BF$11,9,0)*U15,0)

--
Biff
Microsoft Excel MVP


"klappin" wrote in message
...
Why does this formula not work? If I have a product named ABC, DEF, etc,
I
want it to use the formula at the end
HLOOKUP(U14,FORMULAS!$T$3:$BF$11,9,FALSE)*U15, but it only uses it for
product OPQ. I think I have the parenthases in the wrong place, but I
can't
figure it out.

=IF(U14<"ABC",IF(U14<"DEF",IF(U14<"GEH",IF(U14< "IJK",IF(U14<"LMN",IF(U14<"OPQ",0,HLOOKUP(U14,F ORMULAS!$T$3:$BF$11,9,FALSE)*U15))))))

Formula help
 

This is perfect! I've never seen the OR{} before...

Thanks!!!

"T. Valko" wrote:

Try it like this:

=IF(OR(U14={"ABC","DEF","GEH","IJK","LMN","OPQ"}), HLOOKUP(U14,FORMULAS!$T$3:$BF$11,9,0)*U15,0)

--
Biff
Microsoft Excel MVP


"klappin" wrote in message
...
Why does this formula not work? If I have a product named ABC, DEF, etc,
I
want it to use the formula at the end
HLOOKUP(U14,FORMULAS!$T$3:$BF$11,9,FALSE)*U15, but it only uses it for
product OPQ. I think I have the parenthases in the wrong place, but I
can't
figure it out.

=IF(U14<"ABC",IF(U14<"DEF",IF(U14<"GEH",IF(U14< "IJK",IF(U14<"LMN",IF(U14<"OPQ",0,HLOOKUP(U14,F ORMULAS!$T$3:$BF$11,9,FALSE)*U15))))))

Formula help
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"klappin" wrote in message
...
This is perfect! I've never seen the OR{} before...

Thanks!!!

"T. Valko" wrote:

Try it like this:

=IF(OR(U14={"ABC","DEF","GEH","IJK","LMN","OPQ"}), HLOOKUP(U14,FORMULAS!$T$3:$BF$11,9,0)*U15,0)

--
Biff
Microsoft Excel MVP


"klappin" wrote in message
...
Why does this formula not work? If I have a product named ABC, DEF,
etc,
I
want it to use the formula at the end
HLOOKUP(U14,FORMULAS!$T$3:$BF$11,9,FALSE)*U15, but it only uses it for
product OPQ. I think I have the parenthases in the wrong place, but I
can't
figure it out.

=IF(U14<"ABC",IF(U14<"DEF",IF(U14<"GEH",IF(U14< "IJK",IF(U14<"LMN",IF(U14<"OPQ",0,HLOOKUP(U14,F ORMULAS!$T$3:$BF$11,9,FALSE)*U15))))))