VLOOKUP not working - trying to return a number depending on a text character

[jonnylowes]

Hi,

I have a spreadsheet that stores a persons grade as either say 5-, 5 or 5+. This column is stored as text. I have created a range of cells that has the text version of the score (i.e 6+ and I want to return the number 6, or if it's 5+, 5- I would return number 5).

At present my vlookup is working correctly for all text values with a + or - after the number. However the text values 3,4,5,6,7 and 8 don't seem to be working with the vlookup. The value returned is always 1 score lower...i.e. lookup "5" it returns a 4.

Any ideas how I could fix this?

Thanks

 

[James006]

Hi,

When it comes to retrieving data from a table .. you should the Index-match combination ...

Hope this will help

 

[Aladin Akyurek]

Care to post the formula you have?

 

[AliGW]

James - a VLOOKUP is perfectly OK in this case. INDEX MATCH is an alternative method.

Jonny - please show us what your lookup table looks like.

 

[inactiveuserps07]

Yes, Johnny, it always helps to see a screenshot of what you're working with.

As I'm reading your post, I'm wondering why you need VLOOKUP at all, since it sounds like you're just trying to convert text numbers with/without symbols to the text number alone (i.e., "5-", "5" or "5+" would all return "5", etc.), in which case you really just need to use the LEFT function:

=LEFT(cell_reference, 1)

This would return "5" for any of the above options.