Index Match combines two of Excel’s most powerful and useful functions, **INDEX** and **MATCH**, into a single formula. This gives you flexibility to pull specific values from even the largest datasets. This has a variety of practical applications, as you’ll soon see. Let’s learn **how to use Index Match in Microsoft Excel.**

## The INDEX and MATCH Functions in Excel

The term “Index Match” is a bit misleading in its title. Rather than being a standalone function, it’s a combination of two. In essence, the functions nest together inside a formula. Before diving into these combinations, let’s quickly review what each function does individually.

**INDEX** returns a value from a range, working across both rows and columns. For example, imagine a list of ten names like this. You want to find the seventh name in the list and display it in a separate cell. Click into a cell to begin a formula and type:

**=INDEX(A1:A10,7)**, where **A1:A10** is your range and **7** is the location in the range. Hit **Enter**, and the name appears in the cell.

**MATCH** works similarly, but it returns the position of a value in a range, not the actual value.

For example, imagine a row listing six cities. You want to know the position of Miami in the range. In an empty cell, type:

**=MATCH(“miami”,C1:H1,0)**, where **“miami”** is your lookup value, **C1:H1** is your range, and **0** signifies an exact match. Notice that the lookup value is **not** case-sensitive.

Hit **Enter**, and the position from left to right is displayed: **4.**

### How to Combine INDEX and MATCH in Excel

Now that you understand the basics of **INDEX** and **MATCH** and how they work, it’s time to bring the two functions together.

Let’s now imagine a more complex arrangement of data. Consider a multi-column range of products with their serial numbers, names, and prices listed. These are spread over columns **A**, **B,** and **C.**

In cell **F1**, you want to be able to type in the serial number and have the price return in the cell below it, **F2**. This is a great time to merge **INDEX** and **MATCH** together.

A key rule of thumb is that, when you combine these two functions, **INDEX** always goes first, with **MATCH** following – nested – within the **INDEX** function.

Begin by clicking into cell **F2**, where you want the price to display. Type **=** on your keyboard, telling Excel that you’re beginning to input a formula. Then, add your index function:

=INDEX(

Then, click and drag to select the range of prices, which is the range covering **C2:C6. **Then, type a comma. So far, your formula is:

=INDEX(C2:C6,

You have the **INDEX** function looking in the correct column now. What then needs to occur is for the function to know which serial number’s price to pull from the column. This is done by nesting the **MATCH** function.

Type **MATCH(**, and then click on cell **F1.** Remember, you want to be able to change this number and have Excel automatically return the product price:

=INDEX(C2:C6,MATCH(F1

Add another comma, select the serial number range, **A2:A6**, and then type in a **0** to ensure an exact match. Close with a double parentheses, **))**, to enclose the **MATCH** function as well as the complete **INDEX** function.

Your complete formula reads:

=INDEX(C2:C6,MATCH(F1,A2:A6,0))

You now have a dynamic formula built using **INDEX** and **MATCH**. Type any serial number into cell **F1**, then hit **Enter** on your keyboard. Excel will instantly display the appropriate price for the product in question.

### How to Look Left with INDEX and MATCH

Astute users may notice that the above example would be perfectly workable when formatted as a **VLOOKUP**. But this technique has a key advantage over **VLOOKUP**. Remember, **VLOOKUP** looks only to the right across a dataset. Using a similar version of our data, let’s view it in the order of **Price** – **Serial #** – **Name**.

Let’s say you want to look up the price by inputting the serial number. That’s impossible with a **VLOOKUP** in this orientation, because the price is listed to the left of the lookup value, which is the serial number. But a combination of **INDEX** and **MATCH** will handle the job neatly.

Click into cell** F2**, and start by inserting the **INDEX** function:

=INDEX(

Remember, the value you want is in column **A**, the price. Click and drag to select **A2:A6, **the range of prices. Type a comma, then add your **MATCH** function. The formula, so far, is:

=INDEX(A2:A6,MATCH(

You want to change cell **F1** by inputting a serial number, in order to have **F2** return the correct price. Therefore, type **F1**. It’s a lookup value, so type a comma, then select the range of serials, **B2:B6:**

=INDEX(A2:A6,MATCH(F1,B2:B6

And finally, wrap up with a comma, followed by **0** for an exact match, and two closed parentheses:

=INDEX(A2:A6,MATCH(F1,B2:B6,0))

Now, when you input a serial number in cell **F1**, the price will display in cell **F2**. This wouldn’t be possible with a VLOOKUP with the layout and arrangement of your data.

### Two-Way Lookups with INDEX and MATCH

Another useful feature of **INDEX** and **MATCH** is the ability to perform what Excel calls two-way lookups. In the example above, you constructed a function to search for a price based on a serial number input. Now, imagine a more complex dataset where you want to do an advanced search.

Imagine that you sell products in three sizes and three colors. You can buy **small**, **medium**, or **large**, in **red**, **blue**, or **green**. You have the prices for each of the nine options listed in a table. The leftmost column contains the sizes, and the three adjacent columns contain the colors. You want to be able, at a moment’s notice, to identify the price of a **medium green** product, for example.

This time, we’ll let cells **G1** and **G2** be the pair of inputs, while cell **G3** will be the price output. Click into cell **G3**. By now, you’re familiar with the syntax, and won’t be surprised to learn that step one is to type** =INDEX(**. As mentioned above, this is always how you combine the **INDEX** and **MATCH** functions.

**INDEX** here needs to encompass the full range of values, **B2:D4**. Then, add the first of two **MATCH** functions:

=INDEX(B2:D4,MATCH(

Click cell **G1**, then select the range of sizes in your table, **A2:A4**, then type **0** for an exact match:

=INDEX(B2:D4,MATCH(G1,A2:A4,0)

Conclude with a second **MATCH** to capture the range of colors:

=INDEX(B2:D4,MATCH(G1,A2:A4,0),MATCH(G2,B1:D1,0))

Now, you can specify inside cells **G1** and **G2** exactly what size and color combination you want. Excel will run the **INDEX** and paired **MATCH** functions to return your result. For instance, try out **Medium** and **Green**, and cell **G3** will return a price of **$37.00** from your data.

It’s easy to see the power of **INDEX** and **MATCH**, even as individual functions. But when you combine them together, you’ll find yourself working with an absolute powerhouse formula right inside Microsoft Excel.

It renders even the most complex and large datasets easily searchable. After all, even the best data isn’t worth much if it isn’t helping you. Give the **INDEX** and **MATCH** combination a try next time you need to work with a large volume of data in a quick, easy, and interactive way.