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?

3 Answers 3


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:


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.
    – Keyslinger
    Apr 29, 2010 at 17:58
  • 2
    Did you have the last argument in the formula? the ',0)' without the quotes? The order doesn't matter if you do.
    – Sux2Lose
    Apr 30, 2010 at 15:51
  • 1
    =CHOOSE(A2,"VU","U","S","L","VL") Jun 1, 2018 at 15:12

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


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.
    – Sux2Lose
    Apr 30, 2010 at 15:56
  • Nothing personal. I don't think it's the best answer.
    – Sux2Lose
    May 7, 2010 at 16:23
  • 2
    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!
    – Keyslinger
    Jul 4, 2014 at 2:06

Not the answer you're looking for? Browse other questions tagged or ask your own question.