Locked 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 - Begin.xlsx25.4 KB Other Lookup Methods - Complete.xlsx

25.4 KB

## Quick reference

### Other Lookup Methods

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

### When to use

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**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 and then across columns
- HLOOKUP’s syntax (in plain English) is similar to VLOOKUP’s
- =HLOOKUP([
should it look for],[*what**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.