Explanation
One of the VLOOKUP function’s key limitations is that it can only look up values to the right. In other words, the column that contains lookup values must sit to the left of the values you want to retrieve with VLOOKUP. There is no way to override this behavior since it is hardwired into the function. As a result, with normal configuration, there is no way to use VLOOKUP to look up a rating in column B based on a score in column C.
One workaround is to restructure the lookup table itself and move the lookup column to the left of the lookup value(s). That’s the approach taken in this example, which uses the CHOOSE function reverse rating and score like this:
CHOOSE({1,2},score,rating)
Normally, CHOOSE is used with a single index number as the first argument, and the remaining arguments are the values to choose from. However, here we give choose an array constant for the index number containing two numbers: {1,2}. 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. The CHOOSE function selects both ranges in the order provided and returns the result as a single array like this:
{5,"Excellent";4,"Good";3,"Average";2,"Poor";1,"Terrible"}
CHOOSE returns this array directly to VLOOKUP as the table array argument. In other words, CHOOSE is delivering a lookup table like this to VLOOKUP:
Using the lookup value in E5, VLOOKUP locates a match inside the newly created table and returns a result from the second column.
Reordering with the array constant
In the example shown, we are reordering the lookup table by reversing “rating” and “score” inside the CHOOSE function. However, we could instead use the array constant to reorder like this:
CHOOSE({2,1},rating,score)
The result is exactly the same.
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 is INDEX and MATCH . Here is the equivalent formula:
=INDEX(rating,MATCH(E5,score,0))
In fact, this is a good example of how INDEX and MATCH is more flexible than VLOOKUP.
Explanation
In this example, the goal is to generate a list of people who were invited but did not attend an unspecified event. More specifically, we need to compare the names in B5:B16 against the names in D5:D12 and return the missing names. For convenience, list1 (B5:B16) and list2 (D5:D12) are named ranges . The easiest way to solve this problem in Excel is with the FILTER function and the COUNTIF function, as explained below.
FILTER function
This formula uses the FILTER function to retrieve data based on a logical test built with the COUNTIF function. In the worksheet shown, the formula in cell F5 is:
=FILTER(list1,NOT(COUNTIF(list2,list1)))
Working from the inside out, the COUNTIF function is used to create the actual filter:
COUNTIF(list2,list1)
Notice we are using list2 as the range argument, and list1 as the criteria argument. In other words, we ask COUNTIF to count all values in list1 that appear in list2. Because we are giving COUNTIF multiple values for the criteria, we get back an array with multiple results:
{1;1;0;1;0;1;0;1;1;0;1;1}
Note that the array contains 12 counts, one for each value in list1 . Also, notice that there are 4 zeros in the array. A zero value indicates a name in list1 that was not found in list2 . The 1’s in the array indicate a name in list1 that was found in list2 . Because we want to list names that did not attend , we deliver the result from COUNTIF to the NOT function :
NOT({1;1;0;1;0;1;0;1;1;0;1;1})
The NOT function effectively reverses the result from COUNTIF. Any non-zero number becomes FALSE, and any zero value becomes TRUE. The result from NOT is an array like this:
{FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE}
Notice there are now 4 TRUE values in the array. This is what we need to report the missing names. The array is returned directly to the FILTER function as the include argument, and the FILTER function uses the array as a filter. The result is an array of 4 missing names that land in cell F5 and spill into the range F5:F8. If any data changes, FILTER will recalculate and return an updated list.
