Subscriber only lesson.

Sign up to the Microsoft Excel for Mac - 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.

## Exercise files

Download this lessonâ€™s related exercise files.

VLOOKUP with Approximate Match.xlsx14.5 KB VLOOKUP with Approximate Match - Completed.xlsx

14.7 KB

## Quick reference

### Topic

VLOOKUP (approximate match).

### Description

Using VLOOKUP with an approximate match to return the closest value in a column without going over.

### Where/when to use the technique

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

### Instructions

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

- =VLOOKUP([
should it look for],[*what*is the table to look in],[*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.