You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Numbers Noob> Can I give Pop-up menu options a numerical value?

Is there a way to give options in a Pop-up menu a numerical value so that once selected, the corresponding value appears in an adjacent cell?


For example:

Pop-up menu options in Cell A1:

(1) Fruit

(2) Vegtable


If I selected “(1) Fruit” in Cell A1 a “1” would appear in Cell A2.


Any help would be greatly appreciated,

Stu

Posted on Feb 22, 2023 2:52 PM

Reply
Question marked as Top-ranking reply

Posted on Feb 22, 2023 8:30 PM

A simple way that generally does not require cluttering up your document with extra tables is to use the SWITCH function:




=SWITCH(A2,"fruit",1,"vegetable",2)


This is easy to edit. Let's say you decide to add "meat" and assign it a 3. Simply double click the cell to call up the formula editor and change the formula to:


SWITCH(A2,"fruit",1,"vegetable",2,"meat",3)


As you can see, you simply have a reference to the cell with the Pop-Up Menu, in this simple example A2, and follow that by switch-value, if-match pairs. You can have many pairs.


More on SWITCH here (also accessible via Help > Formulas and Function Help in your menu):


SWITCH - Apple Support


You can get fancier and add a default value for when there are no matches, etc., but generally that is not needed because Numbers will warn you automatically if there are no matches.


Substitute ; for , in the above formulas if your region uses , as a decimal separator, e.g.:


=SWITCH(A2;"fruit";1;"vegetable";2)



SG


2 replies
Question marked as Top-ranking reply

Feb 22, 2023 8:30 PM in response to radzombie

A simple way that generally does not require cluttering up your document with extra tables is to use the SWITCH function:




=SWITCH(A2,"fruit",1,"vegetable",2)


This is easy to edit. Let's say you decide to add "meat" and assign it a 3. Simply double click the cell to call up the formula editor and change the formula to:


SWITCH(A2,"fruit",1,"vegetable",2,"meat",3)


As you can see, you simply have a reference to the cell with the Pop-Up Menu, in this simple example A2, and follow that by switch-value, if-match pairs. You can have many pairs.


More on SWITCH here (also accessible via Help > Formulas and Function Help in your menu):


SWITCH - Apple Support


You can get fancier and add a default value for when there are no matches, etc., but generally that is not needed because Numbers will warn you automatically if there are no matches.


Substitute ; for , in the above formulas if your region uses , as a decimal separator, e.g.:


=SWITCH(A2;"fruit";1;"vegetable";2)



SG


Feb 22, 2023 4:09 PM in response to radzombie

If it is a very small list you can do all in the one cell. If it is a larger list or one you will be adding to or modifying, you would use a lookup table. The screenshot has both methods.




Row 2 is the method that does it all in one cell (it does not use Table 2).

Table 1::A2 =XLOOKUP(A1,{"fruit","vegetable"},{1,2},"not found",0)

drag-fill the formula to B2


Row 3 uses the lookup table (Table 2)

Table 1::A3 =XLOOKUP(A1,Table 2::$A,Table 2::$B,"not found",0)

drag-fill the formula to B3


With the lookup table you can add new items to the lookup table, change the number associated with an item, or make other changes without having to modify any formulas.

Numbers Noob> Can I give Pop-up menu options a numerical value?

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.