Adding Values to Drop Down Menu Text Options

Hi folks,

I'm new here (and to Excel as well!) and would appreciate some help.

I'm trying to add numerical values to the text that I have put in a drop down list, I had a look about and followed the advice given in this thread. However, as soon as I put more than 3 entries into a formula like this:

=LOOKUP(A1,{"Freshman","Sophomore","Junior","Senior"},{"1","2","3","4"})

It returns a #N/A value for most of the entries, and does not assign the correct values to the text.

I've checked that all the spelling etc. matches, and am at a bit of a loss. I'm trying to use a list of 14 names, and assign the relevant points value to them. It's for creating a homebrew Army Builder.

Many thanks

Welcomed to the forum, Hammie.

Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

Thanks.

you must arrange them in ascending order
=LOOKUP(A1,{"Freshman","Junior","Senior","Sophomore"},{1,3,4,2})