Excel VLOOKUP Function - Syntax and Basic Uses

VLOOKUP is an Excel function that searches for the value you specify and returns a matching
value from another column. More technically, the VLOOKUP function looks up a value in the
first column of a given range and returns a value in the same row from another column.

In its common usage, Excel VLOOKUP searches through your data set based on the unique
identifier and brings you a piece of information associated with that unique identifier.

The letter "V" stands for "vertical" and is used to differentiate VLOOKUP from the HLOOKUP
function that looks up a value in a row rather than column (H stands for "horizontal").

VLOOKUP syntax

The syntax for the VLOOKUP function is as follows:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

As you see, the function has 4 arguments - the first three are requited and the last one is
optional.

• Lookup_value is the value to search for.

This can be a value (number, date or text), cell reference (reference to a cell containing
a lookup value), or the value returned by some other function. Unlike numbers and
cell references, text values should always be in enclosed in "double quotes".

As an example, please have a look at the below formula and try to "translate" it into
English:

=VLOOKUP("lion", A2:B6, 2, FALSE)

At this point, we can only say that the formula obviously looks up the word "lion".

• Table_array is two or more columns of data.

The VLOOKUP function searches for the lookup value in the first column of the table
array, which may contain various text values, numbers, dates, and logical values.

So, you can now read the above formula a little further: search for "lion" in the range
A2:A6 (because A is the 1st column in our table array).

https://www.ablebits.com/office-addins-blog/2014/07/17/excel-vlookup-tutorial-beginner/
 • Col_index_num is the number of the column from which the value should be
returned. The counting starts from the leftmost column in the table array, which is 1.

Meaning, our sample formula will return a matching value from column B, which is
2nd in the table array.

• Range_lookup (optional) - determines whether to search for approximate or exact

match:
o TRUE or omitted (default) - approximate match. If an exact match is not found,
the formula searches for the closest match, i.e. the largest value that is smaller
than the lookup value. Requires sorting the lookup column in ascending order.
o FALSE - exact match. The formula searches for a value exactly equal to the
lookup value. If an exact match is not found, a #N/A value is returned.

Knowing all the arguments, you should now have no problem reading the whole formula:
search for "lion" in A2:A6, find an exact match, and return a value from column B in the same
row:

=VLOOKUP("lion", A2:B6, 2, FALSE)

For the sake of convenience, you can type the value of interest in some cell, say E1, replace
the "hardcoded" text with the cell reference, and get the formula to look up any value you
input in E1:

=VLOOKUP(E1, A2:B6, 2, FALSE)

Does anything remain unclear? Then try looking at it this way:

https://www.ablebits.com/office-addins-blog/2014/07/17/excel-vlookup-tutorial-beginner/
When using VLOOKUP formulas in real-life worksheets, the main rule of thumb is this: lock
table array with absolute cell references (like $A$2:$C$11) to prevent it from changing when
copying a formula to other cells.

The lookup value in most cases should be a relative reference (like E2) or you can lock only
the column coordinate ($E2). When the formula gets copied down the column, the reference
will adjust automatically for each row.

To see how it works in practice, please consider the following example. To our sample table,
we have added one more column that ranks the animals by speed (column A) and want to
find the 1st, 5th and 10th fastest sprinter in the world. For this, enter the lookup ranks in some
cells (E2:E4 in the screenshot below), and use the following formulas:

To pull the animal names from column B:

=VLOOKUP($E2, $A$2:$C$11, 2, FALSE)

To extract speed from column C:

=VLOOKUP($E2, $A$2:$C$11, 3, FALSE)

Enter the above formulas in cells F2 and G2, select those cells, and drag the formulas to the
below rows:

https://www.ablebits.com/office-addins-blog/2014/07/17/excel-vlookup-tutorial-beginner/
If you investigate the formula in a lower row, you will notice that the lookup value reference
has adjusted for that specific row, while the table array is locked:

Below, you will have a few more useful tips that will save you a lot of headache and
troubleshooting time.

Excel VLOOKUP - 5 things to remember!

1. The VLOOKUP function cannot look at its left. It always searches in the leftmost
column of the table array and returns a value from a column to the right. If you
need to pull values from left, use the INDEX MATCH combination that can does
not care about the positioning of the lookup and return columns.

2. The VLOOKUP function is case-insensitive, meaning that uppercase and

lowercase characters are treated as equivalent. To distinguish the letter case,
use case sensitive VLOOKUP formulas.

3. Remember about the importance of the last parameter. Use TRUE for
approximate match and FALSE for exact match.

4. When searching for approximate match, make sure the data in the lookup column
is sorted in ascending order.

5. If the lookup value is not found, a #N/A error is returned.

https://www.ablebits.com/office-addins-blog/2014/07/17/excel-vlookup-tutorial-beginner/
Excel VLOOKUP examples

How to VLOOKUP from another sheet in Excel

In practice, the Excel VLOOKUP function is rarely used with data in the same worksheet. Most
often you will have to pull matching data from a different worksheet.

To VLOOKUP from a different Excel sheet, put the worksheet's name followed by an
exclamation mark in the table_array argument before the range reference. For example, to
search in the range A2:B10 on Sheet2, use this formula:

=VLOOKUP("Product1", Sheet2!A2:B10, 2)

Of course, you don't have to type the sheet's name manually. Simply, start typing the formula
and when it comes to the table_array argument, switch to the lookup worksheet and select
the range using the mouse.

For instance, this is how you can look up the A2 value in the range A2:A9 on
the Prices worksheet and return a matching value from column C:

=VLOOKUP(A2, Prices!$A$2:$C$9, 3, FALSE)

Notes:

• If the spreadsheet name contains spaces or non-alphabetical characters, it must

be enclosed in single quotation marks, e.g. 'Price list'!$A$2:$C$9.
• In case you use a VLOOKUP formula for multiple cells, remember to lock
table_array with the $ sign, like $A$2:$C$9.

https://www.ablebits.com/office-addins-blog/2014/07/17/excel-vlookup-tutorial-beginner/
How to VLOOKUP from another workbook in Excel

To VLOOKUP from a different Excel workbook, put the workbook's name enclosed in
square brackets before the worksheet's name.

For example, here's the formula to look up the A2 value on the sheet named Prices in
the Price_List.xlsx workbook:

=VLOOKUP(A2, [Price_List.xlsx]Prices!$A$2:$C$9, 3, FALSE)

If either a workbook name or worksheet name contains spaces or non-alphabetical

characters, you should enclose them in single quotes like this:

=VLOOKUP(A2, '[Price List.xlsx]Prices'!$A$2:$C$9, 3, FALSE)

The easiest way to make a VLOOKUP formula that refers to a different workbook is this:

1. Open both files.

2. Start typing your formula, switch to the other workbook, and select the table array
using the mouse.

3. Enter the remaining arguments and press the Enter key to complete your formula.

The result will look somewhat like the screenshot below:

https://www.ablebits.com/office-addins-blog/2014/07/17/excel-vlookup-tutorial-beginner/
Once you close the file with your lookup table, the VLOOKUP formula will continue
working, but it will now display the full path for the closed workbook:

How to VLOOKUP from a named range in another sheet

In case you plan to use the same lookup range in many formulas, you can create a named
range for it and type the name directly in the table_array argument.

To create a named range, simply select the cells and type the name you want in
the Name box to the left of the Formula bar.

For this example, we gave the name Prices_2020 to the data cells (A2:C9) in the lookup sheet
and get this compact formula:

=VLOOKUP(A2, Prices_2020, 3, FALSE)

https://www.ablebits.com/office-addins-blog/2014/07/17/excel-vlookup-tutorial-beginner/
Most names in Excel apply to the entire workbook, so you don't need to specify the
worksheet's name when using named ranges.

If the named range is in another workbook, put the workbook's name before the range
name, for example:

=VLOOKUP(A2, 'Price List.xlsx'!Prices_2020, 3, FALSE)

Such formulas are far more understandable, aren't they? Besides, using named ranges can
be a good alternative to absolute references. Since a named range doesn't change, you can
be sure that your table array will remain locked no matter where the formula is moved or
copied.

If you have converted your lookup range into a fully-functional Excel table, then you can do
a VLOOKUP based on the table name, e.g. Price_table in the below formula:

=VLOOKUP(A2, Price_table, 3, FALSE)

Table references, also called structured references, are resilient and immune to many data
manipulations. For instance, you can remove or add new rows to your lookup table without
worrying about updating the references.

Using wildcards in VLOOKUP formula

Like many other formulas, the Excel VLOOKUP function accepts the following wildcard
characters:

• Question mark (?) to match any single character.

• Asterisk (*) to match any sequence of characters.

Wildcards prove really useful in many situations:

• When you do not remember the exact text you are looking for.
• When you are looking for a text string that is part of the cell contents.
• When a lookup column contains leading or trailing spaces. In that case, you may rack
your brain trying to figure out why a normal formula does not work.

Example 1. Look up text starting or ending with certain characters

Suppose you want to find a certain customer in the below database. You do not remember
the surname, but you are confident that it starts with "ack".

To return the last name from column A, use the following VLOOKUP wildcard formula:

https://www.ablebits.com/office-addins-blog/2014/07/17/excel-vlookup-tutorial-beginner/
=VLOOKUP("ack*", $A$2:$B$10, 1, FALSE)

To retrieve the license key from column B, use this one (the difference is only in the column
index number):

=VLOOKUP("ack*", $A$2:$B$10, 2, FALSE)

You can also enter the known part of the name in some cell, say E1, and combine the wildcard
character with the cell reference:

=VLOOKUP(E1&"*", $A$2:$B$10, 1, FALSE)

The below screenshot shows the results:

Below are a few more VLOOKUP formulas with wildcards.

Find the last name ending with "son":

=VLOOKUP("*son", $A$2:$B$10, 1, FALSE)

Get the name that starts with "joh" and ends with "son":

=VLOOKUP("joh*son", $A$2:$B$10, 1, FALSE)

Pull a 5-character last name:

=VLOOKUP("?????", $A$2:$B$10, 1, FALSE)

Example 2. VLOOKUP wildcard based on cell value

https://www.ablebits.com/office-addins-blog/2014/07/17/excel-vlookup-tutorial-beginner/
From the previous example, you already know that it is possible to concatenate an
ampersand (&) and a cell reference to make a lookup string. To find a value that contains a
given character(s) in any position, put an ampersand before and after the cell reference.

Let's say, you wish to get a name corresponding to a certain license key, but you don't know
the whole key, only a few characters. With the keys in column A, names in column B, and part
of the target key in E1, you can do a wildcard Vlookup in this way:

Extract the key:

=VLOOKUP("*"&E1&"*", $A$2:$B$10, 1, FALSE)

Extract the name:

=VLOOKUP("*"&E1&"*", $A$2:$B$10, 2, FALSE)

Notes:

• For a wildcard VLOOKUP formula to work correctly, use an exact match (FALSE is the
last argument).
• If more than one match is found, the first one is returned.

VLOOKUP TRUE vs FALSE

And now, it's time to take a closer look at the last argument of the Excel VLOOKUP function.
Though optional, the range_lookup parameter is highly important. Depending on whether
you choose TRUE or FALSE, your formula may yield different results.

Excel VLOOKUP exact match (FALSE)

https://www.ablebits.com/office-addins-blog/2014/07/17/excel-vlookup-tutorial-beginner/
If range_lookup is set to FALSE, a VLOOKUP formula searches for a value that is exactly equal
to the lookup value. If two or more matches are found, the 1st one is returned. If an exact
match is not found, the #N/A error occurs.

Excel VLOOKUP approximate match (TRUE)

If range_lookup is set to TRUE or omitted (default), the formula looks up the closest match.
More precisely, it searches for an exact match first, and if an exact match is not found, looks
for the next largest value that is less than the lookup value.

An approximate match VLOOKUP works with the following caveats:

• The lookup column must be sorted in ascending order, from smallest to largest,
otherwise a correct value may not be found.
• If the lookup value is smaller than the smallest value in the lookup array, a #N/A error
is returned.

The following examples will help you better understand the difference between an exact
match and approximate match VLOOKUP and when each formula is best to be used.

Example 1. How to do an exact match VLOOKUP

To look up an exact match, just put FALSE in the last argument.

For this example, let's take the animal speed table, swap the columns, and try to find the
animals that can run 80, 50 and 30 miles per hour. With the lookup values in D2, D3 and
D4, enter the below formula in E2, and then copy it down to two more cells:

=VLOOKUP(D2, $A$2:$B$12, 2, FALSE)

As you can see, the formula returns "Lion" in E3 because they run exactly 50 per hour. For
the other two lookup values an exact match is not found, and #N/A errors appear.

https://www.ablebits.com/office-addins-blog/2014/07/17/excel-vlookup-tutorial-beginner/
Example 2. How to VLOOKUP for approximate match

To look up an approximate match, there are two essential things you need to do:

• Sort the first column of table_array from smallest to largest.

• Use TRUE for the range_lookup argument or omit it.

Sorting the lookup column is very important because the VLOOKUP function stops searching
as soon as it finds a close match smaller than the lookup value. If the data is not sorted
properly, you may end up having really strange results or a bunch of #N/A errors.

For our sample data, an approximate match Vlookup formula goes as follows:

=VLOOKUP(D2, $A$2:$B$12, 2, TRUE)

And returns the following results:

• For a lookup value of "80", "Cheetah" is returned because its speed (70) is the
closest match that is smaller than the lookup value.
• For a lookup value of "50", an exact match is returned (Lion).
• For a lookup value of "30", a #N/A error is returned because the lookup value is less
than the smallest value in the lookup column.

https://www.ablebits.com/office-addins-blog/2014/07/17/excel-vlookup-tutorial-beginner/
VLOOKUP Wizard - easy way to write complex formulas

The interactive VLOOKUP Wizard will walk you through the configuration options to build a
perfect formula for the criteria you specify. Depending on your data structure, it will use the
standard VLOOKUP function or an INDEX MATCH formula that can pull values from left.

To get your custom-tailored formula, this is what you need to do:

1. Run the VLOOKUP Wizard.

1. Choose your main table and lookup table.

2. Specify the following columns (in many cases they are picked automatically):

o Key column - the column in your main table containing the values to look
up.
o Lookup column - the column to look up against.
o Return column - the column from which to retrieve values.
3. Click the Insert button.

The following examples show the wizard in action.

Standard VLOOKUP

When the lookup column (Animal) is the leftmost column in the lookup table, a normal
VLOOKUP formula for exact match is inserted:

https://www.ablebits.com/office-addins-blog/2014/07/17/excel-vlookup-tutorial-beginner/
VLOOKUP to the left

When the lookup column (Animal) is on the right side of the return column (Speed), the
wizard inserts an INDEX MATCH formula to VLOOKUP right to left:

https://www.ablebits.com/office-addins-blog/2014/07/17/excel-vlookup-tutorial-beginner/
Extra bonus! Due to the clever use of cells references, the formulas can be copied or moved
to any column, without you having to update the references.

Merge Two Tables - formula-free alternative to Excel VLOOKUP

If your Excel files are enormously large and complex, the project's deadline is imminent, and
you are looking for someone who can lend you a helping hand, try out the Merge Tables
Wizard.

This tool is our visual and stress-free alternative to Excel's VLOOKUP function, which works
this way:

https://www.ablebits.com/office-addins-blog/2014/07/17/excel-vlookup-tutorial-beginner/
 1. Select your main table.

2. Select the lookup table.

3. Choose one or several common columns as the unique identifier(s).

4. Specify which columns to update.

5. Optionally, choose the columns to add.

6. Allow the Merge Tables Wizard a few seconds for processing… and enjoy the
results :)

https://www.ablebits.com/office-addins-blog/2014/07/17/excel-vlookup-tutorial-beginner/