Problem with LOOKUP function - impossible?

Hello again,

I am in need of some more help.

My data at the moment looks like this:

4B
4C
4A
2B
2C
2A
1B
1A
ETC

Each level above has a number associated with it, e.g. 1c = 9, 1b = 10, 1a = 11, 2c = 12, 2b = 13, 2a = 14, 3c = 15, 3b = 16, 3a = 17, 4c = 18 and so on...

As far as I am aware there isn't a way of getting a cell to display text (e.g. 4B) as well as having a hidden 'number' or 'value' of 18. So what I am trying to do is to create a column to the right of this column which displays the cell to the left of its numerical value so that I can work with the numbers.

So in order to convert text to numbers, I have used this formula =LOOKUP(P84,{"4C","4B","4A"},{"17","18","19"}) Just as a test to see if it would work. However, it doesn't as it gives the value of 4C - 19, which is incorrect.

Can you correct my formula or help me with a multiple IF, THEN function?

Thanks in advance

Joanne

Thanks so much for your reply!!

indexhelp.xlsx

I have attached the file that I am working with as I am having trouble getting this to work. What am I doing wrong?

Joanne

Brilliant thanks!!!