Multiple IF Formulas

Question

I need to link numbers to text so I can trigger conditional formatting for Icon sets. In the simplest way possible I need to link like this.

Minor = 1
Medium = 2
Urgent = 3

The text is selected from a dropdown via data validation. I need to tag a number to the list item and send the number to the cell next to it so I can trigger conditional formatting.

The only issue is, I'm not to well veresed on Excel any assistance would be greatly appreciated.

Heres what I have thus far.

=IF(F7<>"Minor","1","0",IF(F7<>"Medium","2","0",IF(F7<>"Urgent","3","0")))

Possible duplicate of multiple excel if statements to produce value 1,2 or 3 — Grade 'Eh' Bacon
– Grade 'Eh' Bacon, Commented Mar 9, 2016 at 17:00
Did my answer work for you? If so then please mark as correct by clicking the grey/green check mark by the answer. It is something only you can do. — Scott Craner
– Scott Craner, Commented Mar 14, 2016 at 20:23
It did, sorry I can get rather busy at times but I'll always return to amend my questions. Don't worry. — Lewis
– Lewis, Commented Mar 17, 2016 at 10:27

Scott Craner · Accepted Answer · 2016-03-09 16:49:06Z

1

You could do this:

=IF(F7="Minor",1,0)+IF(F7="Medium",2,0)+IF(F7="Urgent",3,0)

The correct way to "nest" the if statement is this:

=IF(F7="Minor",1,IF(F7="Medium",2,IF(F7="Urgent",3,0)))

If you have the latest version of Excel 365 or are using the online version then:

You can use either:

=IFERROR(IFS(F7="Minor",1,F7="Medium",2,F7="Urgent",3),0)

Or, since you are referencing the same cell in each you can use:

=IFERROR(SWITCH(F7, "Minor", 1, "Medium", 2, "Urgent", 3), 0)

edited Mar 9, 2016 at 16:49

answered Mar 9, 2016 at 14:51

Scott Craner

154k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Lewis Over a year ago

Thanks for your elegant solution. Fits the request perfectly! :)

Scott Craner Over a year ago

@Null glad to help, please come back in 10 minutes to mark as correct.

user4039065 Over a year ago

After having mentioned the IFS function, I'm surprized you omitted the SWITCH function. e.g. =IFERROR(SWITCH(F7, "minor", 1, "medium", 2, "urgent", 3), "") sample

Scott Craner Over a year ago

@Jeeped I missed that one, I did not realize they moved it from vba to worksheet function. I hope you don't mind me adding it to the answer above for future reference.

user4039065 Over a year ago

No worries. I'm currently using this as my 'table of contents' for xl2016 (while I suppress my disappointment on not having the new functions in the desktop version).

Collectives™ on Stack Overflow

Multiple IF Formulas

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related