[RESOLVED] Excel and nested IF's

[codge]

Hi, just a question on nested if's, I assume there are only so many nested if statements you can use (8?) but I need to check on around 20 cells values before updating the current cell with a specific value, is there another way of doing this;

One of my cells has the following, and due to the limitations of the IF statement I can't then calculate on the remaining cells

=IF(Sheet1!$C$10=A3,"Apples",IF(Sheet2!$C10=A3,"Pears",IF(Sheet3!$C10=A3,"Oranges",IF(Sheet4!$C10=A3 ,"Plums",IF(Sheet1!$C15=A3,"Apples",IF(Sheet2!$C15=A3,"Pears",IF(Sheet3!$C15=A3,"Oranges",IF(Sheet4! $C15=A3,"Plums",""))))))))

On sheets 1 to 4 I have around 20 cells I need to check if they are equal to the value presented to A3 on sheet5, similarly this is then used to check other values on sheet5.

Hope I've made myself clear, and someone can help, regards

[DKenny]

You could use an OR statement for each possible output, but it is limited to 30 possible options.

=IF(OR(Sheet1!$C$10=A3,Sheet1!$C15=A3,etc,etc),"Apples",IF(OR(Sheet2!$C10=A3,Sheet2!$C15=A3,etc,etc) ,"Pears",IF(OR(....

[Spajeoly]

Use a Select case since you know which cells you're looking at... You can nest ifs in those as well as Or's.

Edited to add.....

I am thinking in VBA, but it will still work.

Select Case Range Sheet1.Range("$C$10") 'Eh, I think that's right...
 
Case "Apples"
 
Case "Plums"
 
Case "Etc."
 
End Select

[codge]

Hi, thanks for the replies, the IF(OR works well and I think everything is working fine, thanks again for the replies and help