Explanation

In this example, the goal is to lookup data to the left of an ID that appears as the last column in the table. In other words, we need to locate a match in column E, then retrieve a value from a column to the left. This is one of those problems that is difficult with VLOOKUP , but easy with INDEX and MATCH or XLOOKUP . Both options are explained below.

Background study

  • What is an Excel Table (3 min. video)
  • Introduction to structured references (3 min. video)
  • How to use INDEX and MATCH (overview)
  • Excel Tables (overview)

INDEX and MATCH

One of the advantages of using INDEX and MATCH over VLOOKUP is that INDEX and MATCH can easily work with lookup values in any column of the data. In the example shown, columns B through E contain product data with a unique ID in column E. Using the ID in column G as a lookup value, the formulas in the range H5:J6 use INDEX and MATCH to retrieve the correct item, color, and price. In cell H5, the formula used to lookup Item is:

=INDEX(data[Item],MATCH(G5,data[ID],0))

Working from the inside out, the MATCH function is used to locate the value in G5 in the ID column like this:

MATCH(G5,data[ID],0) // returns 3

Here, the lookup_value is G5, the lookup_array is data[ID] (E5:E15), and match_type is set to zero for an exact match. The result from MATCH is 3, since the ID 1003 occurs in the third row of the table. This value is returned directly to the INDEX function as row_num . With array provided as data[Item], INDEX returns “T-shirt” as a final result:

=INDEX(item,3) // returns "T-shirt"

The same approach is used to retrieve the correct item, color, and price. The formulas in H5, I5, and J5 are as follows:

=INDEX(data[Item],MATCH(G5,data[ID],0)) // get item
=INDEX(data[Color],MATCH(G5,data[ID],0)) // get color
=INDEX(data[Price],MATCH(G5,data[ID],0)) // get price

Notice the MATCH function is used exactly the same way in each formula. The only difference is the array given to INDEX. Once MATCH returns 3 as a result for ID 1003, we have:

=INDEX(data[Item],3) // returns "T-shirt"
=INDEX(data[Color],3) // returns "Black"
=INDEX(data[Price],3) // returns 19

Each formula above returns the correct result for the ID 1003.

Locking references

The formulas above use normal references to make them easier to read. To lock references so that the same formula can be copied into the range H5:J6, we need to adjust the formula as follows:

=INDEX(data[Item],MATCH($G5,data[[ID]:[ID]],0))

Notice the reference to $G5 is now a mixed reference with the column locked. Also notice the reference to the ID column in the Excel Table data is also locked with the following syntax:

data[[ID]:[ID]] // locked

This syntax is unique to structured references , which are a feature of Excel Tables .

Video: How to copy and lock structured references

XLOOKUP function

The XLOOKUP function is a modern replacement of the VLOOKUP function. One of the features that VLOOKUP lacks, and XLOOKUP provides, is the ability to “look left” in a lookup operation. The equivalent XLOOKUP formulas to retrieve Item, Color, and Price are:

=XLOOKUP(G5,data[ID],data[Item]) // item
=XLOOKUP(G5,data[ID],data[Color]) // color
=XLOOKUP(G5,data[ID],data[Price]) // price

In these formulas lookup_value comes from G5, lookup_array is always data[ID] , and return_array is the column from which to return data.

In addition, because XLOOKUP runs on the new dynamic array engine in Excel , you can retrieve values from all three columns at the same time like this:

=XLOOKUP(G5,data[ID],data[[Color]:[ID]])

This will cause XLOOKUP to return all three values at the same time as a spill range .

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:

Restructured lookup table - 1

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.