9/23/2019 How to use the Excel VLOOKUP function | Exceljet
Cart Login
Quick, clean, and to the point
Training Videos Functions Formulas Shortcuts Blog
Search... Search
Excel VLOOKUP Function
https://exceljet.net/excel-functions/excel-vlookup-function 1/25
9/23/2019 How to use the Excel VLOOKUP function | Exceljet
Summary
VLOOKUP is an Excel function to lookup and retrieve data from a speci c column in table.
VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial
matches. The "V" stands for "vertical". Lookup values must appear in the rst column of
the table, with lookup columns to the right.
Purpose
Lookup a value in a table by matching on the rst column
Return value
The matched value from a table.
Syntax
=VLOOKUP (value, table, col_index, [range_lookup])
Arguments
value - The value to look for in the rst column of a table.
table - The table from which to retrieve a value.
col_index - The column in the table from which to retrieve a value.
range_lookup - [optional] TRUE = approximate match (default). FALSE = exact match.
Usage notes
VLOOKUP is designed to retrieve data in a table organized into vertical rows, where each
row represents a new record. The "V" in VLOOKUP stands for vertical:
https://exceljet.net/excel-functions/excel-vlookup-function 2/25
9/23/2019 How to use the Excel VLOOKUP function | Exceljet
If you have data organized horizontally, use the HLOOKUP function.
VLOOKUP only looks right
VLOOKUP requires a lookup table with lookup values in the left-most column. The data
you want to retrieve (result values) can appear in any column to the right:
VLOOKUP retrieves data based on column number
When you use VLOOKUP, imagine that every column in the table is numbered, starting
from the left. To get a value from a particular column, simply supply the appropriate
number as the "column index":
https://exceljet.net/excel-functions/excel-vlookup-function 3/25
9/23/2019 How to use the Excel VLOOKUP function | Exceljet
= VLOOKUP(H3,B4:E13,2,FALSE) // first
= VLOOKUP(H3,B4:E13,3,FALSE) // last
= VLOOKUP(H3,B4:E13,4,FALSE) // email
VLOOKUP has two matching modes, exact and approximate
VLOOKUP has two modes of matching: exact and approximate, which are controlled by
the 4th argument, called "range_lookup". Set range_lookup to FALSE to force exact
matching, and TRUE for approximate matching.
Important: range_lookup defaults to TRUE, so VLOOKUP will use approximate matching by
default:
= VLOOKUP(value, table, column) // default, approximate match
= VLOOKUP(value, table, column, TRUE) // approximate match
= VLOOKUP(value, table, column, FALSE) // exact match
Example 1: Exact match
In most cases, you'll probably want to use VLOOKUP in exact match mode. This makes
sense when you have a unique key to use as a lookup value, for example, the movie title in
https://exceljet.net/excel-functions/excel-vlookup-function 4/25
9/23/2019 How to use the Excel VLOOKUP function | Exceljet
this data:
The formula in H6 to lookup year based on an exact match of movie title is:
= VLOOKUP(H4,B5:E9,2,FALSE) // FALSE = exact match
Example 2: Approximate match
You'll want to use approximate mode in cases when you're looking for the best match, not
an exact match. A classic example is nding the right commission rate based on a
monthly sales number. In this case, you want VLOOKUP to get you the best match for a
given lookup value. In the example below, the formula in D5 performs an approximate
match to retrieve the correct commission.
https://exceljet.net/excel-functions/excel-vlookup-function 5/25
9/23/2019 How to use the Excel VLOOKUP function | Exceljet
= VLOOKUP(C5,$G$5:$H$10,2,TRUE) // TRUE = approximate match
Note: your data must be sorted in ascending order by lookup value when you use approximate
match mode with VLOOKUP.
More about VLOOKUP:
Detailed VLOOKUP examples
23 things you should know about VLOOKUP
How to nd the rst or last match
Other notes
Range_lookup controls whether value needs to match exactly or not. The default
is TRUE = allow non-exact match.
Set range_lookup to FALSE to require an exact match and TRUE to allow a non-
exact match.
https://exceljet.net/excel-functions/excel-vlookup-function 6/25
9/23/2019 How to use the Excel VLOOKUP function | Exceljet
If range_lookup is TRUE (the default setting), a non-exact match will cause the
VLOOKUP function to match the nearest value in the table that is still less than
value.
When range_lookup is omitted, the VLOOKUP function will allow a non-exact
match, but it will use an exact match if one exists.
If range_lookup is TRUE (the default setting) make sure that lookup values in the
rst row of the table are sorted in ascending order. Otherwise, VLOOKUP may
return an incorrect or unexpected value.
If range_lookup is FALSE (require exact match), values in the rst column of table
do not need to be sorted.
VLOOKUP formula examples
Two-way lookup with VLOOKUP
Preface Inside the VLOOKUP function, the column index
argument is normally hard-coded as a static number.
However, you can also create a dynamic column index by
using the MATCH function to locate the right column....
Get rst text value in a list
If you need to get the rst text value in a list (a one-
column range) you can use the VLOOKUP function set to
exact match, with a wildcard character for the lookup
value. In the example the formula in D7 is: =...
Group numbers with VLOOKUP
If you need to group by number, you can use the
VLOOKUP function with a custom grouping table. This
allows you to make completely custom
or arbitrary groups. In the example shown, the formula in
F7 is: =VLOOKUP(D5,...
https://exceljet.net/excel-functions/excel-vlookup-function 7/25
9/23/2019 How to use the Excel VLOOKUP function | Exceljet
Map inputs to arbitrary values
If you need to map or translate inputs to arbitrary values,
you can use the VLOOKUP function. In the example, we
need to map the numbers 1-6 as follows: Input Output 1
10 2 81...
Basic Tax Rate calculation with VLOOKUP
To calculate a tax rate based on a simple tax rate table,
you can use the VLOOKUP function. In the example
shown, the formula in G5 is:
=VLOOKUP(G4,tax_table,2,TRUE) where "tax_table" is the
named range C5:D8. Note...
Get nth match with VLOOKUP
To get the nth MATCH with VLOOKUP, you'll need to add
a helper column to your table that constructs a unique id
that includes the count. If this isn't practical, you can use
an array formula based on INDEX and MATCH...
Merge tables with VLOOKUP
To merge tables, you can use the VLOOKUP function to
lookup and retrieve data from one table to the other. To
use VLOOKUP this way, both tables must share a common
id or key. This article explains how join tables using...
VLOOKUP from another sheet
Using VLOOKUP from another sheet is very similar to
using VLOOKUP on the same sheet. In the example
shown, the formula in F5 is:
=VLOOKUP(B5,Sheet2!$B$5:$C$104,2,0) Here,
VLOOKUP pulls the correct building for each...
Income tax bracket calculation
https://exceljet.net/excel-functions/excel-vlookup-function 8/25
9/23/2019 How to use the Excel VLOOKUP function | Exceljet
To calculate total income tax based on multiple tax
brackets, you can use VLOOKUP and a rate table
structured as shown in the example. The formula in G5 is:
=VLOOKUP(inc,rates,3,1)+(inc-
VLOOKUP(inc,rates,1,1))*VLOOKUP...
VLOOKUP with multiple critiera
The VLOOKUP function does not handle multiple criteria
natively. However, if you have control over source data,
you can use a helper column to join multiple elds
together, and use these elds like multiple criteria...
Get employee information with VLOOKUP
If you want to retrieve employee information from a
table, and the table contains a unique id to the left of the
information you want to retrieve, you can easily do so
with the VLOOKUP function. In the example shown,...
Calculate shipping cost with VLOOKUP
To calculate shipping cost based on weight, you can use
the VLOOKUP function. In the example shown, the
formula in F8 is: =VLOOKUP(F7,B6:C10,2,1)*F7 This
formula uses the weight to nd the correct "cost per kg"...
Calculate grades with VLOOKUP
To assign a grade based on a score, you can use the
VLOOKUP function con gured to perform an
approximate match. In the example shown, "key" is the
named range B5:C9, the formula in F5. copied down, is:
=VLOOKUP(E5,...
Simple currency conversion
To convert from a given currency to other speci c currencies, you can use the VLOOKUP
function. In the example shown, the formula in E5 is: =VLOOKUP(D5,xtable,2,0)*B5
https://exceljet.net/excel-functions/excel-vlookup-function 9/25
9/23/2019 How to use the Excel VLOOKUP function | Exceljet
which converts the amount in column B from US...
Self-contained VLOOKUP
To make a self-contained VLOOKUP formula, you can
convert the table to an array constant inside of
VLOOKUP. In the example shown, the formula in F7 is:
=VLOOKUP(E7,{0,"F";60,"D";70,"C";80...
Related videos
How to use INDEX and MATCH with a table
In this video, we'll look at how to use INDEX and MATCH
with an Excel Table. Using INDEX and MATCH with an
Excel Table is wonderfully straightforward.
Structured references inside a table
In this video, we'll look at how to use structured reference
syntax inside a table.
How to use VLOOKUP to merge tables
This video shows how to use VLOOKUP to merge data in
two tables based on a common ID. You can even use this
technique to reorder the columns you retrieve.
How to select arguments with the formula tip window
https://exceljet.net/excel-functions/excel-vlookup-function 10/25
9/23/2019 How to use the Excel VLOOKUP function | Exceljet
In this video, we'll look the best way to select function
arguments in a formula - the function screen tip window.
Why VLOOKUP is better than nested IFs
In this video, we look at a few reasons why VLOOKUP is a
better option than nested IF statements.
How to use the CHOOSE function
In this video, we'll look at how you can use the CHOOSE
function. We also compare a formula based on CHOOSE
vs. a formula based on VLOOKUP.
Related functions
Excel HLOOKUP Function
HLOOKUP is an Excel function to lookup and retrieve
data from a speci c row in table. The "H" in HLOOKUP
stands for "horizontal", where lookup values appear in the
rst row of the table, moving horizontally to the right.
HLOOKUP supports...
Excel LOOKUP Function
The Excel LOOKUP function performs an approximate
match lookup in a one-column or one-row range, and
returns the corresponding value from another one-
column or one-row range. LOOKUP's default behavior
makes it useful for solving certain problems...
https://exceljet.net/excel-functions/excel-vlookup-function 11/25
9/23/2019 How to use the Excel VLOOKUP function | Exceljet
Excel INDEX Function
The Excel INDEX function returns the value at a given
position in a range or array. You can use index to retrieve
individual values or entire rows and columns. INDEX is
often used with the MATCH function, where MATCH
locates and feeds a position to...
Excel MATCH Function
MATCH is an Excel function used to locate the position of
a lookup value in a row, column, or table. MATCH
supports approximate and exact matching, and wildcards
(* ?) for partial matches. Often, the INDEX...
See also
23 things you should know about VLOOKUP
Danger: beware VLOOKUP defaults
Good links
Microsoft VLOOKUP function documentation
Excel Formula Training
Formulas are the key to getting things done in
Excel. In this accelerated training, you'll learn
how to use formulas to manipulate text, work
with dates and times, lookup values with
VLOOKUP and INDEX & MATCH, count and sum
with criteria, dynamically rank values, and create
dynamic ranges. You'll also learn how to
https://exceljet.net/excel-functions/excel-vlookup-function 12/25
9/23/2019 How to use the Excel VLOOKUP function | Exceljet
troubleshoot, trace errors, and x problems. Instant access. See details here.
500 Formula Examples, thoughtfully explained.
Download 200+ Excel Shortcuts
Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.
email address Get the PDF
Topics
Video training
Formula Basics
Function Guide
Formula Examples
Formula Criteria
Formula Errors
Excel Shortcuts
https://exceljet.net/excel-functions/excel-vlookup-function 13/25
9/23/2019 How to use the Excel VLOOKUP function | Exceljet
Pivot Tables
101 Excel Functions
Excel Tables
INDEX and MATCH
Excel Charts
Conditional Formatting
Custom Number Formats
Data Validation
Nested IF examples
Formula challenges
How-to videos
Excel glossary
Excel people
Excel books
Key functions
IF function
VLOOKUP function
SUMIFS function
COUNTIFS function
INDEX function
MATCH function
SUMPRODUCT function
More...
Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa.
Our goal is to help you work faster in Excel. We create short
videos, and clear examples of formulas, functions, pivot
tables, conditional formatting, and charts. Read more.
https://exceljet.net/excel-functions/excel-vlookup-function 14/25
9/23/2019 How to use the Excel VLOOKUP function | Exceljet
NOW
PLAYING
https://exceljet.net/excel-functions/excel-vlookup-function 15/25
9/23/2019 How to use the Excel VLOOKUP function | Exceljet
https://exceljet.net/excel-functions/excel-vlookup-function 16/25
9/23/2019 How to use the Excel VLOOKUP function | Exceljet
https://exceljet.net/excel-functions/excel-vlookup-function 17/25
9/23/2019 How to use the Excel VLOOKUP function | Exceljet
https://exceljet.net/excel-functions/excel-vlookup-function 18/25
9/23/2019 How to use the Excel VLOOKUP function | Exceljet
https://exceljet.net/excel-functions/excel-vlookup-function 19/25
9/23/2019 How to use the Excel VLOOKUP function | Exceljet
https://exceljet.net/excel-functions/excel-vlookup-function 20/25
9/23/2019 How to use the Excel VLOOKUP function | Exceljet
https://exceljet.net/excel-functions/excel-vlookup-function 21/25
9/23/2019 How to use the Excel VLOOKUP function | Exceljet
https://exceljet.net/excel-functions/excel-vlookup-function 22/25
9/23/2019 How to use the Excel VLOOKUP function | Exceljet
https://exceljet.net/excel-functions/excel-vlookup-function 23/25
9/23/2019 How to use the Excel VLOOKUP function | Exceljet
Your site is very well laid out. The consistent formatting and
easy on the eyes fonts make for much less fatigue when
reading. The examples are excellent and help to improve
everyone's understanding of Excel functions...ExcelJet is my
rst reference point when working on a complex Excel sheet.
-Doug
https://exceljet.net/excel-functions/excel-vlookup-function 24/25
9/23/2019 How to use the Excel VLOOKUP function | Exceljet
Excel video training
Quick, clean, and to the point.
Learn more
Home About Blog Contact
© 2012-2019 Exceljet. Terms of use
Feedback
https://exceljet.net/excel-functions/excel-vlookup-function 25/25
