Lookup

[JB]

Hello
I have a huge spreadsheet that has results ranging from 1 to 7 in apx 50
columns . I need to convert them to Letters.
7=A
6 =B etc down to 1 which is G.

Amongst that there are decimals which for example
1.2 to 1.4 =G
1.5 to 2.4 =F
2.5 to 3.4 = E etc

I tried doing a VLOOKUP but although most of them are correct, there some
that aren't and I can't work out why.
This is what I did.
I named a range in another sheet "Grades" with one column with all the
numbers and decimals and the second column has the letters they need to be
converted to.

In the main sheet, I entered a formula into an empty column adjacent to a
column I need to convert.
=VLOOKUP(J4,Grades,2)
J4 being the first cell that has to be converted. Then I apply all the way
down 200 rows.

The ones that are incorrect are from 5.5 upwards to 6.2. They should be
showing B but are all C
And 6.3 and 6.4 should be B but they too are C. And then from 6.5 up to 7
they are correctly showing A.
Hope I'm making sense.

Please explain why this is happening. I would be quicker at this rate to do
it manually.
Then once I get that sorted I have to copy the column of formula next to
each 50 columns?

Ta
J

 

[AltaEgo]

Debra Dalgleish's site has some samples that should help you get your
Vlookup working :
http://www.contextures.com/xlFunctions02.html


If you don't need a dynamic grade table, consider the Choose() function as
an alternative:

=CHOOSE((A3+0.5),"G","F","E","D","C","B","A")

NOTE: you may need to tweak the +.05 to get the result returning exactly as
you wish.

 

[Pete_UK]

Could you post the table that you used for the lookups (Grades)? This
needs to be sorted on the first column, and only needs to have the
lowest value for each range.

Pete

 

[JB]

Thanks for both replies. It's sorted. The problem was the formatting of the
column with the numbers. I had them as 'number'. I now changed it to
General and the lookup works a treat.
Jx