3

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:

=LOOKUP(R3,Calculations!B2:C7)

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 Tuinstra Oct 2 '14 at 18:59
  • Tried =VLOOKUP(R3,Calculations!B2:C7,2,0), but same result. – Tigt Oct 2 '14 at 19:05
  • If R3 = 'Master' then this would be the correct answer.... – Jacob Jan Tuinstra Oct 2 '14 at 21:02
3

Please try:

=vLOOKUP(R3,Calculations!$B$2:$C$7,2,0)  

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 '14 at 20:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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