Explanation
One of theVLOOKUP function’skey limitations is that it can only look up values to the right.
There is no way to override this behavior since it is hardwired into the function.
Essentially, we are asking choose for both the first and second values.
The values are provided as the two named ranges in the example: score and rating.
Notice however that we are providing these ranges in reversed order.
With INDEX and MATCH
While the above example works fine, it isn’t ideal.
For one thing, most average users won’t understand how the formula works.
A more natural solution isINDEX and MATCH.
The values provided to CHOOSE can include references.
