The specified formula cannot be entered because it uses more levels of nesting than are allowed in t

[remahesh]

Hi,

I am getting the following error when I use more than 7 IF condition in Excel.

Error Message:The specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format.

Formula:
=IF($C$2=C5,E5,IF($C$2=C6,E6,IF($C$2=C8,E8,IF($C$2=C9,E9,IF($C$2=C10,E10,
IF($C$2=C11,E11,IF($C$2=C12,E12,IF($C$2=C13,E13,IF($C$2=C14,E14,IF($C$2=C15,E15,""))))))))))

Please help to check for more IF conditions.

Many Thanks
Maggi

 

[alow]

are you just trying to show the row that has a match? If so there are other ways to do it, if not, explain what you're trying to do. If you're in Excel 2003, you're pretty stuck to 7 nested IF formulas without an add-in.

 

[alow]

Correction, I misread your formula. You're trying to find the data in Column E that has a value in column C that matches what's in C2. In that case, you want to use
=VLOOKUP($C$2,$C$5:$E$15,3,0)

 

[remahesh]

Hi,
I am checking whether Entered Value in C2 is equal to C5/C6/C7 etc then I need to bring E5/E6/E7.

Example: the formula is for E2 cell. If the Entered Value 2 in C2 then it will show ADMIN in the E2 Cell (where this formula is planced)

Column C Column D Column E
Serial No. Employee Name Department Name
1 James HR
2 Andy Admin
3 Crook Finance
4 Gareth IT

Column D2 formula : =IF($C$2=C5,D5,IF($C$2=C6,D6,IF($C$2=C7,D7,IF($C$2=C8,D8,""))))

Column E2 Formula: =IF($C$2=C5,E5,IF($C$2=C6,E6,IF($C$2=C7,E7,IF($C$2=C8,E8,""))))

If I use more than 7 If condition in the above formula i am getting an error.

Please let me know if you need any other information.

Many Thanks
Maggi

 

[alow]

The VLOOKUP formula in my 2nd response should cover it. Let me know how that works.

 

[remahesh]

Sorry the formula is not working. (=VLOOKUP($C$2,$C$5:$E$15,3,0) )

Moreover the selected column in the formula is from C5 TO E15 but, I need check column C and column E. We should not include column D in the formula.

Many Thanks
Maggi

 

[alow]

The VLOOKUP formula is what you need, but what I typed might not be EXACTLY how you need it to work. The VLOOKUP needs 4 arguments:

1: Lookup Value, the value you want to use to lookup a value in a table (C2)
2: Lookup Array, the table that contains the column with the values to match the lookup value to and the column that has the data you want to pull. The VLOOKUP looks in the first column of this array only for a match to the lookup value and can only be a contiguous range, this is why I have it looking at $C$5:$E$15.
3: Column Index: This is the number of the column in the lookup array that you want to pull the data from. I used 3 because the lookup array was columns C through E and E is the third column in that array.
4: Range Lookup, Whether or not to look for an exact match with the Lookup Value against the first column of the lookup array. False, or 0, is the only way to get a reliable result from this formula in my experience. True, or 1, only works if everything is set up a very particular way.

Hopefully the explanation will allow you to modify the formula as needed. As long as there is an EXACT match of the lookup value in the first column of the lookup array, the formula I gave should be working though. Good luck!

 

[remahesh]

Thanks a lot... It works fine...