Long nested if statements

[mufakkir]

Hi guys,
I have a long nested if statement as follows:
IF(D4="3c","3a",IF(D4="3b","4c",IF(D4="3a","4b",IF(D4="4c","4a",IF(D4="4b","5c",IF(D4="4a","5b",IF(D4="5c","5a",IF(D4="5b","6c",IF(D4="5a","6b",IF(D4="6c","6a",IF(D4="6b","7c",IF(D4="6a","7b","False"))))))))))))

The above displays the text based on the inputted text in D4 (for arguments sake). However, I have two more long nested if statements like the above. They are as follows:

Nested IF statement 2: =IF(D4="3c","4c",IF(D4="3b","4b",IF(D4="3a","4a",IF(D4="4c","5c",IF(D4="4b","5b",IF(D4="4a","5a",IF(D4="5c","6c",IF(D4="5b","6b",IF(D4="5a","6a",IF(D4="6c","7c",IF(D4="6b","7b",IF(D4="6a","7a","False"))))))))))))

Nested IF statement 3:
=IF(D5="3c","4b",IF(D5="3b","4a",IF(D5="3a","5c",IF(D5="4c","5b",IF(D5="4b","5a",IF(D5="4a","6c",IF(D5="5c","6b",IF(D5="5b","6a",IF(D5="5a","7c",IF(D5="6c","7b",IF(D5="6b","7a",IF(D5="6a","8c","False"))))))))))))

Individually they work fine. However, I have another column in which users input either 3, 4 or 5. Now I want the correct nested if statement to execute based on which value (3,4 or 5) is entered. Lets call this cell A5

So this is how I want it to execute: If A5 = 3 then nested IF statement 1, If A5 = 4 then nested IF statement 2 and so forth.
Is this possible? Or am I doing it all wrong?

[oeldere]

Without seeing your excel file, without confidential information, it is hard to tell for me.

So please add the excel file. without confidential information.

Please also add manualy the desired (expected) result.

I think a VLookup will solve your problem.

[daddylonglegs]

This would probably be easier with a lookup table

If you put the possible values for D4 in, for example, in the range W2:W13 and then the return values from formula 1 in X2:X13, return values from formula 2 in Y2:Y13 and return values from formula 3 in Z2:Z13, and then the possible A5 values in the header row, 3 in X1, 4 in Y1 and 5 in Z1 then you can use a simpler formula like this:

=IFERROR(INDEX($X$2:$Z$13,MATCH(D4,$W$2:$W$13,0),MATCH(A5,$X$1:$Z$1,0)),"false")

[mufakkir]

This would probably be easier with a lookup table

If you put the possible values for D4 in, for example, in the range W2:W13 and then the return values from formula 1 in X2:X13, return values from formula 2 in Y2:Y13 and return values from formula 3 in Z2:Z13, and then the possible A5 values in the header row, 3 in X1, 4 in Y1 and 5 in Z1 then you can use a simpler formula like this:

=IFERROR(INDEX($X$2:$Z$13,MATCH(D4,$W$2:$W$13,0),MATCH(A5,$X$1:$Z$1,0)),"false")

I wish I was able to follow you. I am a overworked teacher trying to make my life easier I will add my workbook with comments to help as the reply above suggested. Thanks

[mufakkir]

student levels book.xlsx

My workbook is attached. Some columns have comments to help you understand. I am extremely grateful for your help.

[oeldere]

With index / match.

See the green cells in the attached file.

I changed the table (see the blue cells).

[mufakkir]

With index / match.

See the green cells in the attached file.

I changed the table (see the blue cells).

Wow! You are amazing. Could I possibly trouble you for a little longer. I would also like the 'Actual' cells to turn red of they are below the 'Target' values. As you have seen my workbook, perhaps you know what I mean. I need the colour of the cell to change so that I know a student is not working to target. Of course if he gets more than or equal to his target, the cell should turn green. Please only help me with this if its no trouble

[oeldere]

Your new question is possible with conditial format.

But I suggest you study on the way the formula index / match works, so you can change the formula (and table) in your real sheet.

Edit:

You have to explain how you define the criteria below target

e.g.
H4 = "4c"
(for cel I4 and the result is "5b") is this under or above the target.

[mufakkir]

Your new question is possible with conditial format.

But I suggest you study on the way the formula index / match works, so you can change the formula (and table) in your real sheet.

Edit:

You have to explain how you define the criteria below target

e.g.
H4 = "4c"
(for cel I4 and the result is "5b") is this under or above the target.

I will try

[mufakkir]

The hierarchy of levels is as follows:
3c - 3b - 3a - 4c - 4b- 4a - 5c- 5b - 5a - 6c - 6b - 6a - 7c - 7b - 7a etc

So if Actual is 4c but Target is 4b then this is below target.

[oeldere]

Below is the tabel you use.

Please explain the logic for the input 3c

Here can be the target 3a or e.g.4c.

The value 3a is below 3c.
The value 4c is above 3c.

Y7 Entry 31 32 33 34 35 36
3c 3a 3a 4c 4c 4b 4b

[mufakkir]

Thanks, but I've managed to do it with conditional formatting. I used a formula to turn each level into a figure and then checked if lower, equal or higher. Quite pleased with myself
Thank you very much for your help.

[oeldere]

Thanks for the reply.

Great you are able to solve the problem yourself and replying that on the forum.

Since you are new on the forum:

You can add rep(utation)points to the one who helped you, by clicking on the star on the left side.