How to Use INDEX and MATCH in Excel

The INDEX and MATCH functions work together to perform flexible lookups in Excel. Unlike traditional lookup formulas, this combination allows you to retrieve information from any column without being restricted by the layout of your data. In this lesson, you’ll see how INDEX and MATCH are used to pull equipment details like system type and manufacturer from a master equipment database based on an equipment ID.

Download the Excel file used in this tutorial:

Step By Step Instructions

Using the INDEX and MATCH Functions

1. Identify the lookup value you want to search

Start with the value that will drive the lookup.
In this example, the lookup value is the Equipment ID from the service or work order table.
This value will be used to locate the correct row in the equipment database.

2. Use MATCH to locate the row position

Apply the MATCH function to find where the Equipment ID appears in the equipment database.
MATCH searches a column and returns the row number position where the value is found.
Use the exact match option so Excel only returns results when the Equipment ID matches exactly.

After running MATCH, Excel returns the row location of that equipment record.

3. Confirm the row result returned by MATCH

Verify that the row number returned by MATCH corresponds to the correct record in the database.
When you check that row in the equipment table, you should see the same Equipment ID that was searched.
This confirms the MATCH portion of the lookup is working correctly.

4. Use INDEX to return the desired value

Next, apply the INDEX function to retrieve a value from another column in the same row.
INDEX takes two key inputs:
- The column containing the information you want to return
- The row number where the value exists

If you supply the correct row number, INDEX returns the corresponding value from that column.

5. Test INDEX using the row number from MATCH

Use the row number returned by MATCH to test the INDEX function.
When the row number is correct, INDEX should return the expected value from the selected column.
For example, this might return the System Type associated with that equipment record.

6. Combine INDEX and MATCH into a single formula

Instead of manually typing the row number, embed the MATCH function inside INDEX.
MATCH finds the correct row dynamically.
INDEX then returns the value from the specified column based on that row.

This creates a flexible lookup that automatically retrieves the correct information based on Equipment ID.

7. Copy the formula to retrieve values for all records

Once the combined INDEX–MATCH formula works for one row, drag it down to populate the entire column.
Excel will automatically return the correct value for each Equipment ID.

8. Retrieve additional fields using the same approach

You can repeat the same INDEX–MATCH logic to bring in other columns from the database.
For example:
- System type
- Manufacturer
- Equipment model
Only the INDEX column reference changes while the MATCH portion stays the same.

9. Ensure references are properly locked

When referencing ranges instead of entire columns, apply absolute references so the lookup ranges remain fixed when the formula is copied down.
This prevents the lookup ranges from shifting.

10. Extend the lookup to multiple attributes

Once set up, the same INDEX–MATCH structure can retrieve any related information from the equipment database.
Each new column simply references a different field while keeping the MATCH lookup consistent.

This allows you to dynamically pull equipment details from a centralized database using the Equipment ID as the key.

FAQ

The INDEX and MATCH Functions in Excel

Q1. What do the INDEX and MATCH functions do in Excel?
The INDEX function returns a value from a specific position in a dataset, while the MATCH function finds the position of a value within a range. When combined, they allow you to perform powerful and flexible lookups in Excel.

Q2. Why use INDEX and MATCH instead of VLOOKUP?
Unlike VLOOKUP, the INDEX and MATCH combination can look up values in any direction. It does not require the lookup column to be the first column in the dataset, which makes it more flexible for real-world data structures.

Q3. What is the benefit of separating the lookup into two functions?
MATCH identifies the position of the item you’re searching for, and INDEX retrieves the corresponding value. This separation makes the formula more robust and less likely to break if the structure of your spreadsheet changes.

Q4. Can INDEX and MATCH return values from multiple columns?
Yes. Once the MATCH function finds the correct row, the INDEX function can return values from any column in the dataset, such as system type, manufacturer, price, or any other field.

Q5. When should I use INDEX and MATCH in business analysis?
This combination is commonly used when working with large datasets, equipment databases, customer records, or inventory tables, where you need to quickly retrieve related information based on an ID or key field.

Q6. Is there a newer alternative to INDEX and MATCH?
Yes. Excel introduced XLOOKUP, which simplifies many lookup tasks into a single function. However, INDEX and MATCH remain widely used and are important to understand because they work in almost every version of Excel.

Get more lessons like this
by joining our newsletter

Receive weekly Excel tutorials, tips, and KPIs straight to your inbox

Databoards

Analysis & Development