Allow excel to read a rounded number as a whole number in a table?

Read round up value.jpg
Table

Mathematics
Attainment
Phase 2 3 #N/A
Phase 3 4 #N/A
Phase 4 2 Weak

Overall 3 #N/A

Look up Table
1 Very Weak
2 Weak
3 Acceptable
4 Good
5 Very Good
6 Outstanding


I am trying to create a table so that the value in column 2 will use the look up table value and place the grade into the grid.e.g.
Phase 2 3 Acceptable
Phase 3 4 Good
etc

The problem I have is that the value in column 2 is a rounded up value for another part of the table and although it says 3 the actual value is 2.8 and the look up does not return the acceptable?
Can you tell me how I can rectify this as I am stumped!
Thanks
Ken

If the actual value in the cell is 2.8 but it is showing 3, it is not rounded up, it is just using a format which doesn't display the decimal places. So, it depends on what is an acceptable return. You could use ROUND, ROUNDUP, or ROUNDDOWN with zero decimal places, or you could use INT to take the integer part of the number. Or you could change your lookup function. If you have XLOOKUP, that has options to take the next higher or lower value if there is not an exact match.

There are many options but you need to determine what is acceptable.


Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

The formula does NOT need to be copied down. Delete all results and use this in the FIRST cell only:

IFERROR(XLOOKUP(ROUND(F7:F11,0),F13:F18,G13:G18,,0),"")

refer to the file.