I have a dropdown that selects from several text "keys," like so:

List of items: Pathetic,Untrained,Novice,Adept,Master,Amazing

I'm trying to use these keys to run a lookup on a separate sheet, with the following formula:


Where R3 is the cell with the dropdown, Calculations is the sheet in question, and from B2 to C7 the cells are like this:

| Pathetic  | 1 |
| Untrained | 2 |
| Novice    | 3 |
| Adept     | 4 |
| Expert    | 5 |
| Master    | 6 |

However, I only seem to be able to get it to return the number 6, the final value in the attempted lookup table. Am I missing an argument?

  • The VLOOKUP accepts a third and fourth argument as well. Try setting that to 2 and 0.
    – Jacob Jan
    Oct 2, 2014 at 18:59
  • Tried =VLOOKUP(R3,Calculations!B2:C7,2,0), but same result.
    – Tigt
    Oct 2, 2014 at 19:05
  • If R3 = 'Master' then this would be the correct answer....
    – Jacob Jan
    Oct 2, 2014 at 21:02

1 Answer 1


Please try:


For LOOKUP (as opposed to VLOOKUP) to work properly the data must be sorted.

The anchors ($) are in case say the formula was to be copied down (eg to return a value from R4). Without them the array range would slide down automatically until by R9 the search would be of Calculations!B8:C13.

  • Thanks for the explanation! When you say the data must be "sorted," is that a more specific state that I have to inform the program of?
    – Tigt
    Oct 2, 2014 at 20:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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