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.