INDEX MATCH Formula
In this tutorial, you will understand the way to use INDEX MATCH function in excel. INDEX MATCH formula is useful to find particular data in a group of columns and rows on a spreadsheet by knowing the position of other data used as an indicator in the searching process. INDEX MATCH function in excel is very similar to VLOOKUP and HLOOKUP in terms of its usefulness. However, INDEX MATCH function is more flexible and relatively easier to use as long as you have understood and got used to using it.
In other parts of Compute Expert tutorials, it has been described in detail the INDEX MATCH formula separately. It is just that we feel we need to explain the combination of the two formulas particularly because it is something that we often use and it will be very useful for you to understand the usage of the combination in the daily use of a spreadsheet for the process of finding data.
Why We Need to Learn About INDEX MATCH Function in Excel?
In the process that we do in a spreadsheet software, usually, we need to find a particular data so then the data can be processed further. If the data is hard to find, especially if probably there are many rows and columns in the cell range which contains the data, then it will be very hard to search manually. Especially, if we need to find many data to fill a particular column or row in our spreadsheet.
As a case example, probably we need to fill a product price based on the product name with the requirement that the price number must be taken from a large database which consists of various details on all company products. As a database with a lot of details for all company products, then typically it has so many columns and product entries. This can make it difficult for us in finding one of the variables of a particular product like price. Whereas probably we need the info fast to make an invoice document to be sent to a distributor who wants to buy our company products.
In such a case, then it will be very helpful if there is something to help finding the data automatically from the many rows and columns based on certain indicators depending on our needs (for our previous case example, then the indicators are price and the product name which price wanted to be found).
To help the data search process on a cell range, then there is INDEX MATCH formula combination that can be used. INDEX MATCH function combination in excel can find data in cell range vertically or horizontally depending on the indicator inputs that we give in the formula writing. As such, it will be very helpful if we understand how to use this INDEX MATCH formula in excel, particularly when we need to find particular data on a spreadsheet.
A little bit explanation about the inputs in these two formulas is as follows:
=INDEX(array, row_num, column_num)
Note:
- array = the cell range where you want to take your data
- row_num = the row position in your cell range where the data is
- column_num = the row position in your cell range where the data is
=MATCH(lookup_value, lookup_array, match_type)
Note:
- lookup_value = the value to be found in our row/column
- lookup_array = cell range of the row/column where the lookup process will occur
- match_type = optional. The lookup process type that you want to do, whether it is exact, exact or larger, or exact or smaller
How to Use INDEX MATCH Function in Excel?
INDEX MATCH formula is two formulas combined with the usage objective to find data from a group of rows and columns in this spreadsheet software. This formula is a combination of two formulas with distinctive functions of each. The first formula mentioned in this combination can be used to find data in a cell range with the inputs of row and column positions of the data that we look for and the second formula will give row or column position of data. If we have had the indicators for row and/or column positions that can be used to find the data, then we can input them into INDEX MATCH function in excel for then they are used to find the data that we want depending on the indicators.
This INDEX MATCH formula actually has almost the same function with VLOOKUP and HLOOKUP. It is just INDEX MATCH function that can be used directly to find something in rows and columns without caring whether the search process should be done vertically or horizontally and it is more flexible because the inputs in the INDEX MATCH function writing can be juggled depending on our needs for the search process.
How to Use INDEX Formula in Excel?
The following will explain how to use INDEX MATCH formula. In using INDEX MATCH function, you need to determine whether the search indicator is needed for the row, column, or both so that you know later where you will use the MATCH in INDEX formula writing to find the indicator. It is so that the result can be given as one of the main input for the result that will be produced by INDEX formula.
Using INDEX MATCH Formula
-
Type equal sign ( = ) in the cell where you want to put the result
-
Type INDEX (can be with large and small letters) and open bracket sign after =
-
Drag cursor from the most top-left coordinate until the most bottom right for the cell range where the finding process of the data will be held and then type comma sign ( , )
-
Type number of row position or cell coordinate where that number is if you have known exactly in which row the data is and you want to use that row position number directly or if you only know the indicator as the clue of the row position then:
-
Type MATCH (can be with large and small letters) and open bracket sign after =
-
Type indicator or cell coordinate where the indicator is after open bracket sign then type comma sign
-
Drag cell cursor from the most top cell coordinate to the most bottom on the row where you want the indicator search process to happen then type comma sign
-
Type number 0 so your indicator will be found in an exact fit by this formula
-
Type close bracket sign
-
Type MATCH (can be with large and small letters) and open bracket sign after =
-
Type number of column position or cell coordinate where that number is if you have known exactly in which column the data is and you want to use that column position number directly or if you only know the indicator as the clue of the column position then:
-
Type MATCH (can be with large and small letters) and open bracket sign after =
-
Type indicator or cell coordinate where the indicator is after open bracket sign then type comma sign
-
Drag cell cursor from the most left cell coordinate to the most right on the column where you want the indicator search process to happen then type comma sign
-
Type number 0 so your indicator will be found in an exact fit by this formula
-
Type close bracket sign
-
Type MATCH (can be with large and small letters) and open bracket sign after =
-
Type close bracket sign
- Press Enter
-
The process of INDEX MATCH function is done!
Exercise
After you have learned the usage steps of INDEX MATCH function in the tutorial, you can practice your understanding to be more adept by working on the exercise below!
Download the file from the following link and answer the questions. Please download the answer if you have done the answering process and sure about the results that you get!
Link to download the INDEX MATCH formula exercise file:
Download here
Questions
Use INDEX MATCH formula to answer these three questions!- What is the number in column C?
- What is the number in row E?
- What is the number in row A and column C?
Link to download INDEX MATCH formula exercise answer:
Download here
Additional Notes
If you want to learn in more detail on each of the INDEX MATCH function part or probably want to learn about VLOOKUP and HLOOKUP as the alternatives of data search formula (although we recommend you to use INDEX MATCH formula because it is easier to use and more flexible in its usage), then you can see other tutorial parts which explain them in this Compute Expert blog!
