INDEX and MATCH What A Pair In #Excel!

The team of INDEX & MATCH may make a better combo than using VLOOKUP or HLOOKUP. Here’s an example how….

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

Let’s say I have the sales data for 2012 for my crew of 9, and I want to easily see the value for a given salesperson for a specific month, just by entering those into the small table I have. To make it easy on myself and to ensure I don’t mis-type my entries, I’ve utilized Data Validation to select the salesperson and month properly:

im1

The challenges with using VLOOKUP or HLOOKUP are that these both require the data to be in ascending order for an exact match. They also need the row or column where the data is located to be identified. In addition, they can only search from left to right. Not so with the combination of INDEX and MATCH.

In the example above, in cell C17 I want to enter the INDEX function using MATCH functions as the two variables in the INDEX formula. The syntax for the INDEX is:
=INDEX(array,row number,column number). Column number is optional and often excluded. The formula will be =INDEX(C4:N12,MATCH(C15,B4:B12,0),MATCH(C16,C3:N3,0)) and is defined as follows:

im2

– the array is the area where the data is stored. In this case that is C4:N12
– the row is the list of salespeople. It will check cell C15 then look for a match in cells B4:B12. The “0” indicates an exact match. Other options are 1 = less than, and -1 = greater than.
– the column is the list of months. It will check cell C16 then look for a match in cells C3:N3. The “0” indicates an exact match. Other options are 1 = less than, and -1 = greater than.

Now, by selecting the salesperson and month, the formula indexes the values in the table and, based on matches to the criteria in cells C15 & C16, returns the sales value for that salesperson and month:

im3

Not hard at all! Try it yourself with data that you have in a table. Let me know if you come across any issues, but I think you will find this works very well for this and many other scenarios!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , ,

4 Responses to INDEX and MATCH What A Pair In #Excel!

  1. Oz February 26, 2014 at 3:23 pm #

    Excellent post.
    This has me think of the battle over

    VLOOKUP vs INDEX/MATCH

    I discovered INDEX/MATCH/MATCH long before thinking of INDEX/MATCH as a VLOOKUP replacement.

    VLOOKUP serves my purposes 99% of time. When it doesn’t, the situation is usually so dynamic that INDEX/MATCH/MATCH is the go-to.

Trackbacks/Pingbacks

  1. How To Use INDEX and MATCH In A Table With INDIRECT In Excel | Excel Bytes - July 16, 2015

    […] INDEX/MATCH function combination. You can learn more about these functions on my blog post located here. I’ll define the range for my INDEX portion of my formula with just the sales data from my […]

  2. How To Analyze Completed Projects Using A PivotTable In Excel | Excel Bytes - September 11, 2015

    […] For a bit more detail on using INDEX and MATCH functions together, check out my tutorial here. […]

  3. How To SUM A Range Of Values In An Array Using INDIRECT, ADDRESS, and MATCH Functions In Excel | Excel Bytes - December 23, 2015

    […] You can find the basic INDEX/MATCH tutorial here. […]

Leave a Reply