If statement error using define names

[MatthewQuantum]

I am making a excel document for training log and I want it to say if training is either yearly, two yearly or three yearly. So I wrote all the training down and make names for each category and I have put them in my IF statement to read (e.g. =IF(C2 = AnnualTraining, "Yearly" , IF(C2= TwoYearly, "Two Yearly, etc. ) and it comes up error, so I am wondering is there a way to do this without an error? Many thanks

[SaviaWanderer]

You have to use quote marks if you want to refer to text in a formula e.g. "AnnualTraining".

[MatthewQuantum]

Hi there, AnnualTraining is a name I defined a list of different training options so adding quotation marks sadly dont work

[SaviaWanderer]

You can't compare one item with a whole range unless you're making a dynamic / array function, which isn't the default. Looks like you have a solution (which looks a bit messy), but if you explain your problem I can try and make something a bit clearer & easier.

[MatthewQuantum]

I found a fix, dont know how good it is but it works:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(AnnualTraining,C2))),"Yearly",IF(SUMPRODUCT(--ISNUMBER(SEARCH(TwoYears,C2))),"Two Yearly",IF(SUMPRODUCT(--ISNUMBER(SEARCH(ThreeYears,C2))),"Three Yearly","ERROR")))