Excel IF statement

[ajduk1]

Hi
I have a spread sheet where I want to return a value if a field equals a specific value.
in the statement below you see if($EJ14="K1" then return "FERT".
The thing is I idealy want to return "FERT" if the $EJ14 field equals anything ending in 1, I have tried adding a wildcard so that it is only looking at the number i.e. anything ending in 1 as below.
=IF($EJ14="?1","FERT" this returns "FALSE"

=IF($EJ14="K1","FERT",IF($EJ14="K3","HALB",IF($EJ14="K5","HAWA",IF($EJ14="K7","ROH",IF($EJ14="P1","FERT",IF($EJ14="","INFO"))))))
This works but sometimes the values are not preceded with a "K" it may be a "S" or a "T".
Any help would be appreciated.

AJD

AJD

 

[GlennUK]

How about using the RIGHT function, as in RIGHT($EJ14,1) instead, and compare to "1" and so on?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Gavona]

Some functions allow wildcards others don't. The workaround would be:
=IF($EJ14="?1","FERT"

Wildcards & various Excel functions: thread68-1253790

Gavin

 

[ajduk1]

hi Gavona
As stated in the initial question, the formula =IF($EJ14="?1","FERT" gives me a "FALSE" return. I went with the RIGHT function (GlennUK)and it works a treat.
Thanks for all your help.
AJD

AJD

 

[GlennUK]

My pleasure! :-D

Cheers, Glenn.

Beauty is in the eye of the beerholder.