WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Index Match Function in Excel

Index Match Function in Excel

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

What Does INDEX Function Do in Excel?

The INDEX Function can return the result from the row number, and the MATCH function can give us the position of the lookup value in the array. This combination of the INDEX MATCH Excel Function is very useful in addressing a key limitation of VLOOKUP, which is that that cannot be used to search the table from left to right; however, INDEX MATCH Function can achieve this goal effortlessly. In this article, we will discuss this in detail –

You can download this Index Match Function Excel Template here – Index Match Function Excel Template

This is as simple as that, for now, look at the syntax of the INDEX function.

Index Formula

Array: From which column or array, we need the value??

Row Number: In the provided array, from which row we need the result???

These two arguments are good enough in most situations. So, now we will look into the example of the INDEX function.

Example

For this example, consider the below data.

Index Formula Example 1

We have data from A1 to B7 cell range. In D2 cell, we have the month name, and for this month’s name, we need sales value in cell E2.

Let’s open the INDEX function in cell E2.

Index Formula Example 1-1

An array is the first argument, i.e., from which column we need the result, i.e., we need results from the “sales” column, so select from B2 to B7.

 Example 1-2

Next is the ROW number, i.e., in the selected range of cells from which row we need the result. In this example, we need the sales value for the month “Mar.” In the selected range, “Mar” is the third row, so we need results from the third row.

Index Formula Example 1-3

Ok, that’s all. Close the bracket and hit the enter key, we will have sales value for the month of “Mar.”

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Example 1-4

Like this, based on the row number provided, we will get the value from the supplied array.

What Does MATCH Function Do in Excel?

The MATCH function is used to find the position of the lookup value in the supplied array. In simple terms, lookup value row number or column number in the range of cells. Below is the syntax of the MATCH function.

Match Formula

Lookup Value: For which lookup value, we are trying to find the position??

Lookup Array: In which array or range of cells, we are looking for the lookup value???

Match Type: This will decide what kind of result we need. We can provide zero for an exact match.

Example of MATCH Function in Excel

For this example, consider the above data only.

Example 1

From the above data, we are trying to get the position of the month “Mar” in cell E5. Open MATCH function in the E5 cell.

Match function Example 1-1

The first argument is “Lookup Value,” so here our lookup value is “Mar,” so select D5 cell.

Example 1-2

Lookup Array is from which range of cells we are trying to look for the position of the lookup value. So select the “Month” column.

Example 1-3

The last argument is Match Type since we are looking at the exact match supply 0.

Match function Example 1-4

So, in the lookup array A2:A7, the position of the lookup value “Mar” is 3.

Combination of INDEX + MATCH Function in Excel

The index can return the result from the mentioned row number, and the Match function can give us the position of the lookup value in the array. Instead of supplying the row number to the INDEX formula, we can enclose the MATCH function to return the row number.

Example 1-3

Open INDEX function in cell E2.

Index Match Function Example 1-1

For the first argument, Array supply B2 to B7.

Index Match Function Example 1-2

For row number, instead of supplying the row number as 3, open the MATCH function inside the INDEX function.

Index Match Function Example 1-3

Select the lookup value as a D2 cell.

Index Match Function Example 1-4

Select the lookup array as A2 to A7.

Index Match Function Example 1-5

Enter zero as the match type.

Index Match Function Example 1-6

So, based on the row number provided by the MATCH function INDEX function will return the sales value. We can change the Month name in cell D2 to see the sales value changes dynamically.

Powerful Alternative to VLOOKUP

We all have used the VLOOKUP function day in day out, but one of the limitations of VLOOKUP is, it can only fetch the value from left to right, not from right to left.

For example, look at the below data.

Alternate Index Match Function 1

The above data lookup value is Month, and the result column is sales. But in the data result column (Sales) is to the left of the lookup array table (Month), so VLOOKUP cannot help use here, but with the combination of INDEX and MATCH, we can still fetch the data from the table.

Recommended Articles

This has been a guide to Index Match Function in Excel. Here we learn how to use the index & match function as a powerful alternative to VLOOKUP along with examples and a downloadable excel template. You may learn more about excel from the following articles –

  • Index Match With Multiple Criteria
  • Venn Diagram Examples
  • Create Box and Whisker Plot Excel
  • VBA Index Match
6 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Index Match Function Excel Template

Special Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More