Formatting Percentages/LOOKUP using a Percentage

[eed]

I have a column in a spreadsheet in which I need to return a letter
sequence/"score".

The sequence is based on two criteria:
1. LOOKUP - for which the reference data is a list of percentages in the
following format: 1.00%.
2. A column containing a formula which calculates a percentage.

The letter sequence/score will not generate, and shows as #N/A.
I have tried copy/paste special values to remove the formula, but the
sequence will still not generate because the percentages are in the following
format: 1.000000000000000%.

I have to manually re-key the percentage for every cell in order to get the
score to generate in the 'score' column.

Please help - thanks!

 

[eed]

I tried what you sent, but still got #N/A, so here are the formulas, etc:

1. The 'variance' column (O) contains this formula: TY/LY-1, and shows a
percent to 2 decimal places.

2. The 'score' column (Q), contains this formula:
=VLOOKUP(P5,scoring!$A:$B,2,0) and is referencing another wksheet, 'scoring'

3. The scoring tab contains a column (A) with a range of possible
percentages, as well as a column containing the corresponding letter score.
(see below for example)
-100.00% DNM
-99.99% DNM
-99.98% DNM
-99.97% DNM
-99.96% DNM
ETC...

Thanks!!

 

[eed]

grrr. have tried so many times and it just won't work...the formula for the
'scoring' tab worked fine, but it's the formula for the score column
vlookup/conversion to text that isn't working for me. I am getting the
message that I have an error in my formula every time. This is great though -
totally on the right track, just can't for the life of me figure out what the
error is!