How to combine all these IF's into one formula

[KcrazyCowboy]

Hi guys and gals,

Needing some help if figuring out how to create one formula that can provide what i need. Which is to have several "IF" statements returned with the right answer. I would like to find a way to create the formula that looks up each word and returns into with a numerical value.

=IF(B2="FIRM",0,"")
=IF(B2="FORECAST",1,"")
=IF(B2="ALLOCATION",2,"")
=IF(B2="DEFICIT",3,"")
=IF(B2="SAFETY",4,"")

 

[Sufiyan97]

Try

=IF(B2="FIRM",0,IF(B2="FORECAST",1,IF(B2="ALLOCATION",2,IF(B2="DEFICIT",3,IF(B2="SAFETY",4,"")))))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Another option

=IFNA(MATCH(B2,{"FIRM","FORECAST","ALLOCATION","DEFICIT","SAFETY"},0)-1,"")

 

[KcrazyCowboy]

Try

=IF(B2="FIRM",0,IF(B2="FORECAST",1,IF(B2="ALLOCATION",2,IF(B2="DEFICIT",3,IF(B2="SAFETY",4,"")))))

thank you!

 

[Sufiyan97]

You're welcome!
Glad we could help.