# Using INDEX MATCH

The

Before we begin, it is important to realize that

## The INDEX function

We'll start with an overview of the

=INDEX (range ,row_or_column )

That may sound a bit complicated, but it's actually easy once you see it in action. Take, for example, the following sheet:

=INDEX (C3:C5 ,3 )

Output:9

In this example, the formula outputs the number

Here's another example, in which we've changed the

=INDEX (C3:C5 ,1 )

Output:15

In this example, the formula outputs the number

Let's look at one more example, in which our range is a series of horizontal cells rather than vertical ones:

=INDEX (B2:C2 ,2 )

Output:"Sales (MM)"

As you can see,

This function may not seem particularly useful — and, used alone, it isn't — but when combined with

## Save an hour of work a day with these 5 advanced Excel tricks

Work smarter, not harder. Sign up for our 5-day mini-course to receive **must-learn lessons** on getting Excel to do your work for you.

- How to create
**beautiful table formatting**instantly... - Why to
**rethink the way you do VLOOKUPs**... - Plus, we'll reveal
**why you shouldn't use PivotTables**and what to use instead...

## The MATCH function

The

=MATCH (lookup_value ,lookup_range ,match_type )

Here's an example of

=MATCH ("Cookies" ,B3:B5 ,0 )

Output:3

In this example, the formula outputs the number

Here's another example of

=MATCH ("Brownies" ,B3:F3 ,0 )

Output:5

In this example, the formula outputs the number

## Putting it all together

So, how do we combine

=INDEX (range ,MATCH (lookup_value ,lookup_range ,match_type ))

Let's take a closer look at what's going on here. First, we call

That's a lot to digest, so let's take a look at an example to make things simpler. The following spreadsheet shows SnackWorld production by month. Let's say we want to look up how many Cookies were produced in March using the following table. We could do this easily using

=INDEX (C3:C7 ,MATCH ("March" ,B3:B7 ,0 )

Output:37

In the example above, the formula outputs the number

First, we perform an

Let's try it again, this time with an example that will help demonstrate some of the more advanced functionality of

=INDEX (B3:F3 ,MATCH (8 ,B4:F4 ,0 )

Output:"Gummy worms"

In this case, we're using the function on a horizontal range, and we're looking up something in a table header, rather than table data itself. However,

## INDEX MATCH with wildcards

You can also use

## Why INDEX MATCH is better than VLOOKUP

After all this, you may be wondering why we even bother using

Not quite. Here are a few reasons you might want to use

**You don't have to count.**WithINDEX MATCH , there's no more worrying about counting to figure out which column you need to pull from. You just select your lookup column and your results column, and you're done.**You can safely insert columns.**WithVLOOKUP , if you insert a column in between the start of your table and the column you want to reference, your formula will break — thecolumn_index_number within yourVLOOKUP won't update.INDEX MATCH , on the other hand, safely updates no matter where you insert columns.**You can lookup backwards**.VLOOKUP only allows you to look up from columns that are in front of your starting point. Not so withINDEX MATCH — you can pull from any column you want to.**Separate formulas**. Now you don't need to remember separate formulas forVLOOKUP andHLOOKUP .**More complex functionality.**INDEX MATCH doesn't stop with the above tutorial. You can also use an INDEX MATCH MATCH to look up across both rows and columns, or use an INDEX MATCH with multiple criteria.

Now you know how to use

## Save an hour of work a day with these 5 advanced Excel tricks

Work smarter, not harder. Sign up for our 5-day mini-course to receive **must-learn lessons** on getting Excel to do your work for you.

- How to create
**beautiful table formatting**instantly... - Why to
**rethink the way you do VLOOKUPs**... - Plus, we'll reveal
**why you shouldn't use PivotTables**and what to use instead...