# How do I assign a number value to a non-numerical value in Excel

I have some survey responses with values like "VU" for "Very Unlikely" and "S" for Sometimes. Each survey response occupies a cell. For each cell containing a survey response, I want to fill another cell with a corresponding number. For example, for every cell containing "VU" I want to fill a corresponding cell with the number 1.

How is this done?

I would have a seperate table in another tab that details the values:

``````Code   Value
VU     1
U      2
S      3
L      4
VL     5
``````

Then, in a new column, on the survey I would add this formula:

``````=VLOOKUP(A2,CodeSheet!\$A\$2:\$B\$6,2,0)
``````

where A2 refers to the respondents answer for that row and Codesheet refers to the sheet where you put the above value table.

Now you should be able to sort by the new column.

• I tried this approach and it kept failing because the codes aren't in alphabetical order. Apr 29 '10 at 17:58
• Did you have the last argument in the formula? the ',0)' without the quotes? The order doesn't matter if you do. Apr 30 '10 at 15:51
• =CHOOSE(A2,"VU","U","S","L","VL") Jun 1 '18 at 15:12

You can easily do this with a formula. Something like:

``````=IF(A1="VU",1,IF(A1="S",2,IF(A1="L",3,IF(A1="VL",4,""))))
``````

Where A1 is the cell containing the response.

I used Very Unlikely, Sometimes, Likely, Very Likely as possible responses.

It would also be possible to create a VBA macro to do this, but i think that would be complicating it...

I wound up settling on a good old-fashioned array look-up like this:

``````=LOOKUP(J4, {"A","D","NS","O","R","S","SA","SD","VO","VR";4,2,3,4,2,3,5,1,5,1})
``````

However, in some cells, the cell reference that is the LOOKUP's first argument pointed to an empty cell resulting in an "N/A" error. My response to this was to apply the NOT() and ISBLANK() functions like so:

``````=IF(NOT(ISBLANK(J4)),LOOKUP(Sheet1!J4, {"A","D","NS","O","R","S","SA","SD","VO","VR";4,2,3,4,2,3,5,1,5,1}),"")
``````
• I think you may have over-complicated this solution. Apr 30 '10 at 15:56
• Nothing personal. I don't think it's the best answer. May 7 '10 at 16:23
• I have come to understand the inelegance of my solution and now I share your opinion that the answer you offered is superior. Thank you for downvoting me all those years ago so I could find it again today! Jul 4 '14 at 2:06