VLOOKUP is very useful for finding and retrieving related information in a table (or dataset). This function isn’t perfect though. It suffers from certain limitations.
One of the limitations VLOOKUP impose is the fact that the lookup column must be the first column in the range. That means you cannot perform VLOOKUP to the left of the key field.
The very well-known technique to avoid this particular VLOOKUP limitation is to use other functions (the MATCH and INDEX functions) instead of the VLOOKUP function. In this tutorial, we’ll look at these functions one after the other and how to use them to perform a left lookup.
Note: Before I proceed, let me mention that there’s no ideal way VLOOKUP can look to the left of the key field (not even in the 2019 version of Excel). Therefore, to look to the left, you will have to use the technique here. However, if you must use the VLOOKUP function, the obvious solution is to switch columns in your data so that the lookup column will come first before the index_num column.
Understanding the MATCH function
The MATCH() function is used to look for a value in a range and then retrieve the position of the cell that matches the specified value. The MATCH() function depends on other functions to be useful. It doesn’t do much on its own.
This function goes with three arguments outlined below:
MATCH Function Syntax:
MATCH(lookup_value, lookup_array, [match_type])
- lookup_value: This represents the value you are looking for. This could either be text or numbers.
- lookup_array: This is the range you are searching through. The range must be one-dimensional. This means that you can only search through one column or one row (like A1:A10 or A1:G1), but you can’t search a grid of cells (like A1:G10 which include several rows and columns).
- match_type: This argument is used to specify how the match is determined. A match_type of 0 (the number zero) will retrieve the position of the first item that matches exactly. In most cases, the 0 match_type is used so I recommend you to always stick to it. A match_type of 1 will retrieve the position of the largest value that’s equal to or less than the searched value. A match_type of -1 gives you the position of the smallest value that’s equal to or greater than the searched value.
Note: The match_type of 1 and -1 are rarely used in the real world. So always stick to using a match type of 0. Also, if you fail to specify a match type, Excel will automatically assign 1 as your match type which may not be good for you. Consider this as a warning.
Example of MATCH() function
Above is a table that contains customer order information. And there are about 10 records in this table.
To illustrate the MATCH() function, let’s write a formula to retrieve the position of a customer named Jacob Sophia in the customer list.
The MATCH() formula in cell D2 says the name “Jacob Sophia” is in the 6th location in the list. However, if there’s no match, the function will return #N/A which means that what you are looking for is not in that column.
To grasp how this formula works, you’ll have to try it yourself. Also, check this step by step guide on how to use the MATCH function.
Understanding the INDEX function
As you can see, the MATCH() function only does half of the work of VLOOKUP by returning only the position of the search term. It can’t even retrieve the actual content of the cell.
It is clear that the MATCH() function isn’t so useful on its own. It becomes useful only when you combine it with another function (like the INDEX function)
The INDEX() function retrieves a value from a range of cells, using the index number you specify.
This function has the following arguments:
INDEX Function Syntax:
INDEX(array, row_num, [column_num])
- array: This represents a range of cells.
- row_num: This represents a row number in a range.
- column_num: This represents a column number in a range.
INDEX() function example
Using the table above, lets illustrate how the index function works.
Assuming the above table is sorted in a way that, the first name in the list is our first customer, the second name as our second customer, then in that order up to the end.
Using the index function, we’ll find and retrieve the product our 5th customer bought. So, consider the following formula: