Subscriber only lesson.

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

## About this lesson

Learn to use VLOOKUP to find an approximate match and return it or the corresponding value from another column.

## 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.

VLOOKUP with Approximate Match.xlsx15 KB VLOOKUP with Approximate Match - Completed.xlsx

15.1 KB

## Quick reference

### Topic

Using VLOOKUP to find an approximate match (not exceeding the value looked for) in a data table and return it or the corresponding value from another column.

### When to use

Using VLOOKUP with an approximate match is ideal for tables that have gaps in data, such as volume pricing or tax tables.

### Instructions

#### Sample data

- Create a new worksheet

#### VLOOKUP’s parameters (in English)

- =VLOOKUP([
should it look for],[*what**where*column should it return a value from],[*which*if it can’t find a match])*guess*

#### Key points with VLOOKUP and approximate matches

- For an approximate match, VLOOKUP’s final parameter must be set to TRUE.
- The first column of your data MUST be sorted in ascending order. (If it isn’t, you will get very strange results.)
- The column to return is the column of the table, not the column of the spreadsheet (if your VLOOKUP table starts in column B, then 1 refers to column B, as it is the first column in the table).
- VLOOKUP will return the first row that is not in excess of what you’re looking for.
- It is a best practice to declare the final parameter as TRUE, even though it defaults. (This will get you in the habit of declaring the last parameter, making it less likely to forget it when it needs to be FALSE.)

#### Syntax

- =VLOOKUP(5000,A1:B10,2,TRUE) Returns the value from the second column of the table where the record in column A is closest to 5000 without going over.

Lesson notes are only available for subscribers.