This tutorial will cover how to use Excel’s index and match formula as an alternative to the vlookup function. One major limitation of vlookup is that it can only look for values to the right of the lookup column. Additionally, vlookup can only return the first match it finds, even if there are multiple matches in the data.
We will combine the index and match functions to overcome these limitations. Let’s start!
Step-by-Step Instructions
In our example, we have order ID and product ID, and we will find the product name, price, warehouse city, and state from the product data table, which includes all these data. So here are the steps you need to follow:
Step 1: Open your Excel spreadsheet and identify the data you want to use in the INDEX and MATCH functions.
Step 2: In the cell where you want the result of the INDEX and MATCH function to appear, type “=INDEX(“, then select the range of data that you want to use in the INDEX function. It will become =INDEX($H$4:$L$12
Step 3: Next, type “MATCH(“, then select the value you want to match in the data range. Next, select the column that you want to return the corresponding value from. In our case, it will become MATCH($B2,$H$4:$H$12,0). The 0 indicates that you want an exact match.
Step 4: For the column argument in the INDEX function, type “MATCH(” and then select the column header that you want to return the corresponding value from. In our example, it will become MATCH(“C$1”,$H$3:$L$3,0)
Step 5: Close both the MATCH and INDEX functions with a bracket. The whole formula should look like this.
=INDEX($H$4:$L$12, MATCH($B2,$H$4:$H$12,0), MATCH(“C$1”,$H$3:$L$3,0)).
Press Enter to get the answer. Drag the formula down to get the answers for all rows.
Conclusion
We hope this tutorial has helped you understand how to use the INDEX and MATCH functions in Excel to overcome the limitation of VLOOKUP. We have only scratched the surface, and INDEX and MATCH can do much more. Since the function is a bit big here, it might feel confusing. Check out our video to understand the whole process in more detail.
If you have any questions or suggestions, please leave a comment below. We would love to hear from you!
