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!

This entry was posted in Function-Related Lessons, Workbook/Reference Lessons and tagged , , , , , , , , , . Bookmark the permalink.

5 Responses to Video Lesson: Advanced Lookup – Index & Match

  1. Anonymous says:

    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. Naveen says:

    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. Bianca says:

    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!

  4. Shannon Fretz says:

    I don’t think this data is on Masterbook

    • 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!

Comments are closed.