Formula not working

**Santasanta** · 11-05-2012, 03:45 PM

I am doing some data analysis and -I am quite new to excel and a formula I am trying to input does not seem to work. I have marks /grades ---- ie 3-,3,3+ 4-,4,4+ etc and I am trying to use a formula to assign numerical values so that I can get the top student in the group. =LOOKUP(B6,{"3-","3","4","4+"},{"1","2","3","4"}) I tried this but it does not work-its fine for the 3- and 4+ but not for 3,4

Thanks for your help.

**kbkumar** · 11-05-2012, 03:50 PM

Welcome to the forum Santasanta. Could you please upload a workbook with sample data? Would be helpful to see the way your data is organized.

Thanks.

**TMS** · 11-05-2012, 03:55 PM

The problem is that you have text values in both sides of the lookup, that is, everything in quotes is text. Now "4+" is text, whether or not it is in quotes, and is not equal a numeric value of 4 ... which, in this instance, is not what you want. Similarly, "3-" is text, with or without quotes. 3 and -3 are numbers.

So, if you put 3-, 3+, 4-, 4+, etc in cell B6, they will be treated as text and find a match. However, if you type 3 or 4, it will be seen as a number and not match "3" or "4". If you put a single quote in front of the 3 or 4 the value will be treated as text and find a match.

Regards, TMS

**Santasanta** · 11-05-2012, 04:47 PM

Thanks TMShucks It now works

**TMS** · 11-05-2012, 04:51 PM

You're welcome. Thanks for the rep.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save