Index and Match in Excel – Shiksha Online

Index and Match in Excel – Shiksha Online

5 mins read678 Views Comment
Rashmi
Rashmi Karan
Manager - Content
Updated on Feb 9, 2024 16:47 IST

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.

2022_06_INDEX-MATCH-Functions-in-Excel-1.jpg

Content

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.

Embark on your journey in MS Excel with our guide to the best collegesinnovative programsonline courses, and career opportunities!

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.
How to Create MIS Report in Excel?
How to Create MIS Report in Excel?
A Management Information System (MIS) report in Excel is a vital tool for businesses and organizations, offering valuable insights by consolidating and presenting data in an organized and comprehensible format....read more
How to Merge Cells in Excel – Shiksha Online
How to Merge Cells in Excel – Shiksha Online
Learn how to merge cells in Excel through real datasets.
Most Popular and Powerful Formulas in Excel
Most Popular and Powerful Formulas in Excel
Excel has various functions ready to process your information to get the desired results. Familiarize yourself with each of them. Once you master various functions, you can create advanced MS...read more

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. 

How to Import Text Files to Excel
How to Import Text Files to Excel
Managing massive volumes of data is tricky, especially when those are on your text files. Moving data to Excel for better calculations and presentations is an efficient way to handle...read more
Tutorial – XLOOKUP in Excel – Shiksha Online
Tutorial – XLOOKUP in Excel – Shiksha Online
The article talks about an advanced and flexible lookup function, XLOOKUP.
CONCATENATE in Excel – Shiksha Online
CONCATENATE in Excel – Shiksha Online
The article discusses the use of CONCATENATE in Excel to combine strings.

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

FAQs

What is the INDEX function in Excel?

The INDEX function returns a value or the reference to a value from a table or range.

What is INDEX & Match?

INDEX and MATCH is a popular tool in Excel that allows more advanced lookups. INDEX and MATCH are flexible and allow horizontal and vertical lookups, 2-way lookups, left lookups, case-sensitive lookups, and multiple criteria lookups.

How does Matchx work?

The MATCHX function searches an array or range of cells for a specified element and then returns the relative position of the element. We can use MATCHX to find the position of an element in a list.

About the Author
author-image
Rashmi Karan
Manager - Content

Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio