In Excel, you may often need to look up a value in a table based on another value in the same row. For example, you may want to find the name of a product based on its ID, or the price of a service based on its code. This is a common task that can be easily done with the VLOOKUP function, which searches for a value in the first column of a table and returns a corresponding value from another column in the same row.
However, what if you need to look up a value in a column that is not the first one in the table, and return a value from a column to the left of it? For example, you may want to find the ID of a product based on its name, or the code of a service based on its price. This is a problem that VLOOKUP cannot handle, because it can only look up values to the right of the lookup column. In this case, you need to use a different approach, such as the combination of INDEX and MATCH functions, or the new XLOOKUP function (available in Excel 365 and Excel 2021).
In this article, we will explain the basic theory behind these functions, and show you how to use them to perform a left lookup in Excel. We will also provide a detailed example with real data, and compare the advantages and disadvantages of each method.
VLOOKUP
The VLOOKUP function has the following syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
where:
- lookup_value is the value you want to find in the first column of the table.
- table_array is the range of cells that contains the table of data.
- col_index_num is the number of the column in the table that contains the value you want to return.
- range_lookup is an optional argument that specifies whether you want an exact match (FALSE) or an approximate match (TRUE or omitted).
For example, suppose you have a table of products and their prices in the range A1:B5, and you want to find the price of the product with ID 102. You can use the following formula:
=VLOOKUP(102,A1:B5,2,FALSE)
This formula will search for the value 102 in the first column of the table (A1:A5), and return the value from the second column in the same row (B1:B5). The result is 15. The FALSE argument indicates that you want an exact match, meaning that the lookup value must be found in the table. If you omit this argument, or use TRUE, the function will return an approximate match, meaning that the lookup value can be equal to or less than the closest value in the table. This is useful when you have a sorted table and you want to find the nearest value.
However, if you try to use VLOOKUP to look up a value in a column that is not the first one in the table, and return a value from a column to the left of it, you will get an error. For example, if you want to find the ID of the product with name “Pen”, you cannot use the following formula:
=VLOOKUP(“Pen”,A1:B5,1,FALSE)
This formula will return #N/A, because VLOOKUP cannot find “Pen” in the first column of the table. VLOOKUP can only look up values to the right of the lookup column, not to the left.
INDEX and MATCH
To overcome this limitation of VLOOKUP, you can use the combination of INDEX and MATCH functions. The INDEX function has the following syntax:
=INDEX(array, row_num, [column_num])
where:
- array is the range of cells that contains the values you want to return.
- row_num is the number of the row in the array that contains the value you want to return.
- column_num is an optional argument that specifies the number of the column in the array that contains the value you want to return. If omitted, the function will return the entire row.
The MATCH function has the following syntax:
=MATCH(lookup_value, lookup_array, [match_type])
where:
- lookup_value is the value you want to find in the lookup array.
- lookup_array is the range of cells that contains the values you want to search for.
- match_type is an optional argument that specifies how you want to match the lookup value. It can be 0 for an exact match, 1 for a less than or equal to match (requires a sorted array), or -1 for a greater than or equal to match (requires a sorted array).
By using these two functions together, you can perform a left lookup in Excel. For example, suppose you have the same table of products and prices in the range A1:B5, and you want to find the ID of the product with name “Pen”. You can use the following formula:
=INDEX(A1:A5,MATCH(“Pen”,B1:B5,0))
This formula will use the MATCH function to find the position of “Pen” in the lookup array B1:B5. The result is 3, meaning that “Pen” is in the third row of the array. Then, the INDEX function will use this result as the row_num argument, and return the value from the array A1:A5 in the same row. The result is 102, the ID of the product with name “Pen”.
The advantage of using INDEX and MATCH over VLOOKUP is that you can look up a value in any column of the table, and return a value from any column in the same row. You are not limited by the position of the lookup column or the return column. You can also use different arrays for the INDEX and MATCH functions, as long as they have the same number of rows.
XLOOKUP
If you have Excel 365 or Excel 2021, you can use the new XLOOKUP function to perform a left lookup in Excel. The XLOOKUP function has the following syntax:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
where:
- lookup_value is the value you want to find in the lookup array.
- lookup_array is the range of cells that contains the values you want to search for.
- return_array is the range of cells that contains the values you want to return.
- if_not_found is an optional argument that specifies what you want to return if the lookup value is not found. You can enter any value, such as a text, a number, or a formula. If omitted, the function will return #N/A.
- match_mode is an optional argument that specifies how you want to match the lookup value. It can be 0 for an exact match, -1 for an exact match or next smaller item (requires a sorted array), 1 for an exact match or next larger item (requires a sorted array), or 2 for a wildcard match (allows the use of * and ? characters).
- search_mode is an optional argument that specifies the direction you want to search the lookup array. It can be 1 for a search from first to last (default), -1 for a search from last to first, 2 for a binary search in ascending order (requires a sorted array), or -2 for a binary search in descending order (requires a sorted array).
The XLOOKUP function is a more powerful and flexible version of the VLOOKUP function. It can look up values in any direction, and return exact matches by default. It also has more options to handle different scenarios, such as not found values, wildcard matches, and binary searches.
For example, suppose you have the same table of products and prices in the range A1:B5, and you want to find the ID of the product with name “Pen”. You can use the following formula:
=XLOOKUP(“Pen”,B1:B5,A1:A5)
This formula will use the XLOOKUP function to find the value “Pen” in the lookup array B1:B5, and return the corresponding value from the return array A1:A5. The result is 102, the ID of the product with name “Pen”. The if_not_found, match_mode, and search_mode arguments are omitted, so the function will use the default values of #N/A, 0, and 1, respectively.
The advantage of using XLOOKUP over INDEX and MATCH is that you can use a single function instead of two, and you have more control over the behavior of the function. You can also use different arrays for the lookup_array and return_array arguments, as long as they have the same size.
Example
To illustrate how to use these three methods to perform a left lookup in Excel, let’s use a real data set of the top 10 countries by GDP (nominal) in 2020, according to the World Bank. The data is in the range A1:C11, as shown below:
| Country | GDP (US$ billions) | Rank |
|---|---|---|
| United States | 20936.74 | 1 |
| China | 14342.90 | 2 |
| Japan | 4954.79 | 3 |
| Germany | 3805.31 | 4 |
| United Kingdom | 2827.11 | 5 |
| India | 2626.64 | 6 |
| France | 2611.22 | 7 |
| Italy | 1861.29 | 8 |
| Canada | 1642.21 | 9 |
| Russia | 1477.50 | 10 |
Suppose we want to find the country name based on the rank, or the rank based on the country name. For example, we want to find the country name for rank 7, or the rank for country India. We can use any of the three methods we discussed above to perform a left lookup in Excel. Here are the formulas and the results for each method:
| Method | Formula | Result |
|---|---|---|
| VLOOKUP | =VLOOKUP(7,A1:C11,2,FALSE) | France |
| INDEX and MATCH | =INDEX(A1:A11,MATCH(7,C1:C11,0)) | France |
| XLOOKUP | =XLOOKUP(7,C1:C11,A1:A11) | France |
| Method | Formula | Result |
|---|---|---|
| VLOOKUP | =VLOOKUP(“India”,A1:C11,3,FALSE) | 6 |
| INDEX and MATCH | =INDEX(C1:C11,MATCH(“India”,A1:A11,0)) | 6 |
| XLOOKUP | =XLOOKUP(“India”,A1:A11,C1:C11) | 6 |
As you can see, all three methods can return the correct value for a left lookup in Excel. However, there are some differences in terms of performance, flexibility, and compatibility.
Comparison of Methods
Performance
The performance of a formula depends on how fast it can calculate the result, and how much memory it consumes. Generally, the XLOOKUP function is the fastest and most efficient method, because it uses a binary search algorithm that can find the lookup value in a large array in a few steps. The INDEX and MATCH functions are also fast, but they use a linear search algorithm that can take longer to find the lookup value in a large array. The VLOOKUP function is the slowest and least efficient method, because it always scans the entire lookup column, even if the lookup value is found in the first row.
Flexibility
The flexibility of a formula depends on how well it can handle different scenarios, such as different lookup directions, different match types, different array sizes, and different error handling. Generally, the XLOOKUP function is the most flexible method, because it can look up values in any direction, return exact or approximate matches, use different arrays for the lookup and return values, and return custom values if the lookup value is not found. The INDEX and MATCH functions are also flexible, but they require two functions instead of one, and they cannot return custom values if the lookup value is not found. The VLOOKUP function is the least flexible method, because it can only look up values to the right of the lookup column, return approximate matches by default, use the same array for the lookup and return values, and return #N/A if the lookup value is not found.
Compatibility
The compatibility of a formula depends on how widely it can be used in different versions and platforms of Excel. Generally, the VLOOKUP function is the most compatible method, because it is available in all versions and platforms of Excel. The INDEX and MATCH functions are also compatible, but they require two functions instead of one. The XLOOKUP function is the least compatible method, because it is only available in Excel 365 and Excel 2021, and it may not work in some older or non-Microsoft applications that support Excel files.
