Subscriber only lesson.

Sign up to the Microsoft Excel - Basic & Advanced course to view this lesson.

## About this lesson

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

## Lesson versions

Multiple versions of this lesson are available, choose the appropriate version for you:

2013, **2016**.

## Exercise files

Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.

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

14.5 KB

## Quick reference

### Topic

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

#### Sample data

- Create a new worksheet

#### 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
- MATCH is NOT case sensitive. (Looking up dog in a table will return Dog.)

#### 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**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, e.g. HLOOKUP matches are NOT case sensitive. (Looking up dog in a table will return Dog.)

Lesson notes are only available for subscribers.