The If function in Numbers

Question

Level 1

8 points

The If function in Numbers

The If function in Numbers only allows 3 nested items. What function can I use to select a response from a set of 5 responses, as follows: Normal, Mild, Moderate, Severe, Extremely Severe?

MacBook Pro with Touch Bar

Posted on Sep 2, 2019 5:55 PM

Reply

Answer 1

Top-ranking reply

t quinn

Level 5

7,063 points

Sep 2, 2019 7:49 PM in response to Cracker60

Hi Cracker60,

What you want is a lookup table. I use INDEX/MATCH instead of VLOOKUP or HLOOKUP becasue it is more powerful and actually easier to use.

So I have a popup menu in A2 with the 5 situations as choices.

B2=INDEX(lookup::B,MATCH(A2,lookup::A,0),column-index,area-index)

The formual looks for a match of A2 in the lookup table column A. If it finds an exact match it returns the vallue in column B.

quinn

Reply

Answer 2

Barry

Level 9

60,047 points

Sep 3, 2019 12:41 AM in response to Cracker60

More than 3 IF statements can be nested:

B2: IF(A2="Normal","OK",IF(A2="Mild","Use caution",IF(A2="Moderate","Keep calm and carry on",IF(A2="Severe","Batten down the hatches","Panic!!!"))))

But beyond three conditions the formula gets ugly and difficult to troubleshoot and maintain. Moving to a Lookup table is a better choice.

The table can be external, as described in quinn's post above, or can be written into the formula:

B2: INDEX("OK","Use caution","Keep Calm…","Batten down the hatches!","Panic!!!"),MATCH("Normal","Mild","Moderate","Severe","Extremely severe",0)

Regards,

Barry

Reply