Subscriber only lesson.

Sign up to the Microsoft Excel for Mac - Advanced course to view this lesson.

## About this lesson

Learn to work with INDEX, MATCH and HLOOKUP, as alternatives to the VLOOKUP function.

## Exercise files

Download this lessonâ€™s related exercise files.

Other Lookup Methods.xlsx14.9 KB Other Lookup Methods - Completed.xlsx

15.2 KB

## Quick reference

### Topic

Other lookup methods.

### Description

Working with INDEX, MATCH and HLOOKUP, as alternatives to the VLOOKUP function.

### Where/when to use the technique

INDEX and MATCH can be used in combination to create truly dynamic lookup methods, where HLOOKUP is better suited to horizontal data than VLOOKUP.

### Instructions

#### INDEX

- Allows you to return a value based on the numeric row and column positions within a range.
- =INDEX(A1:C10,5,2) Returns the value in B5 (the 5
^{th}row and 2^{nd}column of the data set)

#### MATCH

- Works similar to VLOOKUP, but returns the numeric position of the match, allowing it to be used in INDEX functions
- The MATCH syntax (in English) is: =MATCH([
should it look for],[*what*is the table to look in],[*where*if it can’t find a match])*guess* - =MATCH(“Coffee”,A1:A10,FALSE) would return the numeric position of the Coffee record in cells A1:A10. This function could then be nested in the row parameter of the INDEX function.
- Note that INDEX/MATCH combinations are actually faster than VLOOKUP formulas; something that can become important in very large workbooks.

#### HLOOKUP

- HLOOKUP works just like VLOOKUP, only it looks across rows, then down rows, unlike VLOOKUP which looks down columns then across columns
- HLOOKUP’s syntax (in plain English) is similar to VLOOKUP’s
- =HLOOKUP([
should it look for],[*what*is the table to look in],[*where*row should it return a value from],[*which*if it can’t find a match])*guess* - All the caveats related to VLOOKUP with exact and approximate matches apply to HLOOKUP as well

Lesson notes are only available for subscribers.