# Formula error - IF(AND...

G

#### Guest

HI!
Please I need help for this...
I tried to put more conditions, but excel says that formula contains an error.
You know an alternative for this ?
=IF(AND(G2>=C2;G2<=D2);E2;IF(AND(G2>=C3;G2<=D3);E3;IF(AND(G2>=C4;G2<=D4);E4;IF(AND(G2>=C5;G2<=D5);E5;IF(AND(G2>=C6;G2<=D6);E6;IF(AND(G2>=C7;G2<=D7);E7;IF(AND(G2>=C8;G2<=D8);E8;0))))))

Thanks for yor help.

N

#### Niek Otten

Look in HELP for the VLOOKUP function

B

#### Bob Phillips

You are hitting Excel's 7 nested functions limit. You will need to break it
down.

--

HTH

RP
(remove nothere from the email address if mailing direct)

Pasmatos said:
HI!
Please I need help for this...
I tried to put more conditions, but excel says that formula contains an error.
You know an alternative for this ??
=IF(AND(G2>=C2;G2<=D2);E2;IF(AND(G2>=C3;G2<=D3);E3;IF(AND(G2>=C4;G2<=D4);E4;

G

#### Guest

Hi,

Try this array formula and confirm with CTRL-SHIFT-ENTER. Modify the ranges
appropriately (note that the ranges in columns C, D, and E should be equal in
length). The formula assumes that E63000 is empty, which I am sure, will be
the case.

=INDIRECT("E"&MIN(IF((\$G\$2>=\$C\$2:\$C\$101)*(\$G\$2<=\$D\$2:\$D\$101)*ROW(\$C\$2:\$C\$101)<>0,ROW(\$C\$2:\$C\$101),63000)))

Regards,
B. R. Ramachandran