# Simplify formula that checks student year level and mark to a letter grade

I am writing a Google Sheet that checks a student's year level, then assigns a letter grade based on their mark in an assessment. If there is no mark, it is designed to leave the cell blank.

The following formula is successful, if a bit clunky. Is there a more elegant solution to this problem?

`=IF(COUNTBLANK(AA12)>=1,"",IFS(P12=5,(IF(AA12>32,"A", (IF(AA12>28,"B",(IF(AA12>19, "C", (IF(AA12>16, "D", "E")))))))),P12=6,(IF(AA12>37,"A", (IF(AA12>32,"B",(IF(AA12>24, "C", (IF(AA12>19, "D", "E")))))))),P12=4,(IF(AA12>28,"A", (IF(AA12>24,"B",(IF(AA12>16, "C", (IF(AA12>12, "D", "E")))))))),P12=3,(IF(AA12>23,"A", (IF(AA12>19,"B",(IF(AA12>12, "C", (IF(AA12>8, "D", "E"))))))))))`

The students grade is stored in Column 'P', and their mark in Column "AA"

Any suggestions are welcome.

I basically used `VLOOKUP` on its true state. According to the Documentation:

If `is_sorted` is `TRUE` or omitted, the nearest match (less than or equal to the search key) is returned. If all values in the search column are greater than the search key, #N/A is returned.

So we have 4 lookup tables, one for each grade. I used named ranges to reference each easily. I had to add one to each grade since `VLOOKUP` will look for less than or equal, while your formula only less than. Easy enough fix.

Each lookup table also has to be sorted (lowest to highest). This is important. You might as well just copy and paste my Data spreadsheet onto yours to keep it consistent.

The main formula uses just uses `SWITCH` to differentiate between grades. You're going to need to adjust the references a bit, but that's simple enough. Here's an adjusted formula:

``````=SWITCH(
P12,
3,
VLOOKUP(
AA12,
2
),
4,
VLOOKUP(
AA12,
2
),
5,
VLOOKUP(
AA12,
2
),
6,
VLOOKUP(
AA12,