Is there a way to assign numerical values to text on numbers?
I’m making a spreadsheet with a dropdown menu that has 3 options. Let’s say Bird, Cat, Dog. Each word has a different value attached. The numerical values will not be shown. Then math is to the numerical value. I know the IF function can be used for this but I’m trying to find out if there’s a faster way.
iPhone 15, iOS 26
There are a number of ways of doing this.
The first one that comes to mind is via LOOKUP:
Assuming your values are in column C:
=LOOKUP(C2,{"Bird","Cat","Dog","None"},{1,2,3,0})
Note that the list is disconnected - that is, if you add/remove options to/from the popup menu you'll need to manually adjust the formula to account for it.
The number of keywords and scores must match, so there's one value for each key, otherwise it'll throw an error.
It's also possible to do this via a sub-table where you have a mini-list of the keywords and scores, like:
using a LOOKUP() to find the matching score:
=LOOKUP(C2,Table 3::A,Table 3::B)
which reads as 'look in Table 3::A to find the value in C2, then return the corresponding value from Table 3::B.
The subtable can be hidden or located on another sheet to keep it out of the way, and this makes it easier to adjust the list/scores (you will still need to manually keep it in synch with changes to the popup menu options)
There are a number of ways of doing this.
The first one that comes to mind is via LOOKUP:
Assuming your values are in column C:
=LOOKUP(C2,{"Bird","Cat","Dog","None"},{1,2,3,0})
Note that the list is disconnected - that is, if you add/remove options to/from the popup menu you'll need to manually adjust the formula to account for it.
The number of keywords and scores must match, so there's one value for each key, otherwise it'll throw an error.
It's also possible to do this via a sub-table where you have a mini-list of the keywords and scores, like:
using a LOOKUP() to find the matching score:
=LOOKUP(C2,Table 3::A,Table 3::B)
which reads as 'look in Table 3::A to find the value in C2, then return the corresponding value from Table 3::B.
The subtable can be hidden or located on another sheet to keep it out of the way, and this makes it easier to adjust the list/scores (you will still need to manually keep it in synch with changes to the popup menu options)
Take a look at the Top Ranking answer in this discussion: Auto fill cell based on drop down menu - Apple Community. It shows the reverse of what you are trying to do, using the INDEX and MATCH functions. Just flip the arguments.
The other option would be to use the IFS (plural) function that can check multiple values in one call.
Assign numerical values to text from dropdown in Numbers
