Nested IF statements within a range

[jthrum]

Hi All,

I'm looking to create an if statement that returns a specific phrase if a cell matches cells within another range exactly.

So far this is what I've got.

=IF(C2='Model List'!G2:G62,"H15",(IF(C2='Model List'!$E$2:$E$17,"H17",(IF(C2='Model List'!$C$2:$C$71,"H20",(IF(C2='Model List'!$A$2:$A$200,"H25","")))))))

the formula works for models within the first if statement, however, anything after that automatically skips to the final 'if false' in the last if statement, being "".

i'm not sure what's wrong with my formula. If anyone could help me out i'd really appreciate it!

Thanks Guys!

 

[Michael M]

MAybe this

=IF(C2='Model List'!G2:G62,H15,IF(C2='Model List'!$E$2:$E$17,H17,IF(C2='Model List'!$C$2:$C$71,H20,IF(C2='Model List'!$A$2:$A$200,H25,""))))

 

[jthrum]

MAybe this

=IF(C2='Model List'!G2:G62,H15,IF(C2='Model List'!$E$2:$E$17,H17,IF(C2='Model List'!$C$2:$C$71,H20,IF(C2='Model List'!$A$2:$A$200,H25,""))))

Sorry, the "H15","H17","H20","H25" are statements, not cell references.

 

[Michael M]

Okay, so simply put quotes around them

=IF(C2='Model List'!G2:G62,"H15",IF(C2='Model List'!$E$2:$E$17,"H17",IF(C2='Model List'!$C$2:$C$71,"H20",IF(C2='Model List'!$A$2:$A$200,"H25",""))))

 

[jthrum]

Okay, so simply put quotes around them

=IF(C2='Model List'!G2:G62,"H15",IF(C2='Model List'!$E$2:$E$17,"H17",IF(C2='Model List'!$C$2:$C$71,"H20",IF(C2='Model List'!$A$2:$A$200,"H25",""))))

Yeah, that's what I had in the original formula. However, if I try to use the formula in the proceeding cells, the formula doesn't return the correct statement, it just skips to the final "if false" which turns up "". The first cell I created the formula in registers all nested if statements. But I need all of the cells after that, in the same column, to act in the same way.

 

[Michael M]

Yeah, Sorry the non cell references threw me for a loop

=IF(COUNTIF('Model List'!G2:G62,C2)>0,"H15",IF(COUNTIF('Model List'!$E$2:$E$17,C2)>0,"H17",IF(COUNTIF('Model List'!$C$2:$C$71,C2)>0,"H20",IF(COUNTIF('Model List'!$A$2:$A$200,C2)>0,"H25",""))))

 

[jthrum]

Yeah, Sorry the non cell references threw me for a loop

=IF(COUNTIF('Model List'!G2:G62,C2)>0,"H15",IF(COUNTIF('Model List'!$E$2:$E$17,C2)>0,"H17",IF(COUNTIF('Model List'!$C$2:$C$71,C2)>0,"H20",IF(COUNTIF('Model List'!$A$2:$A$200,C2)>0,"H25",""))))

That worked like a charm, it's perfect! Thanks so much for your help!

 

[Michael M]

Glad we got it sorted...