What is the Microsoft Excel
VLOOKUP Function?
The VLOOKUP function is a computer
program that you run from a worksheet cell formula. It looks up
a value you designate in the first column of worksheet
area (called a range) and
returns a value from the row it finds the lookup value on. In
other words it is a standard table lookup that you learned in
school. You run the VLOOKUP worksheet function by typing its
name in a formula then followed by the information it is suppose
to use. The VLOOKUP worksheet function is used in applications
when you have to look up prices, name information, scientific
data anything that is contained in a table. It can also be used
to merge two tables together transferring information from one
to the other. The V in VLOOKUP stands for vertical. There is
also another function called HLOOKUP that looks along the first
row of a table. Use VLOOKUP when your comparison values are
located in the first column of the data that you want to find.
Note that this function also has the capability to search for
parts of text within other text when evaluating its lookup
value.
How Do You Type the VLOOKUP
Worksheet Function in a Formula?
Whenever you type a formula in a
worksheet cell, this is called syntax or grammar. The general
VLOOKUP function syntax has a format like this when you type it
in a worksheet cell:
=VLOOKUP(lookup_value, table_array,
col_index_num, [range_lookup])
Where lookup_value, table_array,
col_index_num, [range_lookup] are called the function argument
list. Remember, you are running a computer program at this point
so the program needs information to operate and that is why
there is an argument list. When you see an argument list and you
see square brackets [ ] around the argument name, this means the
argument is optional and you do not have to include it when
typing unless you need it. So for the syntax above, you need to
include 3 arguments for the VLOOKUP worksheet function when
typing it in a worksheet cell formula in order for it to
calculate correctly. What argument values can be used are
discussed below. Remember functions expect certain things in
their argument lists, if you do not put the correct information
in the list they will generate an error when run.
VLOOKUP Worksheet Function
Argument Definitions
 lookup_value: The value to
search for in the first column of your data. Lookup_value
can be a value, a cell reference or formula. Be sure there
are no repeated values in this column. If there are, VLOOKUP
will lock onto the first one encountered and ignore the
rest. These values searched for in the first column can be
text, numbers or logical values. If text is present, they
are not case sensitive. If searching for a text value, you
can use wild card characters like ? and * to go after a
specific character or a group of characters. You can also
use the ampersand (&) to help concatenate the lookup value
on the fly. Be sure to surround text lookup values with
quotes " ", numeric lookup values do not take quotes.
 table_array: Two or more
columns of data, in other words a range (we call it a table
outside of Excel). You can identify this range by range
reference (i.e. A1:B4) or use a range name like sales*. You
can include the headers of the data in the reference as long
as they do not interfere with the lookup_value search. * It
is possible to name a cell or group of cells on a worksheet
and use that name in place of a range reference or cell
reference. Consult Excel help on how to name a cell.
 col_index_num: The column
number in your range that contains the value you want to
return. For example, you have two columns of data located in
columns C and D. The lookup column is C, it contains the
values to scan and column D contains the values to return. C
is considered column 1 and D is considered column 2. If the
VLOOKUP finds the value in column C at row 4 and you tell it
to return column 2, then it would return the value of D4.
Remember the column number system that you use is relative
to the range of data, not worksheet column location. So if
you data starts in column G that is column 1 to VLOOKUP. If
you do not indicate the proper column say, you put 4 instead
of 2 and there are only 2 columns of data, an error will be
returned.
 range_lookup
(Optional): VLOOKUP can find an absolute match or
approximate match in the first column of data it is looking
in. Use TRUE for this argument if you want an approximate
match or FALSE if you want an absolute match.
 If you use TRUE or do
not include it, an exact or approximate match is
returned. If an exact match is not found, the next
largest value that is less than lookup_value is
returned. The values in the first column of your data
must be placed in ascending sort order; otherwise,
VLOOKUP may not give the correct value. You can put the
values in ascending order by sorting the data.
 If you use FALSE,
VLOOKUP will only find an exact match. In this case, the
values in the first column of data do not need to be
sorted. If there are two or more values in the first
column that match the lookup_value argument, the first
value found is used. If an exact match is not found, the
error value #N/A is returned.
* It is possible to name a cell or group
of cells on a worksheet and use that name in place of a range
reference or cell reference. Consult Excel help on how to name a
cell.
How Do You Run the VLOOKUP
Function?
Since the VLOOKUP function is a computer
program, it runs when you press Enter to enter the formula that
contains it. If any of the arguments are wrong, the function
will return an error.
What Do I Type for a VLOOKUP
Function Argument?
When typing the VLOOKUP function in a
worksheet cell formula, you need to replace the argument list
with arguments separating each one with a comma (,). Some
typical arguments you can use are:
Cell Formula 
Explanation 
=VLOOKUP("Widget1", A1:D5, 2, FALSE) 
Find Widget1 in Column A then return information from
Column B the row that Widget1 was found on 
=VLOOKUP("*ST*", G1:J5, 4, FALSE) 
Find the piece of text ST inside the cell value text in
Column G then return information from Column J the row
that ST was found on 
=VLOOKUP("*" & B1 & "*", G1:J5, 2, FALSE) 
Find the piece of text from the B1 cell value inside the
text in Column G then return information from Column H
the row that it was found on 
=VLOOKUP(16, B1:D5, 2, FALSE) 
Find number 16 in Column B then return information from
Column C the row that 16 was found on 
=VLOOKUP(B1, Prices, 3, FALSE) 
Find the B1 cell value in range name Prices first column
then return information from 3rd Column in range the row
that B1 cell value was found on * 
* This function is used a great deal in
building adaptive formulas that figure out ranges, start and
stop of data.... It is also heavily used in dashboard design
tying numeric ActiveX control outputs into formulas that seek
out data in tables.
Additional VLOOKUP Function
Examples
The worksheet seen below contains some
typical worksheet formulas that run the VLOOKUP worksheet
function. Pay close attention to the argument list and the
syntax used to write the formula.

A 
B 
C 
1 
Searching by Names 

2 



3 
Item 
Cost 

4 
Widget1 
$1.25 

5 
Widget2 
$2.50 

6 
Widget3 
$3.50 

7 



8 


Look up name Widget1 in table
and return cost. Formula below 
9 
Cost 
$1.25 
= VLOOKUP( "Widget1", A3:B6,
2, FALSE ) 
10 



11 
Item 
Widget1 

12 
Cost 
$1.25 
= VLOOKUP( B11, A3:B6, 2,
FALSE ) 
13 



14 
Item 
Widget1 
Allow user to search, use
range name Widgets in place of A3:B6 much easier, can be
on different sheet. Formula below 
15 
Cost 
$1.25 
= VLOOKUP( B14, Widgets, 2,
FALSE ) 
16 



17 



18 
Searching by
Characters 

19 



20 
SKU# 
Cost 

21 
XST1100 
$1.25 

22 
VST2200 
$2.50 

23 
ZST3300 
$3.50 

24 



25 


Searches for XST1 in SKU#
column, lookup value based on first 5 characters using
an * after the characters, return cost. Formula below 
26 
Cost 
$1.25 
= VLOOKUP( "XST1*", A20:B23,
2, FALSE ) 
27 



28 


Searches for T3 in SKU#
column, lookup value based on T3, using * before and
after the characters, return cost. Formula below 
29 
Cost 
$3.50 
= VLOOKUP( "*T3*", A20:B23, 2,
FALSE ) 
30 



31 
Search 
T3 
Searches for T3 in SKU#
column, lookup value based on cell value B31 which is
T3, using * before and after B31 and & to combine with
*, return cost. Formula below 
32 
Cost 
$3.50 
= VLOOKUP( "*" & B31 & "*",
A20:B23, 2, FALSE ) 
33 



34 



35 
Searching by
Numbers 

36 
InTake Temp (deg F) 
Correction Factor 

37 
95 
.986 

38 
100 
.974 

39 
105 
.962 

40 
110 
.95 

41 
115 
.937 

42 
120 
.925 

43 
125 
.913 

44 
130 
.9 

45 


Using an approximate match,
4th argument omitted, searches for the value 99 in
Column A and finds the closest match 95, returns .986.
Formula below 
46 
Corr Factor 
.986 
= VLOOKUP( 99, A36:B44, 2 ) 
47 


Using an exact match, 4th
argument is FALSE, searches for the value 90 in Column A
and returns error not found. Formula below 
48 
Corr Factor 
#N/A 
= VLOOKUP( 90, A36:B44, 2,
False ) 
49 




A 
B 
C 
D 
E 
1 
Merging Tables 




2 
Item 
Cost 
SKU# 
Item 
SKU# 
3 
Widget1 
$1.25 
= VLOOKUP( A3, $D$2:$E$5, 2,
False ) 
Widget1 
XST1100 
4 
Widget2 
$2.50 

Widget2 
VST2200 
5 
Widget3 
$3.50 

Widget3 
ZST3300 
6 


Using an absolute
match, use values in column A as lookup in column D to
return SKU# to column C. Formula above 
Things to Know About the VLOOKUP
Function
 When searching text values
in the first column of your data, ensure that the data in
the first column does not have leading spaces, trailing
spaces, inconsistent use of straight ( ' or " ) and curly (
‘ or “) quotation marks, or nonprinting characters. In these
cases, VLOOKUP may give an incorrect or unexpected value.
For more information on functions that you can use to clean
text data, see the Text functions.
 When searching number or
date values, ensure that the data in the first column of
data is not stored as text values. In this case, VLOOKUP may
give an incorrect or unexpected value. For more information,
see Convert numbers stored as text to numbers in help.
 If the range_lookup argument
is FALSE and the lookup_value argument is text, then you can
use the wildcard characters, question mark (?) and asterisk
(*), in lookup_value. A question mark matches any single
character; an asterisk matches any sequence of characters.
If you want to find an actual question mark or asterisk,
type a tilde (~) preceding the character.
