Index and Match in Excel – Shiksha Online

VLOOKUP is a very popular function used in data analysis, but it has certain limitations that prevent you from getting the required result in given situations. The information must be structured in a certain way to use it. The data to be retrieved must be to the right of the value to search for. It does not search to the left. Also, we can’t insert or delete columns because the function breaks or brings the wrong data.. But… What happens when your reference value for the search is found in another column than the first one? In those cases, VLOOKUP does not work. INDEX and MATCH in Excel are the functions that can solve your problem when you can’t use the VLOOKUP function to perform the search you need. The article talks about INDEX and MATCH functions in Excel, how to use them, and how to combine INDEX and MATCH to get the desirable results.

Content

• INDEX function
  • INDEX Function Example
• MATCH Function
  • MATCH Function Example
• Combine the INDEX and MATCH functions

INDEX function

The Excel INDEX function allows you to find values ​​in a data matrix by determining the row and column; that is, it returns the value that is in the position that we request. 

The INDEX function syntax is 

=INDEX(array, row_num, [column_num])

Where:

• array: Range of data where the value you are looking for is located. (Required)
• row_num: Row number within the range where the searched value is found. (Required)
• column_num: Column number within the range where the searched value is found. (Optional)

The column number appears in square brackets because it is an optional argument in the INDEX function since in single-column ranges it is not necessary to indicate this argument.

INDEX Function Example

For example, if we have the income and expenses of a company, we can easily and quickly find what the expenses were for the month of May.

Since it is the month of May, we want Excel to look at that row. Therefore, it is row number “5”. We also look for the expenses, which correspond to column three, thus, the column is number “3”. 

So, the elements of the function are:

array : B3: D11

row_num : 5

column_num : 3

That is, the INDEX function is:

=INDEX(B3:D11,5,3)

The result we will get will be:

We can verify that the expenses for May correspond to the result delivered by Excel. 

Note – we have just named the columns and rows in orange for our reference.

Read MS Excel Tutorials

MATCH Function

The MATCH function of Excel allows us to find the numerical position of an element in a list of values. It returns a numeric value that expresses the position of the value sought in the established array.

MATCH syntax 

=MATCH(lookup_value, lookup_array, [match_type])

Where,

lookup_value: The value whose position we want to know.

lookup_array: Range of cells where the lookup value is found.

match_type: Specifies how Excel matches lookup_value with values in lookup_array. 

• For 0, it finds the first value that is exactly the same as lookup_value.
• If we specify 1 or omit it, it finds the largest value that is less than or equal to lookup_value.
• If we specify -1, it finds the smallest value that is greater than or equal to lookup_value.

MATCH Function Example

Here, we want to see what was the month in which there were profits of $20,100. For this, we must use the matching time “exact match”.

Since we are looking for the profits, we must tell Excel that only that column is used, then the searched matrix corresponds to the income column. Also, since this is an exact match, the type_of:_match is “0”.

The elements of the function are:

lookup_value : $20,100

lookup_array : C3:C11 

match_type : 0

That is, the MATCH function is:

=MATCH(20100, C3:C11, 0)

The result we will get will be:

We can verify that position number 3 of the income column is the row for the month of March. This suggests that the month that there was $20,100 in income was March. 

Combine the INDEX and MATCH in Excel

By using both functions together you can very efficiently manage your Excel sheets. This tool allows you to find values ​​faster and more dynamically in a database.

In this case, the MATCH function will determine the row in which the INDEX function should search, that is, it will be found in the row_num argument.

For example, we want to find out what were the expenditures in September.

For this, the MATCH function looks for the value called “September” in the list of months. We have to find the exact match, so in the type_of_coincidence argument we must write a “0”. Also, since we are looking for expenditures, we must indicate that it is column number 3.

Remember that since we are looking for a text value, it must be enclosed in quotes. 

The elements of the function are:

matrix: B3:D11

row_num: 9

column_num: MATCH functio

lookup_value : “Expenditure”

lookup_array : B2:D2

match_type : 0

That is, the INDEX function is

=INDEX(B3:D11,9,MATCH(“Expenditure”,B2:D2,0))

The result we will get will be:

We can see that the result delivered by Excel coincides with the expenses generated in September.

I hope the above examples helped you to understand how to use INDEX and MATCH in Excel.

Top Trending Articles in MS Excel:

Most Useful Excel Formulas | Min Max Functions in Excel | Average Functions in Excel | Introduction to MS Excel | Financial Modelling in Excel | MS Excel interview questions | Sum Function in Excel | Trim Function in Excel | Pivot Table in Excel | Percentage in Excel | Vlookup in Excel | Median Function in Excel | Types of Charts in Excel | Count Function in Excel | MS Excel Vs. Google Sheet | Remove Duplicates in Excel | Create Graph in Excel