## About this lesson

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

## Exercise files

Download this lesson’s related exercise files.

VLOOKUP with Exact Match.xlsx14.3 KB VLOOKUP with Exact Match - Completed.xlsx

14.3 KB

## Quick reference

### Topic

VLOOKUP (exact match).

### Description

Using VLOOKUP to locate an exact match in a data table, and return the value from a corresponding column.

### Where/when to use the technique

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

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