Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
INDEX Function
3.
MATCH function
4.
INDEX and MATCH function
5.
Two-way lookup
6.
Left lookup
7.
Case-sensitive lookup
8.
Closest match
9.
Multiple criteria lookup
10.
FAQs
11.
Key Takeaways
Last Updated: Mar 27, 2024

Index and Match Function

Author Abhay Trivedi
1 upvote
Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

The INDEX function returns the value in an array or range; it retrieves individual values or entire rows and columns. The MATCH function locates the position of an item in a range.

INDEX and MATCH functions are the most popular tool in MS Excel for performing more advanced lookups. Because INDEX and MATCH functions are incredibly flexible, you can do horizontal and vertical lookups, 2-way, left, case-sensitive, and even lookups based on multiple criteria. If we want to improve our Excel skills, INDEX and MATCH functions should be on your list.

INDEX Function

The INDEX function in Excel is highly flexible and powerful, and you'll find it in a massive number of Excel formulas, especially advanced formulas. But what does INDEX do? In a nutshell, INDEX retrieves the value at a given location in a range.

For example: Use the formula

=INDEX(B2:E9, 4, 4)

Like this in the example to get the salary of the desired person.

Output:

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

MATCH function

The MATCH function is developed for one purpose, to find the position of an item in a range. For example, if we can use MATCH to get the position of the word "Albert" in this list like this:

INDEX and MATCH function

Let's say we write a formula that returns the salary for a given ID. From above, we know we can provide INDEX with a row and column number to retrieve a value. But we don't want to hardcode numbers. Instead, we want a dynamic lookup. The MATCH will work perfectly for finding the positions we need.

For example:

Output:

Two-way lookup

In a two-way lookup, use the MATCH function twice to get a row position and once to get a column position.

For Example:

Left lookup

One of the critical advantages of INDEX and MATCH over the VLOOKUP function is the ability to perform a "left lookup". It means a lookup where the ID column is to the right of the values you want to retrieve.

Case-sensitive lookup

The MATCH function is not case-sensitive by itself. However, we use the EXACT function with INDEX and MATCH to perform a lookup that respects upper and lower case.

Output:

Closest match

Here, an example that shows the flexibility of the INDEX and MATCH functions is the problem of finding the closest match. We use the MIN and ABS functions to create a lookup value and a lookup array inside the MATCH function. Essentially, we use MATCH to find the slightest difference. Then we use INDEX to recover the associated trip from column B.

Output:

Multiple criteria lookup

One of the most challenging problems in Excel is a lookup based on multiple criteria. In other words, a lookup that matches on more than one column simultaneously. 

Output:

FAQs

1. What are the uses of MS Excel?

Microsoft Excel is software made by Microsoft that lets users organize, format, and calculate data using a spreadsheet system. Excel is one part of the Microsoft Office suite.

2. What are the advantages of MS Excel?

MS Excel allows us to create a visual representation of data and information. We can visually display the data in bar charts, column charts, and graphs. It automatically revises the charts and graphs once the data gets modified.

3. Where is MS Excel used?

Excel is generally used to organize data and perform financial analysis. It is used across all business functions and at firms from small to large.

4. What are INDEX and MATCH functions in Excel?

The INDEX returns the value of a cell in a table based on the column and row number. The MATCH returns the position of a cell in a row or column. Combined, the two functions can look up and return the value of a cell in a table based on vertical and horizontal criteria.

5. Why is INDEX and MATCH better than VLOOKUP?

VLOOKUP is better because it is more accessible to understand for beginner to intermediate Excel users. The INDEX MATCH functions are better because they will continue to work if you insert or delete columns in the lookup table and allow the lookup column to be anywhere.

Key Takeaways

This article gives information about the index and match functions. We also discussed the applications of using index and match functions. Based on the column and row number, the INDEX function returns the value of a cell in a table. The MATCH function returns the row or column position of a cell. Both functions may look up and return the value of a cell in a table depending on vertical and horizontal criteria when used together.

Click here to read about Introduction to ExcelVLOOKUP FunctionWorksheets in ExcelMust have Excel Skills.

Refer to our guided paths on Coding Ninjas Studio to learn more about DSA, Competitive Programming, JavaScript, System Design, etc. Enroll in our courses and refer to the mock test and problems available. Take a look at the interview experiences and interview bundle for placement preparations.

Do upvote our blog to help other ninjas grow.

Happy Learning!

Next article
HLOOKUP Function
Live masterclass