Video Lesson: Advanced Lookup – Index & Match

The versatility and usefulness of the Index and Match functions are cherished by Excel veterans, but many less experienced users have either not encountered them, or are a bit confused by the usage of the two.

This Excel Video Lesson shows you how you can use the Index & Match functions to do advanced lookups which can be more reliable and versatile than just VLOOKUP or HLOOKUP alone.  In the video I go over the following uses of Index & Match:

– Two-Way Lookup (using both a row header and column header to find the intersecting value).  This can be very helpful when columns and rows may be added or deleted to your ranges.

– Averaging entire row/column information based on a lookup value.

– Summing entire row/column information based on a lookup value.

Make sure to download the most recent Master Workbook so that you can follow along at home!

5 thoughts on “Video Lesson: Advanced Lookup – Index & Match

  1. i have a question regarding the conditional formatting. how do you do the conditional formatting where it will actually format the intersecting cell in the index & match formula? I tried to do this, but i end up highlighting all those cells with duplicate values, equal to the cell reference in the array area.


  2. How did you get the result number to be highlighted within the range table each time the correct answer was produced? Was is some kind of conditional formatting?

  3. Thanks so much for this information, it’s been so helpful, after months of trying to figure out using Excel for work, I finally managed to understand it by using your tutorials in just a couple of days, thanks for your effort!

    • The example worksheet can be found labeled “13 – Advanced Lookup”.

      There should also be a ‘Table of Contents’ sheet which has links to all of the sheets (this one being number 13 – Advanced Lookup – Index & Match’.

      Hope that helps!

Post any comments, feedback or questions below!

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s