Showing Formula Result as plain text value

[markswjh]

I have a cell with the following formula

=IF(D5="H","1.5",IF(D5="M","F2.8",IF(D5="L","F2.2",IF(D5="",""))))

this gives a child's expected grade.

I then need another cell to work out the numeric value of that grade with vlookup:

=VLOOKUP(G5,Sheet2!$A$2:$B$14,2,FALSE)

That Vlookup uses the following table to work out the value of the cell.

I am assuming that Vlookup doesn't like working with a base value that is the outcome of a formula, so I have attempted to show the outcome of the If formula in another cell using either a =text(G5,0) formula or a =value(G5). The value formula defaults to a #VALUE error every time the student's grade value changes and the text formula keeps rounding the result (if the child scores a grade 1.5 it lists it as 2.

This then throws off the next formula I am running.

Any help on a formula to show the exact value of the original cell without throwing up endless errors and rounding would be invaluable!

Thank you!

Images below:

Fine with one value but if the value changes:

Thank you!!

 

[Fluff]

Try using

=IF(D5="H",1.5,IF(D5="M","F2.8",IF(D5="L","F2.2",IF(D5="",""))))

that way the 1.5 is a number & not text.

 

[markswjh]

Great idea, tried it and it has the same outcomes!

 

[Fluff]

But does the vlookup work?

 

[markswjh]

why yes it does... Perfectly!

Thanks

 

[Fluff]

You're welcome & thanks for the feedback.