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 exact 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 Exact Match.xlsx13.1 KB VLOOKUP with Exact Match - Completed.xlsx

13.2 KB

## Quick reference

### Topic

Using VLOOKUP to find an exact match in a data table and return it or the corresponding value from another column.

### When to use

Using VLOOKUP with an exact match is ideal to look up product numbers, customer data or other data where there is a unique key to find in a data table.

### 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 exact matches

- The final parameter for VLOOKUP with an exact match must be set to FALSE.
- The first column of your data does NOT need to be sorted in ascending order. (VLOOKUP will keep looking until it finds a match or figures out there is no match.)
- 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 exact matches are NOT case sensitive. (Looking up dog in a table will return Dog.)
- We can nest formulae into the “what to look for” section of the VLOOKUP formula.
- If VLOOKUP cannot find a match it will return #N/A.

#### Common VLOOKUP #N/A causes

- There is a space at the end of the value being looked up or the value in the table.
- One side of the lookup value or table is text, while the other is numbers.

#### Syntax

- =VLOOKUP(5000,A1:B10,2,FALSE)
- Will return the value from the second column of the table where the record in column A is equal to 5000.
- If the exact value of 5000 is not in the first column of the table, it will return #N/A.

Lesson notes are only available for subscribers.