Enter a letter code in one cell and represented number in another cell

I will do my best to explain what I’m looking for – I hope it makes sense! I have included a bit of my worksheet so you can hopefully understand easier. As you can see I have a table of information and a Key to explain what abbreviations mean and the point values. Ideally, I would like to be able to type in the abbreviation in the “Occurance” column and have a formula or function set up to automatically enter the point value in the “Point Change” Column. I have already assigned a new name for each abbreviation so that when I type “=NL [Enter]” in the “point change” column it automatically changes to the coresponding point value for “NL” or “No Lunch”, but I still have to type “NL” in the “occurence” column. Is there a way to get this function to happen in one step? Maybe even typing “=NL [Enter]” into the “point Change” column and having the code “NL” automatically entered into the “Occurance” column? I would like to find some way to get it to one step where I enter the information once and the points and abbreviation are automatically inserted in their places. I feel like Excel has a way to do this and I just don’t know how to get it to happen. Thank you for your help!


          NL No Lunch 0.5
Date Occurrence Description Point change   T 10-60 min. Late 1
2/1/18 T Clocked in at 8:15 1   TT 61-120 Late 2
3/5/18 NL No lunch taken 0.5   LE Leave Early, unexcused 2
          TTT 2+ Hours Late, all day absence 3


By: Rosy

One thought on “Enter a letter code in one cell and represented number in another cell

  1. Rosy

    I apologize, it looks like the other half of the Key is cut off. There is one more column with a point number for each row.

Leave a Reply

Your email address will not be published. Required fields are marked *