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?
Best Answer
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.