• Skip to primary navigation
  • Skip to content
  • Skip to primary sidebar
  • Skip to footer
EDUCBA

EDUCBA

MENUMENU
  • Resources
        • Excel Charts

          • Histogram Chart Excel
          • Basic Excel Formulas
          • Text to Columns in Excel
        • Excel Charts
        • Excel Tips

          • Excel Gantt Chart
          • IFERROR with VLOOKUP
          • Data Table in Excel
        • Excel Tips
        • Excel Tools in Excel

          • Stacked Column Chart
          • Cheat Sheet of Excel Formulas
          • Excel Data Validation
        • Histogram chart in excel
        • Others

          • Resources (A-Z)
          • Excel Functions
          • Financial Functions in Excel
          • Logical Functions in Excel
          • Lookup Reference Functions in Excel
          • Maths Function in Excel
          • TEXT and String Functions in Excel
          • View All
  • Free Courses
  • All Courses
        • Certification Courses

          Excel Course 1
        • All in One Bundle

          All-in-One-Excel-VBA-Bundle
        • Excel course

          Excel-Training
        • Others

          • Excel advanced course
          • VBA Course
          • Excel Data Analysis Course
          • Excel for Marketing Course
          • Excel for Finance Course
          • View All
  • 120+ Courses All in One Bundle
  • Login

INDEX MATCH Function in Excel

Home » Excel » Blog » Excel Tips » INDEX MATCH Function in Excel

INDEX MATCH Function in Excel

Excel INDEX MATCH Function (Table of Contents)

  • Introduction to INDEX MATCH Function
  • How to Use the INDEX MATCH Function in Excel?

Introduction to INDEX MATCH Function

Being an Excel user, we often rely on VLOOKUP, in worst case HLOOKUP formula for looking up the values inside a given range of cells. It is a well-known fact that VLOOKUP has its own limitations. We can’t lookup the values from right to left if you are using VLOOKUP as a function and this is where I believe users across the globe might have started to find out an alternative for this function. As far as an alternative is concerned, there is an alternative for VLOOKUP which is more versatile and called INDEX MATCH popularly. In this article, we will see how INDEX MATCH works through the help of some examples.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Syntax for INDEX MATCH

As is said earlier, INDEX and MATCH work as a combination to lookup the value in a given range. It has syntax as below:

INDEX Function Syntax

MATCH Syntax

Arguments:

INDEX() – Formula, that allows you to capture the value from a given cell through the table associated with column or row number.

MATCH() – Formula, that matches the lookup value in a given array and provides the position of it as an argument to INDEX function.

How to Use the INDEX MATCH Function in Excel?

We will see how INDEX MATCH can be used as an alternative to VLOOKUP through this example.

You can download this INDEX MATCH Function Excel Template here – INDEX MATCH Function Excel Template

Example #1 – INDEX MATCH as an Alternative to VLOOKUP

Suppose we have a data as shown in the screenshot below:

Index Match Function in Excel 1-1

We are about to capture the Location column with the Name column as a reference (for name Lalit).

Popular Course in this category
Cyber Week Sale
Excel Training (18 Courses, 9+ Projects) 18 Online Courses | 9 Hands-on Projects | 95+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.9 (2,645 ratings)
Course Price

View Course

Related Courses
Excel Data Analysis Training (12 Courses, 6+ Projects)Excel for Finance Training (15 Courses, 5+ Projects)

Step 1: In cell H2, start typing =INDEX and double click to select the INDEX formula out of the list of all possible functions starting with keyword INDEX.

Index Match Function in Excel 1-2

Step 2: Use $C$1:$C$5 (Location column) as an argument to INDEX formula (this is an array from where we want to pull the lookup value for match). The dollar sign emphasizes that the range C1:C5 is made constant for the processing of this formula.

Index Match Function in Excel 1-3

Step 3: Use MATCH formula as a second argument inside INDEX formula and use a value in H1 as a lookup value under MATCH formula.

Index Match Function in Excel 1-4

Step 4: Select the lookup array from A1:A5 as this is the column where we would like to check whether the lookup value can be found. Also, use zero as an exact match argument inside the MATCH function as zero looks up for the exact match of the lookup value.

Index Match Function in Excel 1-5

Step 5: Close the parentheses to complete the formula and press Enter key to see the output. We could see Location as Pune which is looked up based on lookup value Lalit.

Index Match Function in Excel 1-6

Example #2 – INDEX MATCH for LOOKUP from Right to Left

Now, suppose a scenario, where we have a salary as a lookup value and we need to figure out with whom that salary is associated with.

INDEX Example 2-1

Step 1: Start the formula with =INDEX and use A1:A5 as an array argument to it under cell H2 of the current worksheet.

INDEX Example 2-2

Step 2: Use MATCH formula under INDEX as a second argument. Inside MATCH, use H1 as a lookup value argument.

INDEX Example 2-3

Step 3: D1:D5 would be your lookup array. The formula will search the lookup value in this array and give the position of the same as an argument to the INDEX formula. Don’t forget to use zero as a matching argument.

Exact Match -INDEX Function

Step 4: Close the parentheses to complete the formula and press Enter key to see the output. You can see the name as Martha in H2 and could verify that she is the one who has salary of $2,300.

Exact Match - INDEX Function 1

Example #3 – INDEX MATCH to LOOKUP Values from Rows and Columns

Suppose we have data as shown below and we need to lookup sales value for India in 2018. We need to do two types of matching, one for the Country and another for the Year. See how it goes with INDEX MATCH.

LOOKUP Values from Rows and Columns 1

Step 1: Input =INDEX formula and select all the data as a reference array for the index function (A1:D8).

LOOKUP Values from Rows and Columns 2

We need to use two MATCH functions, one which can match the country name and other which can match the year value.

Step 2: Use MATCH as an argument under INDEX and set F2 as a lookup value under it. This is the MATCH for COUNTRY.

LOOKUP Values from Rows and Columns 3

Step 3: Use A1:A8 as a lookup array as this is where we could find the specified country name. Don’t forget to use zero as a matching criteria which specifies an exact match.

LOOKUP Values from Rows and Columns 4

Step 4: Now, again use the MATCH function which allows the system to check the year 2018 and assign the position of Sales value associated with India to the INDEX formula. Set the lookup value as G2 inside the MATCH formula.

LOOKUP Values from Rows and Columns 5

Step 5: Here, we could see only cell A1:D1 are the ones where we could find the lookup value 2018. Thus, use the same as a lookup array to the MATCH formula.

LOOKUP Values from Rows and Columns 6

Step 6: Use zero as matching criteria that specifies the exact match for the year value inside the lookup range. Close the parentheses and press Enter key to see the output.

LOOKUP Values from Rows and Columns 7

We can see that the function has captured the correct value for the 2018 sales value associated with country India. This is how we can use INDEX MATCH function under different scenarios. Let’s wrap the things with some points to be remembered.

Conclusion

INDEX MATCH is not a function itself in Excel, rather it is a combination of two different formulae and is very powerful than VLOOKUP (we will check this in short). This function can be used on rows, columns, or on a combination of both as well which makes it a successor of old school VLOOKUP which only can work on columns (vertical lines). This combination is so relevant that some of the analysts even prefer it to be used for looking up the values and never move their head towards VLOOKUP.

Things to Remember

  • INDEX MATCH is not a function in Excel, but a combination of two formulas, INDEX and MATCH
  • INDEX MATCH can be used as an alternative to old school VLOOKUP. VLOOKUP only can see through the vertical cells. Whereas, INDEX MATCH can lookup values based on rows, columns as well as a combination of both (see example 3 for reference).

Recommended Articles

This is a guide to Index Match function in Excel. Here we discuss how to use Index Match function in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. Creating Data Model in Excel
  2. How to Use Checklist in Excel?
  3. Examples of Excel Match Function
  4. VLOOKUP Examples in Excel
  5. Examples of Excel Macros

All in One Excel VBA Bundle (120+ Courses, 30+ Projects)

120+ Online Courses

30+ Projects

500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Reader Interactions
Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar
Excel Functions Tutorials
  • Excel Tips
    • Excel Timesheet Template
    • Weighted Average in Excel
    • CTRL Shift-Enter in Excel
    • Excel Expense Tracker
    • INDEX MATCH Function in Excel
    • Shade Alternate Rows in Excel
    • Excel Database Template
    • Inverse Matrix in Excel
    • New Line in Excel Cell
    • Format Cells in Excel
    • Excel Text with Formula
    • Project Management Template in Excel
    • Vlookup vs Index Match
    • Embedded in Excel
    • Count Colored Cells in Excel
    • Excel Hacks
    • Column Header in Excel
    • Database Function in Excel
    • Excel Spell Check
    • Linear Interpolation in Excel
    • Convert Excel to CSV
    • Excel Not Responding
    • Delta Symbol in Excel
    • Excel Mortgage Calculator
    • Excel Show Formula
    • Break Links in Excel
    • VLOOKUP Examples in Excel
    • Alternate Row Color Excel
    • Checklist in Excel
    • Microsoft Office Tips
    • Excel vs Access
    • Excel Change Case
    • Count Characters in Excel
    • Formula Bar in Excel
    • Invoice Template in Excel
    • Find and Replace in Excel
    • PowerPivot in Excel
    • Wildcard in Excel
    • Line Break in Excel
    • NULL in Excel
    • Text Formula in Excel
    • Family Tree in Excel
    • Insert Calendar in Excel
    • Page Setup in Excel
    • Search For Text in Excel
    • Switching Columns in Excel
    • 3D Cell Reference in Excel
    • Roadmap Template in Excel
    • Cell References in Excel
    • Excel Automation
    • Matching Columns in Excel
    • Evaluate Formula in Excel
    • How to Find Mean in Excel
    • How to Add Cells in Excel
    • Project Timeline in Excel
    • TODAY Formula in Excel
    • Poisson Distribution in Excel
    • YEAR Formula in Excel
    • REPLACE Formula in Excel
    • Create Spreadsheet in Excel
    • SEARCH Formula in Excel
    • HYPERLINK Formula in Excel
    • Pivot Table with Multiple Sheets
    • COLUMNS Formula in Excel
    • DAY Formula in Excel
    • RIGHT Formula in Excel
    • INDIRECT Formula in Excel
    • Excel IRR Formula
    • Pivot Table Sort
    • OR Formula in Excel
    • Pivot Table Formula in Excel
    • How to Edit Drop Down List in Excel?
    • WEEKDAY Formula in Excel
    • Chart Wizard in Excel
    • Time Difference in Excel
    • Lookup Table in Excel
    • SUBTOTAL Formula in Excel
    • LOOKUP Formula in Excel
    • Themes in Excel
    • Group Worksheets in Excel
    • Excel ISNUMBER Formula
    • 3D Maps in Excel
    • VLOOKUP Tutorial in Excel
    • Moving Columns in Excel
    • Grouping Columns in Excel
    • Excel Formula of Percentage
    • Excel Leading Zeros
    • Timesheet in Excel
    • Nested IF Formula in Excel
    • SUMIF Formula in Excel
    • Concatenation in Excel
    • Excel Insert Page Break
    • LEN Formula in Excel
    • Insert Comment in Excel
    • Excel Print
    • Fractions in Excel
    • Excel Add a Column
    • Borders in Excel
    • Excel Sum by Color
    • Excel vs Google Sheets
    • Calculate Percentage Increase in Excel
    • Excel Format Phone Numbers
    • Excel Search Box
    • Excel vs CSV
    • Excel vs Numbers
    • Excel Concatenate Date
    • Excel Bullet Points
    • Calculate Compound Interest in Excel
    • Find External Links in Excel
    • Excel Comma Style
    • Excel Remove Leading Spaces
    • Combine First and Last Name in Excel
    • How to Calculate Ratio in Excel
    • SUMPRODUCT Function with Multiple Criteria
    • Create Excel Template
    • Excel Greater than or Equal
    • Excel Keyboard Shortcuts
    • Excel Row vs Excel Column
    • Excel Operators
    • Excel Text Compare
    • Excel Uppercase Function
    • Excel Auto Numbering
    • Watermark in Excel
    • Excel Delete Row Shortcut
    • Excel Word Count
    • Row Count in Excel
    • Excel Grade Formula
    • Excel Percentage Difference
    • Excel Rows and Columns
    • Excel Group
    • Excel Move Columns
    • Excel Row Height
    • Excel Sparklines
    • Excel Drawing a line
    • Multiple IFS in Excel
    • Excel Strikethrough
    • Numbering in Excel
    • Share Excel Workbook
    • Excel Shortcut For Merge Cells
    • Excel OneDrive
    • Excel Track changes
    • Excel Hide Formula
    • Excel Filter Shortcuts
    • Excel 3D Reference
    • Protect Excel Workbook
    • Excel Moving Averages
    • Excel Row Limit
    • Excel Absolute Value
    • Excel Print Area
    • Excel Format Painter
    • Excel Separate text
    • Excel Autofit
    • Excel Columns to Rows
    • Excel Paste Shortcut Option
    • Excel Rows to Columns
    • Excel Insert Row Shortcut
    • Excel Extensions
    • Excel Unmerge Cells
    • Divide in Excel Formula
    • Excel Compare Two Columns
    • Not Equal To Excel
    • Excel Remove Duplicates
    • Excel Remove Spaces
    • Excel Calculate Age
    • Copy Excel Sheet
    • Excel Conditional Formatting Based on Another Cell Value
    • Excel Cell Reference
    • Excel Split Cell
    • Excel Circular Reference
    • Highlight Every Other Row in Excel
    • Errors in Excel
    • Excel Delete Pivot Table
    • Excel Pivot Table
    • Subtraction in Excel
    • Count Unique Values in Excel
    • Excel Highlight Duplicates
    • Excel Data Formatting
    • Random Numbers in Excel
    • Basic Excel Formulas
    • Page Numbers in Excel
    • Excel CAGR Formula
    • Excel Combine cells
    • IFERROR with VLOOKUP in Excel
    • Column Sort in Excel
    • Print Comments in Excel
    • Cheat Sheet of Excel Formulas
    • Remove Hyperlinks in Excel
    • Excel Drop Down List
    • Remove (Delete) Blank Rows in Excel
    • Relative Reference in Excel
    • Subscript in Excel
    • Superscript in Excel
    • Excel Insert Multiple Rows
    • Excel Absolute Reference
    • Unhide Columns in Excel
  • Excel Functions (10+)
  • Excel Tools (97+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (12+)
  • Lookup Reference Functions in Excel (30+)
  • Maths Function in Excel (39+)
  • TEXT and String Functions in Excel (25+)
  • Date and Time Function in Excel (20+)
  • Statistical Functions in Excel (55+)
  • Information Functions in Excel (4+)
  • Excel Charts (44+)
  • VBA (180+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (14+)
  • Excel Formula and Functions (20+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL COURSE
  • Online Excel Data Analysis Course
  • Excel for Finance Course
Footer
About Us
  • Who is EDUCBA?
  • Sign Up
  •  
Free Courses
  • Free Online Excel Course
  • Free Vba Course
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training
Resources
  • Resources (A To Z)
  • Excel Charts
  • Excel Tips
  • Excel Tools in Excel
  • Excel Functions
  • Financial Functions in Excel
  • Logical Functions in Excel
  • Lookup Reference Functions in Excel
  • Maths Function in Excel
  • TEXT and String Functions in Excel
  • Date and Time Function in Excel
  • Statistical Functions in Excel
  • Information Functions in Excel
Apps
  • iPhone & iPad
  • Android
Support
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions

© 2019 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

Download INDEX MATCH Function Excel Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

Let’s Get Started
Please provide your Email ID
Email ID is incorrect

Cyber Week Offer - All in One Excel VBA Bundle (120+ Courses, 500+ hours of Videos) View More

Cyber Week Offer - Cyber Week Offer - All in One Excel VBA Bundle (120+ Courses, 500+ hours of Videos) View More