Have you ever wanted to pull up specific term or phrase within your document and a value that might be associated with it? This would be difficult to perform from multiple tabs and your information in different columns. The Index match function in Excel surpasses the VLOOKUP feature by allowing you return a value based on the defined array/ column you have set, even for a row number. If you don’t want to change your columns in other tabs or sheets, you can simply use the Index Match feature in Excel to find the exact data you need without changing a thing, unlike its search counterpart, VLOOKUP. Now you can finally calculate information across all your pages to get the information you need to succeed.
How to Use Index Match Formula
- To get started using an Index Match function in Excel, you’ll first need to choose the location you want to receive your data. You can create a new column and title this section whatever you want. Just make sure you are able to indicate the meaning of this value later on with this new title.
- Next, you will enter one of the formulas below. The structure of the Index Match function is as follows :
- =INDEX(table array, MATCH formula)
- When you enter the formula into your cell, you can use separate variables for different uses: =INDEX(table array, MATCH (lookup_value, lookup_array))
- If you took the time to download the free file located at the bottom of this tutorial, you will see the following formula was used in order to create the Index match function: =INDEX(Sheet2!A:A,(MATCH(Sheet1!C:C,Sheet2!C:C,0)))
- Below is a short explanation of the different variables that can be used in an Index Match function.
You can use multiple different variables in this formula to achieve a different result. Some of the most common are the Array, in which the formula will take the range of data in your columns on the second sheet and transfer that data to the first sheet.
The Lookup Value will search the selected column in the first sheet and calculate data that has the same values in all sheets.
Finally, Lookup Array will search the column in the second sheet and calculate the value of your selected data with the same values in all sheets.
Download: Uisng the Index Match Function in Excel
- How to Use VLOOKUP in Excel
- 3D SUM Function in Excel
- Creating Arrays in Excel
- COUNT Matches Template
- COUNT Between Numbers Template
View this offer while you wait!