• Skip to main content
  • Skip to primary sidebar

Techbout

Technology Simplified.

  • Windows
  • iOS
  • Mac
  • Android
  • Office
Home » Office » How to Use INDEX MATCH Function in Excel

How to Use INDEX MATCH Function in Excel

By: Waseem Patwegar

The INDEX and MATCH Function in Excel can be used in combination to Lookup values much faster than VLOOKUP. You will find below the steps to use the combined INDEX MATCH Function in Excel.

Use INDEX MATCH Function in Excel

INDEX MATCH Function in Excel

While the VLOOKUP function is widely used, it is known to be resource intensive and slow due to its inherent tendency to scan data columns from left to right, while performing a Lookup.

In comparison, the hybrid INDEX MATCH Function is faster and more efficient due to its ability to go to the exact location of information, using a combination of INDEX and MATCH Functions.

This GPS like accuracy is achieved by using INDEX function to identify ‘Data Array’ (where info to Lookup is located) and MATCH Function to pinpoint the location of Item (for which info is required).

Illustration of Excel INDEX MATCH Function

As you can see in above image, the INDEX Function identifies the location of Price Column and the Match Function pinpoints the Location of Item (Chromebook).

So, let us go ahead and take a look at the steps to use INDEX and MATCH Functions in combination to perform a faster lookup.

1. How to Use INDEX MATCH Function in Excel

To illustrate the use of INDEX and MATCH Functions in combination, let us try to find the Price of MacBook from a Sales Data listing different types of computers sold along with their selling prices.

1. Type the Name of the item (MacBook) for which price is required in Cell A13.

Computer Sales Data

2. Next, place the curser in Cell B13 and start typing =INDEX – This will bring up the Syntax of Index Function.

Syntax of INDEX Function in Excel

3. Select E1:E10 as the INDEX Array – This is where the Price of items is located.

Select Array For INDEX Function in Excel

4. Now, instead of pointing to Row Number, type =Match – As we are going to use the Match Function to point to the location of Item Name (Chromebook) in our Data.

Start MATCH Function in Excel

Note: Manually pointing to the Location of Item Name (Row, Col) is not easy in large data. Hence, the MATCH Function is being used to point to the exact location of Item Name.

5. Select, Cell A13 as the lookup_value – This is the item for which Price is required.

Enter MATCH Function Lookup Value

6. Select A1:A10 as the lookup_array – This is the range where item Names are located.

Enter MATCH Function Lookup Array

7. The next step is to state the Match Type – Select 0 – Exact Match.

Select Match Type in Excel MATCH Function

8. Finally, close the Brackets and hit the Enter Key on the Keyboard of your computer.

Complete INDEX MATCH Function in Excel

Once you press the Enter Key, the INDEX MATCH Function will perform a Matrix search (Horizontally & Vertically) to bring the Price of Chromebook in Cell B13.

Result of INDEX MATCH Function in Excel

As you can see from above image, INDEX MATCH Function has scanned 2 data columns to find the Price of Chromebooks.

In comparison, the VLOOK Function would have scanned 5 data columns to accomplish the same task.

Related
  • How to Create Pivot Table in Excel
  • How to Create Two Pivot Tables in Single Worksheet
  • How to Use SUMIF Function in Excel

You May Also Like
    • Prevent Outlook From Adding Calendar Events
      How to Prevent Outlook From Adding Calendar Events
      • Show Developer Tab in Excel Ribbon Menu
        Add or Show Developer Tab in Excel Ribbon Menu
        • Set Out of Office Message in Microsoft Teams
          How to Set Out of Office Message in Microsoft Teams

Primary Sidebar



Gadget Guide

  • Should You Buy MacBook or Windows Laptop
  • Chromebook vs Windows Laptop
  • 12 Reasons to Switch to Chromebook
  • Kindle E-Reader Comparison
  • Best Asus Laptops Under 500




About | Privacy Policy | Disclosure | Terms | Contact
© 2023 Techbout.com · All Rights Reserved