If you have ever tried to return a value that physically lies to the left of the lookup column, you quickly realize that this task is difficult to accomplish with the VLOOKUP function. This is a perfect time to move beyond the VLOOKUP function and explore the INDEX and MATCH functions.
Note: depending on your version of Excel, you may have XLOOKUP as an option … more info here: https://www.excel-university.com/xlookup/
Consider the following screenshot.
We are trying to populate the report’s CustName column. We would like to look up the CustID from the Customers table, and return the CustName. The problem is that in the lookup range, the CustName column lies to the left of the CustID column. That is, the return column lies to the left of the lookup column. VLOOKUP is a righty, can’t go left.*
And this brings us to the reason we are here, to get the hang of using the INDEX/MATCH combination to return values that lie to the left of the lookup column. But wait, there’s more. In addition to being able to return values to the left, this combination doesn’t suffer from another common limitation of the VLOOKUP function. When the third argument of VLOOKUP is expressed as a static integer value, such as 3, a column inserted between the lookup column and the return column will break the function. Since the third argument is expressed as an integer, it will not adapt to column inserts when inserted between the lookup and return columns. The INDEX/MATCH combination does not suffer from this limitation. Since the arguments are range references, Excel adjusts the formulas to accommodate column inserts. Alright, enough talk, let’s get to it.
Let’s take them one at a time. The MATCH function returns the relative position of a list item. If we asked Excel to MATCH “Jun” in a list of month abbreviations, it would return 6. “Apr” would return 4. This idea is illustrated in the screenshot below.
The syntax of the MATCH function follows:
- lookup_value is the value we are trying to find
- lookup_array is where we are looking
- [match_type] is typically 0 for exact match, but there are other choices to explore here
In our screenshot, we use the following function to return the relative position of the month name:
We are trying to find the relative position of our month (C6), in the list of month abbreviations (B11:B22), and zero for exact match.
Just remember, the MATCH function returns the relative position of a list item.
The INDEX function returns the cell value from a range at a given position. Technically, it does much more than that, but since we are just getting warmed up, let’s stick with that idea: it returns a cell value from a list at a given position.
The syntax of the INDEX function follows:
=INDEX(array, row_num, [column_num])
- array is the range that has the value you want to return
- row_num is the relative row position that has the value you want to return
- [column_num] is the optional column number, useful when performing two-dimensional lookups
What we’ll do is use the INDEX function to return a cell value, and we’ll nest the MATCH function in there so that it can tell the INDEX function which row has the value we want. Our INDEX/MATCH formula will look something like this:
Where the MATCH function figures out the row number argument. The MATCH function returns the relative row number to the INDEX function.
Consider the screenshot below:
We used the INDEX function to return a value from the CustName column. For the row_num argument, we used the MATCH function to determine the relative position of the CustID within the CustID column.**
The formula used in C7 is:
- $B$16:$B$27 is the list that contains the value we wish to return, the CustName column
- MATCH(B7,$C$16:$C$27,0) determines the row for the INDEX function
- B7 is the value we are trying to find, our CustID
- $C$16:$C$27 is where we are looking, the CustID column
- 0 means exact match
The INDEX/MATCH combination is quite handy, and can be used to return values that lie to the left of the lookup column.
If you want to play with these functions a bit, feel free to download the IndexMatch Excel file that was used to prepare the screenshots for this post:
* There is a very creative way to have the VLOOKUP function go left, which is to use the CHOOSE function as the second argument. Wow, when I read Richard Schollar’s post on Bill Jelen’s site, I was so impressed! Well done!
** The thing that can be often confusing for long-time VLOOKUPers is the order of the arguments. VLOOKUP begins with the value we are trying to find. We think about the VLOOKUP function like this: go find this, comma, in here, comma, return this. When using the INDEX/MATCH combination, the thinking reverses. We first specify the column that has the value we wish to return. The thinking becomes this: from this column, return this value.