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 Excel, VLOOKUP Function, Worksheets in Excel & Must 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!
240+ registered 
