EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Tips INDEX MATCH Function in Excel
Secondary Sidebar
Excel Functions
  • Excel Tips
    • Recover Document in Excel
    • Excel Shortcut to Select Row
    • Excel Header Row
    • Excel Insert Button
    • Excel Translate
    • Budget in Excel
    • KPI Dashboard in Excel
    • Blank Invoice Excel Template
    • Write Formula in Excel
    • Auditing Tools in Excel
    • Autofit Row Height in Excel
    • Formatting Text in Excel
    • Add Rows in Excel Shortcut
    • Divide Cell in Excel
    • Compare Two Columns in Excel for Matches
    • Excel Troubleshooting
    • Unprotect Excel Workbook
    • Subtract Date in Excel
    • Carriage Return in Excel
    • Negative Numbers in Excel
    • Worksheets in Excel
    • Excel Match Multiple Criteria
    • Exponential Moving Average Formula
    • Sentence Case in Excel
    • Excel Reverse Order
    • Excel Timesheet Template
    • Weighted Average in Excel
    • CTRL Shift-Enter in Excel
    • Excel Expense Tracker
    • INDEX MATCH Function in Excel
    • Percent Change in Excel
    • Drag and Drop 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 (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL COURSE
  • Online Excel Data Analysis Course
  • Excel for Finance Course

INDEX MATCH Function in Excel

By Madhuri ThakurMadhuri Thakur

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 the worst-case HLOOKUP formula, to look up the values inside a given range of cells. However, it is a well-known fact that VLOOKUP has its own limitations. For example, 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 with the help of some examples.

Start Your Free Excel Course

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

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,452 ratings)

The 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 matches the lookup value in a given array and provides its position as an argument to the 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 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).

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 the keyword INDEX.

Index Match Function in Excel 1-2

Step 2: Use $C$1:$C$5 (Location column) as an argument to the INDEX formula (this is an array from where we want to pull the lookup value for a 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 the MATCH formula as a second argument inside the INDEX formula and use a value in H1 as a lookup value under the 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 the 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 a 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 to match the country name and the other matching 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 the country India. This is how we can use the 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, which makes it a successor of old-school VLOOKUP, which can only work on columns (vertical lines). This combination is so relevant that some analysts even prefer to look 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, and a combination of both (see example 3 for reference).

Recommended Articles

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

  1. Excel Match Function
  2. Excel INDEX Function
  3. Excel Match Multiple Criteria
  4. Compare Two Columns in Excel for Matches
Popular Course in this category
Excel Training (23 Courses, 9+ Projects)
  23 Online Courses |  9 Hands-on Projects |  110+ Hours |  Verifiable Certificate of Completion
4.9
Price

View Course

Related Courses

Excel Data Analysis Training (17 Courses, 8+ Projects)4.9
Excel for Finance Training (18 Courses, 7+ Projects)4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
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

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Free Excel Course

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

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download INDEX MATCH Function Excel Template

EDUCBA

Download INDEX MATCH Function Excel Template

EDUCBA

डाउनलोड INDEX MATCH Function Excel Template

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more