assignment for vlookup

VLOOKUP Practice Example File [With Solutions]

What Is Vlookup?

Vlookup stands for Vertical Lookup and is part of a group of functions that I like to call “Lookup Functions”.  A Lookup Function’s sole purpose is to pull in information from a table of data based on a unique identifier (I will refer to these as “IDs”).  

It’s like telling your dog to find your yellow ball ( Lookup Value ID ) and that it’s somewhere in the backyard ( Table Array ).  Okay, give your dog a little more help because you have a REALLY BIG backyard!  

You then specify it’s on the left side of the backyard ( Column Reference ) and that he has to bring back the exact ball you asked for ( Range Lookup ).  

Kind of make sense? For a simple spreadsheet-based example let’s look at the data below:

In  Table 1  we have a data table with an Employee ID, Last Name, & First name field.  Let’s say that we received  Table 2  from the Payroll department and we need to fill in the last name associated with the Employees ID. 

With the small amount of data in this example, you probably could get away with manually looking up each person’s ID number and copying/pasting their last name into  Table 2 , however, there would be two things that would prevent you from wanting to do this:

• The solution is manual and therefore time-consuming
• You are keying in data by hand which should always be avoided if possible

Plus, what if you had to fill in the last names of a thousand employees?  This would turn into an all-day exercise!  Luckily we can use an Excel Lookup function to do this search for us.  Watch the below animation to see how quickly I can use the VLOOKUP function to pull in the data I want.

VLOOKUP Function Inputs

There are 3 required and 1 optional input in the VLOOKUP function. The below table describes all four inputs.

Possible Errors That Can Occur

There are 3 different errors that can occur if your VLOOKUP function cannot find a match or is set up improperly.

• #REF! – If your function’s Col_Index_Num is larger than the number of columns in your Table_Array , your VLOOKUP function will return a #REF! error.
• #VALUE! – If your function’s  Col_Index_Num  is less than 1, your VLOOKUP function will return a #VALUE! error.
• #N/A – If you input FALSE (or 0) for your Range_Lookup parameter and no exact match can be found, your VLOOKUP function will return a #N/A error. You can hand this by wrapping an IFERROR function around your VLOOKUP function.

VLOOKUP Function Practice Examples

Here is an Excel file you can download to see ways you can apply the VLOOKUP Function in your spreadsheets!

There are both working tabs and solution tabs provided within the Excel file so you can reference the answers if you can’t solve the task on the first try.

Example 1: Add First/Last Name From Another Table

In this practice example, you are asked to add the first and last name of the employee to a Pay Report . You will need to use the Employee ID to Vlookup the name columns from another table so you don’t have to manually type out all the names.

Example 2: Vlookup From Multiple Tables

In this practice example, you will need to reference two separate data table sources in order to complete the requested Pay Report .

Example 3: Vlookup Using Approximate Matching

In this practice example, you will need to utilized VLOOKUP’s approximate match capability to categorize each employee to their proper payroll pay band based on the amount they are being paid.

Why You Should Learn VLOOKUP

I currently sit right across from the Human Resources department and I always find myself listening in on the questions that our hiring managers ask prospective hires over the phone.  Over time I recognized that certain questions were always asked to size up abilities pertaining to their analytic abilities.  I found it really fascinating that computer skills (especially Excel) could be analyzed by one very simple question: Do you have experience using VLOOKUP? 

VLOOKUP seems to be that one function that basic users (including myself at one point) have never heard of and that even recognizing the function’s name puts you into a category of an “experienced Excel user”.  While I can agree that VLOOKUP is an essential function to know, I probably would not categorize all people who know how to use it as experienced users, but it is definitely a stepping-stone towards becoming one.

Understanding how to use VLOOKUP was the pinnacle moment in my Excel experience where I realized that there was way more to spreadsheets than adding and multiplying numbers.  Hopefully, the information on this page has helped you get a grasp of what Vlookup is and how it can be used.

Other Lookup Functions To Learn

Microsoft Excel has additional Lookup functions that you can use within your spreadsheets. Each function has its own pros and cons.

• XLOOKUP (New!)
• INDEX/MATCH

XLOOKUP was released in 2020 and was created to be an all-in-one solution for lookup needs. This is the function I would recommend you learn next as it has the most diversity in its use cases.

You can learn more about this function in my dedicated XLOOKUP guide which will teach you everything you need to know.

Additional Vlookup Resources

• The Vlookup Formula: Why Your Employer Wants you to Know How to Use It
• VLOOKUP Explained in Simple Terms at Starbucks

Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!

Keep Learning

List of the 50 States, Abbreviations, & Capitals

Why I Made This List of 50 US States… I grew tired of searching for a well-formatted web page where...

Use Power Query and Excel At The Same Time

Why Can’t I Use Excel While In The Power Query Editor? For some insane reason, Microsoft decided it was necessary...

TSG HQ – My Home Office Setup

Today I’m going to give you behind-the-scenes access to all the equipment I use here at the TSG headquarters (aka...

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.

Excel VLOOKUP Function

The VLOOKUP Function (which stands for "vertical lookup") is one of the most useful functions in Excel, and is a fast way to look up a value when your information is arranged in a table with rows and columns. The VLOOKUP function has four arguments (values in the function separated by commas) and because of this people often think VLOOKUP is complex. Once you try a few practice problems with VLOOKUP you'll see how simple it really is.

Syntax of the VLOOKUP Function

If your data is arranged in a table with rows and columns, the VLOOKUP function will tell you a value in one row if you know a different, corresponding value to the left in that same row (the VLOOKUP function only looks from left to right). For the VLOOKUP example below, say you have a table with columns for employee ID, hire date, and employee name in your Excel spreadsheet, as shown in the image below. Each row represents a different employee. Say you want to retrieve the name of the employee with ID 1234 using VLOOKUP.

=VLOOKUP(lookup_value, lookup_range, column_number, [approximate_match])

• Lookup_value: This is the value that you know, which you will use to locate the value that you do not know. In this example it is the employee ID, which is 1234
• Lookup_range: This is the entire table, also called the lookup table or table array, that we are searching. We could say A1:C6. If this was a long list we could use the entire columns A:C
• Column_number: The columns in your lookup_range table are numbered from left to right, starting with 1. The VLOOKUP function will locate the lookup_value in column 1 (far left) and return the value in the same row but in the column specified by column_number. The employee ID column is 1, the hire date column is 2, and the employee name column is 3. Note that VLOOKUP only searches from left (starting in column 1) to right. Because we want VLOOKUP to return the employee's name, we will specify 3 for column_number.
• Approximate_match: This is the only optional argument, though it's recommended that you specify it as false (it defaults to true) when you know the lookup_value. In our case we can say false or 0; we want an exact match on 1234. The default value is true, so if you leave this blank Excel will tell VLOOKUP will look for approximate matches, which we typically don't want except for special cases.

So, in order to use the VLOOKUP function in Excel to find the name of the employee whose ID is 1234, you could use the formula =VLOOKUP(1234, A1:C6, 3, false) . See image below for details.

Note that the value you know (the first argument) must always be in the first column (on the far left side) of the range you choose.

VLOOKUP Practice Exercises

Suppose we are tracking employee names and information in Excel, but our columns are now in a different order with Employee name, employee ID, and hire date in the first column, second column, and third column, respectively. The following are VLOOKUP examples with the new, rearranged lookup table.

Use VLOOKUP to Find Will's Employee ID

The value we know in the first column is "Will Ferrell" and our lookup range can be A:B, A:C, or A1:B6, etc. Employee name is column 1 in our lookup range, and we want Employee ID so we will specify 2 for column number. Finally, we know exactly how to spell our lookup_value (Will Ferrell) so we will specify false for approximate_match.

=VLOOKUP("Will Ferrell", A:B, 2, false)

This VLOOKUP formula will return the value 2323, which is in column 2 of our table array in the same row as the lookup value.

Use VLOOKUP to Find the Hire Date of the Employee With ID 1234

This example is a little trickier because Employee ID will be our lookup value instead of Employee Name. Recall that the lookup value needs to be in the first column of our lookup table, so we will need to specify B:C or B1:C6 as our lookup range.

=VLOOKUP(1234, B:C, 2, false)

This VLOOKUP formula will search column B from the top down until it finds 1234, then will look in column 2 (column C) of the lookup table in the same row to return a value of 12/10/2005.

VLOOKUP With Wildcard Matching

Excel lets you use the VLOOKUP function with wildcards. Note that this only works in exact matching mode, so your fourth argument (approximate_match) must be 0 or FALSE. Remember, you can't omit this argument because Excel defaults approximate_match to true. Also note that this only works for matching strings of text.

What is Wildcard Matching in Excel?

Wildcard matching in Excel is the ability to use characters which represent anything in a string of text. For example, say you know someone's first name but not their last name. You could find this person in an Excel spreadsheet by concatenating their first name with a wildcard character.

A question mark (?) replaces exactly one character in a string of text in Excel.

An asterisk (*) replaces any number of characters in a string of text in Excel.

To use a wildcard in Excel, you concatenate an asterisk enclosed in quotes at the beginning or end (or both) of your string of text. It looks like this:

=VLOOKUP("My text"&"*", A:C, 2, FALSE)

The VLOOKUP function will then return the first value it finds where the lookup_value is text that begins with "My text". If our lookup_value was instead "*"&"my text" , then it would search for any text that ends with "my text" because the asterisk comes first.

Say we want to find the ID number of the employee whose first name is "Amy". We could use the wildcard match as follows:

=VLOOKUP("Amy"&"*", A:B, 2, FALSE)

This will return the ID number of the first employee in the table whose name starts with the text "Amy". Now suppose you want to find the ID of the employee whose middle initial is C. You can use wildcard at the beginning and end of the search string as follows:

=VLOOKUP("*"&"C."&"*", A:B, 2, FALSE)

This will return the ID of the first employee who has "C." in their name.

Exact Match vs. Approximate Match

Excel lets you use VLOOKUP to search for an exact match or an approximate match, as determined by the fourth argument, which is optional. VLOOKUP defaults to approximate match, so if you omit the fourth argument, or if you include it as TRUE or 1, then VLOOKUP will use an approximate match.

If you include the fourth argument as 0 or FALSE, then VLOOKUP will use an exact match.

Exact matching makes sense when you're searching for an exact value, like Employee ID number in the examples above. If you don't match on the exact ID number, you may get the wrong employee!

If you're not sure whether the exact value appears in the list, you can use approximate match to find the value that matches best. If Excel does find an exact match, then it will match on that value. If it does not find an exact match, it will find the closest value to match.

If VLOOKUP is using an approximate match it will use an exact match if the exact value is available. If not, it will use the next smallest value, so if you're using an approximate match in Excel you need to make sure the first column is sorted from small to large, or alphabetically.

VLOOKUP Errors

The VLOOKUP function in Excel is prone to errors because it has so many required arguments, and if any part of the formula is written incorrectly it will result in an error. Unfortunately, the error messages that Excel provides are not very helpful in diagnosing the problem. Read on to learn more about the different error messages you might see while writing a VLOOKUP formula in Excel and how to correct them.

VLOOKUP Function is Returning the #N/A! Error

The VLOOKUP function returns the #N/A! error when it is looking for an exact match (approximate_match is false) and it cannot locate the lookup_value. To fix this error, ensure that the lookup_value is spelled correctly in both the formula and the lookup table, and double check the defined lookup_range. Ensure that the lookup_value is in the left-most column of the lookup_range. Also ensure that the lookup_value is the same format in both the formula and in the lookup table; if a number is formatted as text in one of these places the VLOOKUP function will not consider them a match.

VLOOKUP Function is Returning the #REF! Error

The VLOOKUP function in Excel returns the #REF! error if the formula references a column that is not included in the lookup_range. To fix this issue, double check the lookup_range and count the number of columns included in the lookup table. Then double check the column_number argument (the 3rd argument of a VLOOKUP formula) and ensure that the number is not greater than the total number of columns in the lookup table. The column_number argument can be any number from 1 to the total number of columns in the lookup table.

VLOOKUP Function is Returning the #VALUE! Error

The VLOOKUP function returns the #VALUE! error if the column_number or approximate_match arguments are invalid values. To fix this issue, first double check the column_number argument and ensure that it is a number (greater than 0) and is not text, a negative number, or anything else. Next, double check the approximate_match argument and ensure that it is either omitted or is a valid boolean (true or false) value. It must either be the words "true" or "false" (without the quotation marks). Alternatively, in Excel, boolean values can be 1 for true and 0 for false. Any other values will be considered invalid.

VLOOKUP Function is Returning a Value I Do Not Expect

Is the VLOOKUP function returning a value you're not expecting, and you're not sure why? We've all been there. The first thing to check is that the fourth and final argument is set to false, provided that you know the exact value of the lookup_value. If the approximate_match argument is true or if it is left out of the formula, it will default to true and VLOOKUP will look for an approximate match, which can cause all kinds of unexpected problems.

The second thing to check is whether there are multiple instances of the first argument (the lookup_value) in the first column of the lookup table. If the lookup_value is listed more than once, the VLOOKUP function will simply match with the first one that it finds, which is whichever row is highest in the lookup table since VLOOKUP in Excel searches from top to bottom. So if VLOOKUP is returning an unexpected result but from the correct column, make sure that the lookup_value doesn't appear again higher up in the lookup table.

While you're checking for a second instance of the lookup_value, also note that when VLOOKUP matches with the lookup_value, it is NOT case sensitive. This means that, to the VLOOKUP function in Excel, "robert de niro" is the same as "Robert De Niro" amd will treat them the same. So when you're checking that the lookup_value only exists once in the table, also check for the same value with different capitalization.

Continue to VLOOKUP practice exercises! →

#1 Excel tutorial on the net

Exact Match | Approximate Match | Vlookup Looks Right | First Match | Partial Match | Vlookup is Case-insensitive | Multiple Criteria | #N/A error | Multiple Lookup Tables | Index and Match | Table Magic | Xlookup

The VLOOKUP function is one of the most popular functions in Excel . This page contains many easy to follow VLOOKUP examples.

Exact Match

Most of the time you are looking for an exact match when you use the VLOOKUP function in Excel. Let's take a look at the arguments of the VLOOKUP function.

1. The VLOOKUP function below looks up the value 53 (first argument) in the leftmost column of the red table (second argument).

2. The value 4 (third argument) tells the VLOOKUP function to return the value in the same row from the fourth column of the red table.

Note: the Boolean FALSE (fourth argument) tells the VLOOKUP function to return an exact match. If the VLOOKUP function cannot find the value 53 in the first column, it will return a #N/A error .

3. Here's another example. Instead of returning the salary, the VLOOKUP function below returns the last name (third argument is set to 3) of ID 79.

Approximate Match

Let's take a look at an example of the VLOOKUP function in approximate match mode (fourth argument set to TRUE).

1. The VLOOKUP function below looks up the value 85 (first argument) in the leftmost column of the red table (second argument). There's just one problem. There's no value 85 in the first column.

2. Fortunately, the Boolean TRUE (fourth argument) tells the VLOOKUP function to return an approximate match. If the VLOOKUP function cannot find the value 85 in the first column, it will return the largest value smaller than 85. In this example, this will be the value 80.

3. The value 2 (third argument) tells the VLOOKUP function to return the value in the same row from the second column of the red table.

Note: always sort the leftmost column of the red table in ascending order if you use the VLOOKUP function in approximate match mode (fourth argument set to TRUE).

Vlookup Looks Right

The VLOOKUP function always looks up a value in the leftmost column of a table and returns the corresponding value from a column to the right .

1. For example, the VLOOKUP function below looks up the first name and returns the last name.

2. If you change the column index number (third argument) to 3, the VLOOKUP function looks up the first name and returns the salary.

Note: in this example, the VLOOKUP function cannot lookup the first name and return the ID. The VLOOKUP function only looks to the right. No worries, you can use INDEX and MATCH in Excel to perform a left lookup .

First Match

If the leftmost column of the table contains duplicates, the VLOOKUP function matches the first instance. For example, take a look at the VLOOKUP function below.

Explanation: the VLOOKUP function returns the salary of Mia Clark, not Mia Reed.

Partial Match

The VLOOKUP function supports wildcards for partial matches. For example, take a look at the VLOOKUP function below.

Explanation: an asterisk (*) matches zero or more characters. The VLOOKUP function matches the first instance that begins with "Jess". Use "*"&G2 for an "ends with" match. Use "*"&G2&"*" for a "contains" match.

Vlookup is Case-insensitive

The VLOOKUP function in Excel performs a case-insensitive lookup. For example, the VLOOKUP function below looks up MIA (cell G2) in the leftmost column of the table.

Explanation: the VLOOKUP function is case-insensitive so it looks up MIA or Mia or mia or miA, etc. As a result, the VLOOKUP function returns the salary of Mia Clark (first instance). Use INDEX, MATCH and EXACT in Excel to perform a case-sensitive lookup .

Multiple Criteria

Do you want to look up a value based on multiple criteria? Use INDEX and MATCH in Excel to perform a two-column lookup .

Note: the array formula above looks up the salary of James Clark, not James Smith, not James Anderson.

If the VLOOKUP function cannot find a match, it returns a #N/A error.

1. For example, the VLOOKUP function below cannot find the value 28 in the leftmost column.

2. If you like, you can use the IFNA function to replace the #N/A error with a friendly message.

Note: the IFNA function was introduced in Excel 2013. If you're using Excel 2010 or Excel 2007, combine IF and ISNA or use IFERROR.

Multiple Lookup Tables

When using the VLOOKUP function in Excel, you can have multiple lookup tables. You can use the IF function to check whether a condition is met, and return one lookup table if TRUE and another lookup table if FALSE.

1. Create two named ranges : Table1 and Table2.

2. Select cell E4 and enter the VLOOKUP function shown below.

Explanation: the bonus depends on the market (UK or USA) and the sales amount. The second argument of the VLOOKUP function does the trick. If UK, the VLOOKUP function uses Table1, if USA, the VLOOKUP function uses Table2. Set the fourth argument of the VLOOKUP function to TRUE to return an approximate match.

3. Press Enter.

4. Select cell E4, click on the lower right corner of cell E4 and drag it down to cell E10.

Note: for example, Walker receives a bonus of $1,500. Because we're using named ranges, we can easily copy this VLOOKUP function to the other cells without worrying about cell references.

• Index and Match

Instead of using VLOOKUP, use INDEX and MATCH . To perform advanced lookups, you'll need INDEX and MATCH. Maybe this is one step too far for you at this stage, but it shows you one of the many other powerful formulas Excel has to offer.

Table Magic

Tables can make VLOOKUP formulas a lot easier. In the example below, a single VLOOKUP function automatically looks up all country codes.

Note: visit our page about merging tables to learn how to create this VLOOKUP function and bring your Excel game to a new level.

If you have Excel 365 or Excel 2021, use XLOOKUP instead of VLOOKUP. The XLOOKUP function is easier to use and has some additional advantages.

• Lookup & Reference Functions

Learn more, it's easy

• Two-way Lookup
• Case-sensitive Lookup
• Left Lookup
• Locate Maximum Value
• Two-column Lookup
• Closest Match
• Compare Two Columns

Download Excel File

• vlookup.xlsx

Next Chapter

• Financial Functions

Follow Excel Easy

Become an Excel Pro

• 300 Examples

Most Popular

• Pivot Tables
• Formulas and Functions
• Conditional Formatting
• Find Duplicates
• Drop-down List

Vlookup • © 2010-2023 Popular Excel Topics: Pivot Tables • Vlookup • Formulas • Charts • Conditional Formatting

Excel VLOOKUP Made Easy – Learn Basics with Practice File & Video

Have you ever found yourself lost in a sea of data spread across multiple columns and worksheets in Excel? Worse, you need a way to connect items. In this Microsoft Excel VLOOKUP tutorial, you’ll learn how to use VLOOKUP using two examples with different match types. ( Includes free downloadable Excel VLOOKUP practice sheets and video tutorial. )

When I first heard about this powerful Excel function in 2005, I looked at the help file and syntax. I then rolled my eyes.

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

It didn’t look easy, and I couldn’t see the immediate benefit. But sometimes, pushing through difficulties can make things easier in the long run. I’m glad I did.

If you’re using Google Sheets, please see Using Google Sheets & VLOOKUP .

I like to start with an easy example when learning Microsoft Excel formulas and functions. This VLOOKUP tutorial will provide two examples using different function arguments and lookup values.

• Example 1 uses one worksheet with an Approximate match. You’ll be referencing a table on the existing sheet.
• Example 2 uses two worksheets with an Exact match. You’ll be referencing data from another worksheet.

What is Excel VLOOKUP Function?

VLOOKUP is an Excel function that allows you to search and retrieve a cell’s content from one column and use it in another location to retrieve data. As you might guess, the “V” stands for vertical and relies on looking up data from a lookup table column.

This lookup column could be on the same worksheet you’re viewing or another within your workbook. The function requires a common field or key and four arguments . In addition, the function allows you to specify whether to use an exact match or an approximate match .

Why Use a VLOOKUP Formula?

Let’s put these terms in context and give an example of how the function might be used. It’s similar to one of the practice exercises. When I was working on our local elections, the county provided a massive file with the data. Each worksheet contained info I might use.

The problem is I had numerous worksheets in this workbook. I didn’t want to switch between each sheet. That’s not efficient. Moreover, I wanted to do some of my own calculations using other Excel formulas.

The solution was to find a common denominator or key between these worksheets. Using this common key, I can create a new worksheet and pull the needed columns from each worksheet using the VLOOKUP function . Again, Microsoft Excel does the heavy lifting. This allows me to concentrate on one worksheet with the data I need.

You might have your own examples, such as retrieving data using a shopkeeping unit (SKU) or ISBN if you deal with books, part numbers, etc.

VLOOKUP in Action: Approximate Match Example

The example outlined here is found on the Example 1 worksheet , which can be downloaded at the end of the tutorial. All the names are fictitious.

One cell reference contains the voter’s birth date. However, I didn’t want the voter’s birth date to show on the final distributed files. But, I did want to do some age analysis.

Instead, I created a segment based on age ranges and a lookup formula. Excel would do a vertical lookup that returns the matching value from one column to the desired cell. For example, rather than showing a voter was 28, I would define them as “Young.”

Let’s refer to the screenshot above with my first voter, Sophia Collins. If you scan Column D (Age) , you’ll see she is 43 years old and in the “Mature” Segment . This is because the value of “ Mature ” in Column E was dynamically pulled using Excel’s VLOOKUP function.

The lookup table in Columns H and I with blue headings is the small Excel table. Microsoft refers to this as a table_array . This is where I’ve defined my four age segments: New , Young , Mature , and Senior .

My “Segment” works because if a voter is under 21, they are “New.” From 21-38, they are “Young.” From 39-59, they are “Mature.” And if they are 60 or older, they are “Senior.”

In Sophia’s case, Excel would take her age of 43 from cell D2 and return the closest match from Column H . Both columns D and H contain age data, which is our standard key.

When Excel found an approximate match, it would go to Column I and get the Label . The returned value of “Mature” was then copied to cell E2, the Segment .

You might notice that the lookup table doesn’t list every age. It doesn’t have to because I’m using an approximate match . I’m telling Excel to find me the closest age.

For example, the next voter Evelyn Bennett is 54, but there is no value for 54 in Column H. In this case, 54 falls between 39 and 59, so she is also labeled “Mature.”

As we stated, to use the VLOOKUP function, there needs to be a common key. In this case, it’s age. Both columns D and H contain ages. The column headings and cell contents can be different and don’t have to be an exact match.

Unpacking the Arguments of VLOOKUP

Let’s peel away some of the mystery and display how VLOOKUP shows in the Excel formula bar . In this illustration, I’ve clicked cell D2.

The term “ argument ” isn’t as complicated or negative as it sounds. If you’re familiar with the Excel formula bar, an argument is what goes in between the parentheses ( ) . It provides an input value for the Excel function.

[ A] – This represents the VLOOKUP formula in Cell E2 .

=VLOOKUP(D2,$H$2:I$5,2,TRUE)

[B] – Cell D2 is our first argument called Lookup_value .

[C] – The cell range $H2$2:$I$5 is our Table_array and the second argument.

[D] – 2 is the Col_index_num from our Table_array and the third argument. Label is the 2nd column.

[E] – TRUE is the Range_lookup and the fourth argument.

The good news is the VLOOKUP Function Arguments dialog box guides you through these elements, so you don’t need to type the long string in Excel’s formula bar.

VLOOKUP Arguments

Some functions have required arguments, while others don’t. For example, to compute the voter’s age, I also used the TODAY function =TODAY(), which doesn’t use any arguments. Some common argument examples include:

• true/false logical value

Using the formula from cell D2, here’s how these four arguments work.

1. Lookup_value – Think of this field as your starting point. In this example, I want to look up Sophia’s Age from cell D2.

2. Table_array – This is the cell range for your lookup table. This range lookup can be on your existing worksheet or another worksheet. For example, I have a small table with the age groupings and corresponding labels in this example.

3. Col_index_num – The column number on your lookup table with the necessary information. In our example, we want column 2 , which has the column heading of Label . This will be our voter’s Segment name.

When we count, we’re counting the columns on the lookup table . So even though the “ Label ” column is Column I or the 9th column, it’s the 2nd column on the lookup table. Some people call this a Column Index .

4. Range-lookup – this field defines how close a match should exist between your Lookup_value (D2) and the value in the leftmost column on our lookup table. In our case, we want an approximate match , so we’ll use “ TRUE .”

Essential Rules & Caveats for VLOOKUP’s Table Array

There are several rules to remember about this table array.

• Rule 1 – The left column must contain the values being referenced . Leftmost doesn’t mean it has to be in Column A. It’s just the leftmost or first column on the table_array. For example, the leftmost column on the lookup table above is H.
• Rule 2 – You can’t have duplicate values in the leftmost column of the lookup range. For example, I couldn’t have two entries with the value “39”, with one being “Mature” and another “39” for “Go Getter.” Excel would complain.
• Rule 3 – When referring to the lookup table, you want absolute cell references when you copy the VLOOKUP formula to other cells .

For example, if I want to use the same formula in cells E3 through E11, I don’t want my lookup cell references to shift each time I move down to the next cell. I need the cell references to be constant. This is called an absolute cell reference .

After you define your lookup range of cells, you can press F4 . This will cycle through absolute and relative cell references. You want to select the option that includes a $ before your Column and Row. You can get around this if you know how to create named ranges in Excel .

Step by Step: Adding VLOOKUP Formula

The steps below were used for Example 1 from the practice file.

• Add in the column where you’ll enter the VLOOKUP formula. In my case, I added Column E and called it Segment .
• Add in your lookup table and data. Mine is H1:I5.
• Click cell E2 .
• Click the Formulas tab from the Excel ribbon .
• Click the Insert Function button.
• From the Insert Function dialog box, type “vlookup” in the Search for a function textbox. You may also select it from the Lookup & Reference category.

• Click OK . The Function Arguments dialog box will appear with text boxes for the required arguments.

• In Lookup_value type D2 . Or, you can click the cell.
• In Table_array type $H$2:$I$5 . Note the $ signs.
• In Col_index_num type 2 .
• In Range_lookup type true .
• Your Function Arguments dialog should look like the following. Notice in the lower left; you can see the Formula result .

• Click OK . You should now see “Mature” in cell E2 .
• Click the small green square (fill handle) in the cell’s lower right corner to copy the VLOOKUP formula down the column.

You might want to use the Excel formula auditing feature if you get any error messages.

VLOOKUP Across Spreadsheets: Exact Match Example

The second scenario dealt with that same election file. This time there was an extra worksheet for political parties. The voter’s party was listed as an alphanumeric value called “Pcode” and not the political party.

This coding wasn’t intuitive. For example, “D” was for the “American Independent Party,” but some thought it meant “Democratic Party.” Another difference was we needed an exact match for the Political party.

Again, the way to solve this problem was to use the worksheet with the Pcode and translation and have Excel use the VLOOKUP function for the Party name . I could add a column called “Political Party” to my original worksheet to show the lookup table’s information.

Using the Starting VLOOKUP Practice File

• Download the practice file. The file link is at the bottom of this tutorial.
• Review Example 2 – Voters worksheet. It has the voter’s first and last names but only a Pcode.
• Review Example 2 -Party Codes worksheet. It has a listing of party codes and political names. Each of the Party Codes and Names is unique. You’ll also note that Column A is sorted in ascending order .

• Add your new column on the Voters worksheet that will display the info pulled from the Lookup table on the Party Codes worksheet. In my example, I added a column called Political Party in Column D. This is where I will insert the Excel function.

• Place your cursor in the first blank cell in that column. In my example, this is cell D2.
• Click the Formulas from the Excel ribbon .
• From the Insert Function dialog , type “ vlookup ” in the Search for a function textbox.

Defining VLOOKUP Argument Values

After you click OK , Excel’s Function Arguments dialog appears and allows you to define the four values. You’ll see that your starting cell and the formula bar show the beginning part of the function =VLOOKUP() . The Function Arguments dialog adds the needed data elements that will display between ().

For illustration purposes, I overlaid the Party Codes worksheet to show the relationships.

After entering the required arguments, my dialog looks like the example below.

You can see this in the red outlined formula bar above. I now have more information based on my Function Arguments dialog box entries . You might also note that when I clicked the Party Codes worksheet to add in my Table_array, Excel prepended the worksheet name before the cell range. However, I must return and enter my $ signs to make the cell references absolute .

The other item of interest is that Excel displays the Formula result = text line when you build these functions. This is great feedback that can show if your function is on target. In our example, Excel looked up the Pcode of “A” and returned the Political Party “Democratic.”

VLOOKUP is a powerful Excel function that can leverage spreadsheet data from other sources. There are many ways you can benefit from this function. I used a 1:1 code translation in this example, but you could also use it for group assignments. For example, you could assign state codes to a region such as CT, VT, and MA to a “New England” region.

One important note about using Excel functions and formulas is you want to be careful when deleting columns.

For example, I omitted the Age column in the final spreadsheet I distributed. After completing my VLOOKUP and getting my segments, I copied the cell values to a new Excel worksheet. If I had just deleted Column D, my Excel formula would’ve returned an error.

If you’re trying to do a horizontal lookup, you’ll be happy to learn that Excel has an HLOOKUP function . I haven’t done an HLOOKUP tutorial yet. If this interests you, let me know. However, Microsoft has released a new versatile vlookup alternative function called XLOOKUP .

Key Points & Takeaways

Before you grab the practice file below, you might want to review these points.

• VLOOKUP is an Excel function that allows you to search and retrieve a cell’s content from one column and use it in another location to retrieve data.
• The “V” in VLOOKUP stands for vertical, meaning it looks up data from a column in a lookup table.
• The lookup table used by VLOOKUP can be on the same worksheet or another worksheet within your workbook.
• The VLOOKUP function requires four arguments: the lookup value, the table array, the column index number, and the range lookup.
• VLOOKUP allows you to specify whether to use an exact match or an approximate match.
• VLOOKUP is useful when you have multiple worksheets with related data, and you want to consolidate specific data into one worksheet.
• VLOOKUP can significantly increase efficiency when dealing with large datasets by eliminating the need to manually search for and copy data across worksheets.
• If you delete a column that is referenced in a VLOOKUP formula, the formula will return an error. To avoid this, it’s recommended to copy the cell values to a new Excel worksheet after completing the VLOOKUP operation.
• It’s important to use absolute cell references when defining the table array in VLOOKUP to ensure the correct cells are referenced when the formula is copied to other cells.

Get Hands-On: VLOOKUP Practice File & Video

Hand-picked excel tutorials.

• How to Use the IF Function in Excel
• How to Use XLOOKUP in Excel
• How to Convert Word Table to Excel
• How to Split Names in Excel
• How to Copy Excel Formula Values

Practice And Learn Excel Online For Free

Vlookup function – exercise number 1.

• Post published: May 20, 2021

In this exercise you will be able to practice the VLOOKUP function!

You can view the answers in the Answer tab! 🙂

Terms and Conditions - Privacy Policy

20 Real Vlookup Exercises

Description

Additional information, reviews (3), excel vlookup exercises, description.

This Excel practice consists of more than 20 Real Excel Vlookup exercises to practice the multiple use cases of the most important function of Excel. Vlookup stands for vertical lookup and is used to search for a specific value in a table or range of cells. It is essential to practice and master the Vlookup function to ensure maximum productivity and efficiency with the spreadsheet. All Excel exercises are real, based on business cases where vlookup is a must. You will also practice other types of lookup functions  such as hlookup, reference or database in the different scenarios that tend to occur at corporate level. All Vlookup practice exercises come with a real set of data and describe a regular situation or problem that you will have to solve using the most suitable Excel function. As a result, the exercises replicate real world problems or situations that you may encounter at any position of wide range of industries. Moreover, the workload is mixed, some exercises are short, finished with just one formula, but others are rather mini projects where you will have to implement and combine several formulas to yield the expected outcome. Once you finish all the exercises, you will be more confident with Excel and gain the expertise and knowledge required to succeed at any position.

WHAT YOU WILL PRACTICE

1) Find, match and retrieving values of multiple tables and/or sheets in order to get insights about a business. Not only vlookup but also other formulas used to retrieve values from one place to another. 2) Confront the situations when vlookup does not work and learn how to fix it. 3) Common Excel tools and tricks to handle and analyze data. 4) Tools and tricks to work with large data sets (more than 60 000 rows). 5) Real applications of the following functions: vlookup, sumif, hlookup, index-match, ifna, indirect, sumproduct, offset, find… 6) Nested functions. 7) Text functions: len, wide, concat, substitute, left, lower, etc. 8) Conditional statements.

REQUIREMENTS

90% of Excel Vlookup exercises require an intermediate level of Excel. Students must have attended a complete course of Excel and feel confident with the tool. Five Excel practice questions are considered of advanced level. The spreadsheets included in the project have been done with the latest Excel version. It is then compatible with Excel 2007, Excel 2010, Excel 2013 and Excel 2016.

After purchase, you will receive a zip folder in the confirmation email. It also available on your Practity account. The file includes the next files:

• PDF with the Excel problems description.
• PDF with the solutions (57 pages). It includes detailed explanations and print screens with the  formulas required to solve each problem.
• 22 Excel files with the data required for each exercise.
• 20 Excel files with the solutions to each exercise.

Andrey Ampilogov, based in Rusia, currently works as SPSS programmer. He holds a Bachelor of Applied Science (BASc), Quantitative Economics. Andrey has held several positions as  senior analyst at major financial institutions in Rusia and the US.

• Email Address *

Specification: 20 Real Vlookup Exercises

3 reviews for 20 real vlookup exercises.

rona – 7 February, 2022

After finishing all the exercises, I feel now much more confident about this crucial function. You end up practicing vlookup in lots of different scenarios. Quite different from the regular exercises and online tutorials. I really liked to have all the data in different workbooks so you can focused on the problems rather than wasting time filling up a blank spreadsheet just to get started.

shiva – 6 October, 2023

I recently purchased these practice exercises and I must say that it has exceeded my expectations. The exercises are varied and practical, covering a wide range of scenarios where the VLOOKUP function can be applied. What’s more, the difficulty level of the exercises increases progressively, making the learning experience challenging yet rewarding. Solutions provided for each exercise are clear and well explained. They come with visual aids and examples, making them easy to understand and straightforward. Highly recommended

Xia Jones matt – 23 October, 2023

I liked a lot that the exercises are quite different and you have to solve them in a different manner. I didn´t know there were so many options when working with Vlookup. Good investment!

Only logged in customers who have purchased this product may leave a review.

Related Products

Real Excel Exercises with Solutions

Excel Exercises for Job Interview

Excel Projects for Practice

25 Real Pivot Table Exercises

Solar System Calculator

Excel Most Popular Functions Practice

Terms of Use

Shopping cart

• VLOOKUP: When and how to use it Video
• The nuts and bolts of VLOOKUP Video
• Look up values Video
• Copy a VLOOKUP formula Video

VLOOKUP: When and how to use it

The basics of using VLOOKUP.

Use VLOOKUP

In the Formula Bar , type = VLOOKUP( ) .

In the parentheses, enter your lookup value, followed by a comma. This can be an actual value, or a blank cell that will hold a value: (H2,

Enter your table array or lookup table, the range of data you want to search, and a comma: (H2,B3:F25,

Enter column index number. This is the column where you think the answers are, and it must be to the right of your lookup values: (H2,B3:F25,3,

Enter the range lookup value, either TRUE or FALSE . TRUE finds partial matches, FALSE finds exact matches. Your finished formula looks something like this: =VLOOKUP(H2,B3:F25,3,FALSE)

VLOOKUP function

Quick reference card: VLOOKUP refresher

Quick reference card: VLOOKUP troubleshooting tips

When you need to find information in a large spreadsheet, or you are always looking for the same kind of information, use the VLOOKUP function.

VLOOKUP works a lot like a phone book, where you start with the piece of data you know, like someone's name, in order to find out what you don't know, like their phone number.

So, as an example, I'll enter part numbers, the thing I know, and find out Prices, the thing I don't know.

To do that, I'll click the cell where I want to see the Prices, I'll enter an = sign, VLOOKUP , and parentheses.

These parentheses will contain a set of arguments, and an argument is just a piece of data that the function needs in order to run.

I'll enter H2 as the first argument, because that is where I'll type the part numbers.

Follow that with a comma, and then, I'll enter the range of cells that contains the data I want to search. That's this block of data here.

The part numbers start in cell B3, and if I scroll down, you can see the status values end at cell E52.

So, I'll enter B3, a colon, and E52, then I'll type another comma.

And you need to do that because the functions won't work without the colons and commas.

Next, I'll type the number 3. This tells VLOOKUP that the values I want to see are in the third column from the left in the range of cells I want to search.

In other words, it's the third column over from the part numbers, the data I know.

Another comma, and I enter FALSE , because that gives me an exact match between part number and price.

And don't worry, I'll explain how that works later.

When I press Enter to tell Excel I am done, you can see I get an error message because I haven't entered a value in cell H2.

But, when I enter a part number, I get a price.

So what just happened? I told Excel, “Here is a value in the left-hand column of my data. Now look through this range of cells, and in the third column to the right, find the value on the same row.”

A lot like a phone book.

So up next, I'll explain each of the arguments - the values inside the parentheses - along with rules for using VLOOKUP .

Need more help?

Want more options.

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Microsoft 365 subscription benefits

Microsoft 365 training

Microsoft security

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Ask the Microsoft Community

Microsoft Tech Community

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Thank you for your feedback.

10 VLOOKUP Examples For Beginner & Advanced Users

• -- By Sumit Bansal

This Tutorial Covers:

VLOOKUP Function – Introduction

VLOOKUP function is THE benchmark.

You know something in Excel if you know how to use the VLOOKUP function.

If you don’t, you better not list Excel as one of the strong areas in your resume .

I have been a part of the panel interviews where as soon as the candidate mentioned Excel as his area of expertise, the first thing asked was – you got it – the VLOOKUP function.

Now that we know how important this Excel function is, it makes sense to ace it completely to be able to proudly say – “I know a thing or two in Excel”.

This is going to be a massive VLOOKUP tutorial (by my standards).

I’ll cover everything there is to know about it, and then show you useful and practical VLOOKUP examples.

So buckle up.

It’s time for the takeoff.

When to use the VLOOKUP Function in Excel?

VLOOKUP function is best suited for situations when you are looking for a matching data point in a column, and when the matching data point is found, you go to the right in that row and fetch a value from a cell which is a specified number of columns to the right.

Let’s take a simple example here to understand when to use Vlookup in Excel.

Remember when the exam score list was out and pasted on the notice board and everyone used to go crazy finding their names and their score (at least that’s what used to happen when I was in school).

Here is how it worked:

• You go up to the notice board and start looking for your name or enrolment number (running your finger from top to bottom in the list).
• As soon as you spot your name, you move your eyes to the right of the name/enrolment number to see your scores.

And that is exactly what the Excel VLOOKUP function does for you (feel free to use this example in your next interview ).

VLOOKUP function looks for a specified value in a column (in the above example, it was your name) and when it finds the specified match, it returns a value in the same row (the marks you obtained).

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

Input Arguments

• lookup_value – this is the look-up value you are trying to find in the left-most column of a table. It could be a value, a cell reference, or a text string. In the score sheet example, this would be your name.
• table_array – this is the table array in which you are looking for the value. This could be a reference to a range of cells or a named range. In the score sheet example, this would be the entire table that contains score for everyone for every subject
• col_index – this is the column index number from which you want to fetch the matching value. In the score sheet example, if you want the scores for Math (which is the first column in a table that contains the scores), you’d look in column 1. If you want the scores for Physics, you’d look in column 2.
• [range_lookup] – here you specify whether you want an exact match or an approximate match. If omitted, it defaults to TRUE – approximate match (see additional notes below).

Additional Notes (Boring, but important to know)

• The match could be exact (FALSE or 0 in range_lookup) or approximate (TRUE or 1).
• In approximate lookup, make sure that the list is sorted in ascending order (top to bottom), or else the result could be inaccurate.
• If the VLOOKUP function can not find the value, it returns the largest value, which is less than the lookup_value.
• It returns a #N/A error if the lookup_value is smaller than the smallest value.
• If lookup_value is text, wildcard characters can be used (refer to the example below).

Now, hoping that you have a basic understanding of what the VLOOKUP function can do, let’s peel this onion and see some practical examples of the VLOOKUP function.

10 Excel VLOOKUP Examples (Basic & Advanced)

Here are 10 useful exampels of using Excel Vlookup that will show you how to use it in your day-to-day work.

Example 1 – Finding Brad’s Math Score

In the VLOOKUP example below, I have a list wth student names in the left-most column and marks in different subjects in columns B to E.

Now let’s get to work and use the VLOOKUP function for what it does best. From the above data, I need to know how much Brad scored in Math.

From the above data, I need to know how much Brad scored in Math.

Here is the VLOOKUP formula that will return Brad’s Math score:

The above formula has four arguments:

• “Brad: – this is the lookup value.
• $A$3:$E$10 – this is the range of cells in which we are looking. Remember that Excel looks for the lookup value in the left-most column. In this example, it would look for the name Brad in A3:A10 (which is the left-most column of the specified array).
• 2 – Once the function spots Brad’s name, it will go to the second column of the array, and return the value in the same row as that of Brad. The value 2 here indicated that we are looking for the score from the second column of the specified array.
• 0 – this tells the VLOOKUP function to only look for exact matches.

Here is how the VLOOKUP formula works in the above example.

First, it looks for the value Brad in the left-most column. It goes from top to bottom and finds the value in cell A6.

As soon as it finds the value, it goes to the right in the second column and fetches the value in it.

You can use the same formula construct to get anyone’s marks in any of the subjects.

For example, to find Maria’s marks in Chemistry, use the following VLOOKUP formula:

In the above example, the lookup value (student’s name) is entered in double quotes. You can also use a cell reference that contains the lookup value.

The benefit of using a cell reference is that it makes the formula dynamic.

For example, if you have a cell with a student’s name, and you are fetching the score for Math, the result would automatically update when you change the student’s name (as shown below):

If you enter a lookup value that is not found in the left-most column, it returns a #N/A error.

Example 2 – Two-Way Lookup

In Example 1 above, we hard-coded the column value. Hence, the formula would always return the score for Math as we have used 2 as the column index number.

But what if you want to make both the VLOOKUP value and the column index number dynamic. For example, as shown below, you can change either the student name or the subject name, and the VLOOKUP formula fetches the correct score. This is an example of a two-way VLOOKUP formula.

This is an example of a two-way VLOOKUP function.

To make this two-way lookup formula, you need to make the column dynamic as well. So when a user changes the subject, the formula automatically picks the correct column (2 in the case of Math, 3 in the case of Physics, as so on..).

To do this, you need to use the MATCH function  as the column argument.

Here is the VLOOKUP formula that will do this:

The above formula uses MATCH(H3,$A$2:$E$2,0) as the column number. MATCH function takes the subject name as the lookup value (in H3) and returns its position in A2:E2. Hence, if you use Math, it would return 2 as Math is found in B2 (which is the second cell in the specified array range).

Example 3 – Using Drop Down Lists as Lookup Values

In the above example, we have to manually enter the data. That could be time-consuming and error-prone, especially if you have a huge list of lookup values.

A good idea in such cases is to create a drop-down list of the lookup values (in this case, it could be student names and subjects) and then simply choose from the list.

Based on the selection, the formula would automatically update the result.

Something as shown below:

This makes a good dashboard component as you can have a huge data set with hundreds of students at the back end, but the end user (let’s say a teacher) can quickly get the marks of a student in a subject by simply making the selections from the drop down.

How to make this:

The VLOOKUP formula used in this case is the same used in Example 2.

The lookup values have been converted into drop-down lists.

Here are the steps to create the drop down list:

• Select the cell in which you want the drop-down list. In this example, in G4, we want the student names.
• Go to Data –> Data Tools –> Data Validation.
• In the Data Validation Dialogue box, within the settings tab, select List from the Allow drop-down.
• In the source, select $A$3:$A$10

Now you’ll have the drop-down list in cell G4. Similarly, you can create one in H3 for the subjects.

Example 4 – Three-way Lookup

What is a three-way lookup?

In Example 2, we’ve used one lookup table with scores for students in different subjects. This is an example of a two-way lookup as we use two variables to fetch the score (student’s name and the subject’s name).

Now, suppose in a year, a student has three different levels of exams, Unit Test, Midterm, and Final Examination (that’s what I had when I was a student).

A three-way lookup would be the ability to get a student’s marks for a specified subject from the specified level of exam.

In the above example, the VLOOKUP function can lookup in three different tables (Unit Test, Midterm, and Final Exam) and returns the score for the specified student in the specified subject.

Here is the formula used in cell H4:

This formula uses the CHOOSE function to make sure the right table is referred to. Let’s analyze the CHOOSE part of the formula:

CHOOSE(IF(H2=”Unit Test”,1,IF(H2=”Midterm”,2,3)),$A$3:$E$7,$A$11:$E$15,$A$19:$E$23)

The first argument of the formula is  IF(H2=”Unit Test”,1,IF(H2=”Midterm”,2,3)) , which checks the cell H2 and see what level of exam is being referred to. If it’s Unit Test, it returns $A$3:$E$7, which has the scores for Unit Test. If it’s Midterm, it returns $A$11:$E$15, else it returns $A$19:$E$23.

Doing this makes the VLOOKUP table array dynamic and hence makes it a three-way lookup.

Example 5 – Getting the Last Value from a List

You can create a VLOOKUP formula to get the last numerical value from a list.

The largest positive number that you can use in Excel is  9.99999999999999E+307 .  This also means that the largest lookup number in the VLOOKUP number is also the same.

I don’t think you would ever need any calculation involving such a large number. And that is exactly what we can use get the last number in a list.

Suppose you have a dataset (in A1:A14) as shown below and you want to get the last number in the list.

Here is the formula you can use:

Note that the formula above uses an approximate match VLOOKUP  (notice TRUE at the end of the formula, instead of FALSE or 0). Also, note that the list doesn’t need to be sorted for this VLOOKUP formula to work.

Here is how the approximate VLOOKUP function works. It scans the left most column from top to bottom.

• If it finds an exact match, it returns that value.
• If it finds a value that is higher than the lookup value, it returns the value in the cell above it.
• If the lookup value is greater than all the values in the list, it returns the last value.

In the above example, the third scenario is at work.

Since 9.99999999999999E+307 is the largest number that can be used in Excel, when this is used as the lookup value, it returns the last number from the list.

In the same way, you can also use it to return the last text item from the list. Here is the formula that can do that:

The same logic follows. Excel looks through all the names, and since zzz is considered bigger than any name/text starting with alphabets before zzz, it would return the last item from the list.

Example 6 – Partial Lookup using Wildcard Characters and VLOOKUP

Excel wildcard characters  can be really helpful in many situations.

It’s that magic potion that gives your formulas super powers.

Partial look-up is needed when you have to look for a value in a list and there isn’t an exact match.

For example, suppose you have a data set as shown below, and you want to look for the company ABC in a list, but the list has ABC Ltd instead of ABC.

You can not use ABC as the lookup value as there is no exact match in column A. Approximate match also leads to erroneous results and it requires the list to be sorted in an ascending order.

However, you can use a wildcard character within the VLOOKUP function to get the match.

Enter the following formula in cell D2 and drag it to the other cells:

How does this formula work?

In the above formula, instead of using the lookup value as is, it is flanked on both sides with the wildcard character asterisk (*) – “*”&C2&”*”

An asterisk is a wildcard character in Excel and can represent any number of characters.

Using the asterisk on both sides of the lookup value tells Excel that it needs to look for any text that contains the word in C2. It could have any number of characters before or after the text in C2.

For example, cell C2 has ABC, so the VLOOKUP function looks through the names in A2:A8 and searches for ABC. It finds a match in cell A2, as it contains ABC in ABC Ltd. It doesn’t matter if there are any characters to the left or right of ABC. Until there is ABC in a text string, it will be considered a match.

Note: VLOOKUP function always returns the first matching value and stops looking further. So if you have ABC Ltd., and ABC Corporation in a list, it will return the first one and ignore the rest.

Example 7 – VLOOKUP Returning an Error Despite a Match in Lookup Value

It can drive you crazy when you see that there is a matching lookup value and the VLOOKUP function is returning an error.

For example, in the below case, there is a match (Matt), but the VLOOKUP function still returns an error.

Now while we can see there is a match, what we can not see with a naked eye is that there could be leading or trailing spaces. If you have these additional spaces before, after, or in between the lookup values, it ISN’T an exact match.

This is often the case when you import data from a database or get it from someone else. These leading/trailing spaces have a tendency to sneak in.

The solution here is the TRIM function . It removes any leading or trailing spaces or extra spaces between words.

Here is the formula that’ll give you the right result.

Since this is an array formula, use Control + Shift + Enter instead of just Enter.

Another way could be to first treat your lookup array with the TRIM function to make sure all the additional spaces are gone, and then use the VLOOKUP function as usual.

Example 8 – Doing a Case Sensitive Lookup

By default, the lookup value in the VLOOKUP function is not case sensitive. For example, if your lookup value is MATT, matt, or Matt, it’s all the same for the VLOOKUP function. It’ll return the first matching value irrespective of the case.

But if you want to do a case-sensitive lookup, you need to use the EXACT function along with the VLOOKUP function.

Here is an example:

As you can see, there are three cells with the same name (in A2, A4, and A5) but with a different alphabet case.  On the right, we have the three names (Matt, MATT, and matt) along with their scores in Math.

Now the VLOOKUP function is not equipped to handle case-sensitive lookup values. In this above example, it would always return 38, which is the score for Matt in A2.

To make it case sensitive, we need to use a helper column (as shown below):

To get the values in the helper column, use the =ROW() function . It will simply get the row number in the cell.

Once you have the helper column, here is the formula that will give the case-sensitive lookup result.

Now let’s break down and understand what this does:

• EXACT(E2,$A$2:$A$9) – This part would compare the lookup value in E2 with all the values in A2:A9. It returns an array of TRUEs/FALSEs where TRUE is returned where there is an exact match. In this case, it would return the following array: {TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}.
• EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9) – This part multiplies the array of TRUEs/FALSEs with the row number. Wherever there is a TRUE, it gives the row number, else it gives 0. In this case, it would return {2;0;0;0;0;0;0;0}.
• MAX(EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9))) – This part returns the maximum value from the array of numbers. In this case, it would return 2 (which is the row number where there is an exact match).
• Now we simply use this number as the lookup value and use the lookup array as B2:C9

Note: Since this is an array formula, use Control + Shift + Enter instead of just enter.

Example 9 – Using VLOOKUP with Multiple Criteria

Excel VLOOKUP function, in its basic form, can look for one lookup value and return the corresponding value from the specified row.

But often there is a need to use VLOOKUP in Excel with multiple criteria.

Suppose you have a data with students name, exam type, and the Math score (as shown below):

Using the VLOOKUP function to get the Math score for each student for respective exam levels could be a challenge.

For example, if you try using VLOOKUP with Matt as the lookup value, it’ll always return 91, which is the score for the first occurrence of Matt in the list. To get the score for Matt for each exam type (Unit Test, Mid Term and Final), you need to create a unique lookup value.

This can be done using the helper column. The first step is to insert a helper column to the left of the scores.

Now, to create a unique qualifier for each instance of the name, use the following formula in C2: =A2&”|”&B2

Copy this formula to all the cells in the helper column. This will create unique lookup values for each instance of a name (as shown below):

Now, while there were repetitions of the names, there is no repetition when the name is combined with the level of examination.

This makes it easy as now you can use the helper column values as the lookup values.

Here is the formula that’ll give you the result in G3:I8.

Here we have combined the student name and the level of examination to get the lookup value, and we use this lookup value and checks it in the helper column to get the matching record.

Note: In the above example, we have used | as the separator while joining text in the helper column. In some exceptionally rare (but possible) conditions, you may have two criteria that are different but ends up giving the same result when combined. Here is an example:

Note that while A2 and A3 are different and B2 and B3 are different, the combinations end up being the same. But if you use a separator, then even the combination would be different (D2 and D3).

Here is a tutorial on how to use VLOOKUP with multiple criteria without using helper columns. You can also watch my video tutorial here .

Example 10 – Handling Errors while Using the VLOOKUP Function

Excel VLOOKUP function returns an error when it can not find the specified lookup value. You may not want the ugly error value disturbing the aesthetics of your data in case VLOOKUP can’t find a value.

You can easily remove the error values with any meaning full text such as “Not Available” or “Not Found”.

For example, in the example below, when you try to find the score of Brad in the list, it returns an error as Brad’s name is not there in the list.

To remove this error and replace it with something meaningful, wrap your VLOOKUP function within the IFERROR function.

Here is the formula:

The IFERROR function checks if the value returned by the first argument (which is the VLOOKUP function in this case) is an error or not. If it’s not an error, it returns the value by the VLOOKUP function, else it returns Not Found .

IFERROR function is available from Excel 2007 onwards. If you are using versions prior to that, use the following function:

Also See: How to handle VLOOKUP Errors in Excel .

That’s it in this VLOOKUP tutorial.

I’ve tried to cover major examples of using the Vlookup function in Excel. If you would like to see more examples added to this list, let me know in the comments section.

Note: I’ve tried my best to proofread this tutorial, but in case you find any errors or spelling mistakes, please let me know 🙂

Using  VLOOKUP Function in Excel – Video

Related Excel Functions:

• Excel HLOOKUP Function .
• Excel XLOOKUP Function
• Excel INDEX Function .
• Excel INDIRECT Function .
• Excel MATCH Function .
• Excel OFFSET Function .

You May Also Like the Following Excel Tutorials:

• VLOOKUP Vs. INDEX/MATCH – The Debate ends here.
• Excel Index Match Examples
• How to Make VLOOKUP Function Case Sensitive .
• Get Multiple Lookup Values Without Repetition in a Single Cell
• Avoid Nested IF Function in Excel by using VLOOKUP

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

55 thoughts on “10 vlookup examples for beginner & advanced users”.

Thanks a lot Sumit! I have seen many resources on vlook up, but your way is so simple to understand and also to help others understand. 🙂

can you please provide the excel sheet of above examples

Very helpful tutorial. Can anyone please guide how was the data validation drop down created in Example 4 for the Term exams (cell H2) done as it has multiple range areas which is not accepted in the simple ‘list’ function.

Thank you for this tutorial! I have tried a few video tutorials on youtube and step-by-step on other websites, but they did not successfully explain the way VLOOKUP actually works in the background, which was confusing to me. This one is going from the core basics and building up on top of that which is how I prefer to understand and learn things! I saw you mentioned to let you know if people notice some spelling errors so here is may take on that: The sentence “From the above data, I need to know how much Brad scored in Math.” is written twice in a row. 🙂 Thank you once again! Much appreciated!

So much love the teaching,o simplified. Thanks so much

hai summit, very useful site, way of your teaching style is wonderful. explaining with videos are so helpful and understandable .

Hi Sumit, Can you provide me with the proper vlookup formula to use if pulling data from a separate worksheet in the same workbook? I can use the regular lookup function and it works but it’s pulling the wrong information. Here is the formula I’m using for lookup (=lookup(d9, Code!A2:A164,CodeE2:E164).

Really your videos in Excel are very helpful and I need to learn many more techniques in Excel. How to automate the data from the previous Excel to the New Excel Book.

some new things even for experts

There must be an error in the Additional Notes section because its contradicting what is said in the 5th example: “If the VLOOKUP function can not find the value, it returns the *latest* value”, not greatest, “which is less than the lookup_value.”

Hello Nick.. The additional notes mention points when the data is sorted in ascending order. In that case, when VLOOKUP doesn’t find the value, it will give the largest value which is less than the lookup value.

In example 5, the data doesn’t need to be sorted and since we are looking for a really large number, it will go to the last cell and then return that last cell. This also work in case the data is sorted and it would then return the largest value.

Awesome , easiest way to learn & revise excel

Yes sir . I want to pick data such as : = Vlookup(C5, Vatlieu!$B$16:$B27,2,0) on the same workbook . Why it does’nt run ? Error : #Ref or #N/A . Can you help me . Very Thank .

Check your table array!!! It shows a reference error. Akshay Magre

Example 4 – Three-way Lookup

Can I do for multi-sheet using same formula as per Example 4 but already tried is not coming, please find the way to do it

I have no word to thanks that you share by so easy method..

I request You to share the File with various way….

how to create dropdown list for 3 different tables headings?

how did you create the dropdown list for unit test, mid term, final in 3 way lookup function? please explain

Good information @ mbaminiprojects.com

There are two columns of different numbers and I want to find out using a vlookup which numbers are missing

how to do left and right side Freeze pens in excel

Hi, I have 2 worksheets. One reference number is common in both the sheets. I want to import data on one sheet. Only those entries which are not there, since I don’t need repetitive columns. When I put the formula it gives an error. I have tried with if error as well. It again returns “not found” for all the data. Need help.

Definitely need some type of excel document to go along with this training

Can you share the examples in excel please

For the three way how do we get data validation to work for the three titles since they come from three separate areas? I keep getting an error when I try to create a drop down list with data validation and pressing ctrl key for the three titles.

I created the list in separate cells and then formatted them to blend in with the background. From memory, I think data validation is found as ALT + A + V. Best of luck

ow it is so nice thank you

Won’t it be better if you attach the excel sheet which you used in these examples. Without hands-on, it’s of no use.

I realize you were illustrating VLOOKUP techniques and your selection is very good. You could, however (also), use SUMPRODUCT for example 9 and avoid the helper column:

=SUMPRODUCT(($E2=$A$2:$A$19)*(F$1=$B$2:$B$19)*$C$2:$C$19)

Worked great!

Super Sumit Explanation are really superp I never miss your tutorial whenever it is posted on this blog. I always watch your turorial eagerly. Thanks.

Nicely explained

Great explanation. Can you share the examples as worksheets also ?

Very Good …. Learned some new things..

Advanced Excel Training Mumbai, Delhi, Gurgaon, Bangalore, Chennai. Excel Next offers training like MS Excel, MS Powerpoint, MS Excel VBA and many more.

Advanced Excel Training Gurgaon

I really like your post.It’s really informative and interesting.I really appreciate that.Thank you for sharing valuable information. Nice post. I enjoyed reading this post. The whole blog is very nice found some good stuff and good information here Thanks.

Hi Sumit, Is it possible to apply VLOOKUP along with formats? I have a master sheet from where I need to copy data into another sheet, however the constraint is the reference columns have got some formats like date and time stamp, currency, etc. and I need to copy the details as is by applying VLOOKUP. To the best of my knowledge its not possible without applying VBA. Just wanted to check with you if you can give me a non VBA solution :).

I can’t think of any non-VBA method to do this!

Thanks for your prompt response! If I can ask for your assistance as in how can i perform the aforesaid action.

Q. क्या मैं हिंदी में प्रश्न पूछ सकता हूँ।

Hi, I completely understand your question. This was the same problem with me in excel.

And then I got a YouTube channel ( Learn With Lokesh Lalwani) I always learn everything about the excel this channel. I would like to suggest that you can also learn from this channel.

Aap Vlookup ke bare me is youtube channel par sab kuch sikh sakte hai. or bhi kafi sare topics hai.

Great post. Most Excel users are still using VLOOKUP rather than INDEX/MATCH. So it’s great to see VLOOKUP being used in so many different ways (some of which I will definitely be learning from).

Good Tutorial.

Thanks for commenting.. Glad you liked it

I want to add, that VLOOKUP is able to look up from right to left also.

Hello Armen. . Yes, VLOOKUP can look to the left as well, but in such a case, I prefer using INDEX/MATCH formula combination

Hi Sumit, Would you please share an example on this

Excellent Tutorial. Learned a lot!

Thanks for commenting.. Glad you liked it!

Hi Sumit Opened my eyes to vlookup I have and do use it, and didn’t know as much as what you have explained Good job

Thanks for commenting Rahul.. Glad you found the tutorial useful.

Very well explained! It saved me a lot of time!

Thanks Aanchal.. Glad you found this useful 🙂

Leave a Comment Cancel reply

BEST EXCEL TUTORIALS

Best Excel Shortcuts

Conditional Formatting

Excel Skills

Creating a Pivot Table

Excel Tables

INDEX- MATCH Combo

Creating a Drop Down List

Recording a Macro

© TrumpExcel.com – Free Online Excel Training

Privacy Policy  | Sitemap

How-To Geek

How to use vlookup in excel.

VLOOKUP is one of Excel’s most useful functions, and it’s also one of the least understood. In this article, we demystify VLOOKUP by way of a real-li

VLOOKUP is one of Excel’s most useful functions, and it’s also one of the least understood.  In this article, we demystify VLOOKUP by way of a real-life example.  We’ll create a usable Invoice Template for a fictitious company.

VLOOKUP is an Excel function.  This article will assume that the reader already has a passing understanding of Excel functions, and can use basic functions such as SUM, AVERAGE, and TODAY.  In its most common usage, VLOOKUP is a database function, meaning that it works with database tables - or more simply, lists of things in an Excel worksheet.  What sort of things?   Well, any sort of thing.  You may have a worksheet that contains a list of employees, or products, or customers, or CDs in your CD collection, or stars in the night sky.  It doesn’t really matter.

Here’s an example of a list, or database.  In this case it’s a list of products that our fictitious company sells:

Usually lists like this have some sort of unique identifier for each item in the list.  In this case, the unique identifier is in the “Item Code” column.  Note:  For the VLOOKUP function to work with a database/list, that list must have a column containing the unique identifier (or “key”, or “ID”), and that column must be the first column in the table.  Our sample database above satisfies this criterion.

The hardest part of using VLOOKUP is understanding exactly what it’s for.  So let’s see if we can get that clear first:

VLOOKUP retrieves information from a database/list based on a supplied instance of the unique identifier.

In the example above, you would insert the VLOOKUP function into another spreadsheet with an item code, and it would return to you either the corresponding item’s description, its price, or its availability (its “In stock” quantity) as described in your original list.  Which of these pieces of information will it pass you back?  Well, you get to decide this when you’re creating the formula.

If all you need is one piece of information from the database, it would be a lot of trouble to go to to construct a formula with a VLOOKUP function in it.  Typically you would use this sort of functionality in a reusable spreadsheet, such as a template.  Each time someone enters a valid item code, the system would retrieve all the necessary information about the corresponding item.

Let’s create an example of this:  An Invoice Template that we can reuse over and over in our fictitious company.

First we start Excel, and we create ourselves a blank invoice:

This is how it’s going to work:  The person using the invoice template will fill in a series of item codes in column “A”, and the system will retrieve each item’s description and price from our product database. That information will be used to calculate the line total for each item (assuming we enter a valid quantity).

For the purposes of keeping this example simple, we will locate the product database on a separate sheet in the same workbook:

In reality, it’s more likely that the product database would be located in a separate workbook.  It makes little difference to the VLOOKUP function, which doesn’t really care if the database is located on the same sheet, a different sheet, or a completely different workbook.

So, we've created our product database, which looks like this:

In order to test the VLOOKUP formula we’re about to write, we first enter a valid item code into cell A11 of our blank invoice:

Next, we move the active cell to the cell in which we want information retrieved from the database by VLOOKUP to be stored.  Interestingly, this is the step that most people get wrong.  To explain further:  We are about to create a VLOOKUP formula that will retrieve the description that corresponds to the item code in cell A11.  Where do we want this description put when we get it?  In cell B11, of course.  So that’s where we write the VLOOKUP formula: in cell B11. Select cell B11 now.

We need to locate the list of all available functions that Excel has to offer, so that we can choose VLOOKUP and get some assistance in completing the formula.  This is found by first clicking the Formulas tab, and then clicking Insert Function :

A box appears that allows us to select any of the functions available in Excel.

To find the one we’re looking for, we could type a search term like “lookup” (because the function we’re interested in is a lookup function).  The system would return us a list of all lookup-related functions in Excel.  VLOOKUP is the second one in the list.  Select it an click OK .

The Function Arguments box appears, prompting us for all the arguments (or parameters) needed in order to complete the VLOOKUP function.  You can think of this box as the function asking us the following questions:

• What unique identifier are you looking up in the database?
• Where is the database?
• Which piece of information from the database, associated with the unique identifier, do you wish to have retrieved for you?

The first three arguments are shown in bold , indicating that they are mandatory arguments (the VLOOKUP function is incomplete without them and will not return a valid value).  The fourth argument is not bold, meaning that it’s optional:

We will complete the arguments in order, top to bottom.

The first argument we need to complete is the Lookup_value argument.  The function needs us to tell it where to find the unique identifier (the item code in this case) that it should be returning the description of.  We must select the item code we entered earlier (in A11).

Click on the selector icon to the right of the first argument:

Then click once on the cell containing the item code (A11), and press Enter :

The value of “A11” is inserted into the first argument.

Now we need to enter a value for the Table_array argument.  In other words, we need to tell VLOOKUP where to find the database/list.  Click on the selector icon next to the second argument:

Now locate the database/list and select the entire list - not including the header line.  In our example, the database is located on a separate worksheet, so we first click on that worksheet tab:

Next we select the entire database, not including the header line:

...and press Enter .  The range of cells that represents the database (in this case “’Product Database’!A2:D7”) is entered automatically for us into the second argument.

Now we need to enter the third argument, Col_index_num .  We use this argument to specify to VLOOKUP which piece of information from the database, associate with our item code in A11, we wish to have returned to us.  In this particular example, we wish to have the item’s description returned to us.  If you look on the database worksheet, you’ll notice that the “Description” column is the second column in the database.  This means that we must enter a value of “2” into the Col_index_num box:

It is important to note that that we are not entering a “2” here because the “Description” column is in the B column on that worksheet.  If the database happened to start in column K of the worksheet, we would still enter a “2” in this field because the “Description” column is the second column in set of cells we selected when specifying the “Table_array”.

Finally, we need to decide whether to enter a value into the final VLOOKUP argument, Range_lookup .  This argument requires either a true or false value, or it should be left blank.  When using VLOOKUP with databases (as is true 90% of the time), the way to decide what to put in this argument can be thought of as follows:

If the first column of the database (the column that contains the unique identifiers) is sorted alphabetically/numerically in ascending order, then it’s possible to enter a value of true into this argument, or leave it blank. If the first column of the database is not sorted, or it’s sorted in descending order, then you must enter a value of false into this argument

As the first column of our database is not sorted, we enter false into this argument:

That’s it!  We’ve entered all the information required for VLOOKUP to return the value we need.  Click the OK button and notice that the description corresponding to item code “R99245” has been correctly entered into cell B11:

The formula that was created for us looks like this:

If we enter a different item code into cell A11, we will begin to see the power of the VLOOKUP function:  The description cell changes to match the new item code:

We can perform a similar set of steps to get the item’s price returned into cell E11.  Note that the new formula must be created in cell E11.  The result will look like this:

...and the formula will look like this:

Note that the only difference between the two formulae is the third argument ( Col_index_num ) has changed from a “2” to a “3” (because we want data retrieved from the 3rd column in the database).

If we decided to buy 2 of these items, we would enter a “2” into cell D11.  We would then enter a simple formula into cell F11 to get the line total:

...which looks like this...

Completing the Invoice Template

We’ve learned a lot about VLOOKUP so far.  In fact, we’ve learned all we’re going to learn in this article.  It’s important to note that VLOOKUP can be used in other circumstances besides databases.  This is less common, and may be covered in future How-To Geek articles.

Our invoice template is not yet complete.  In order to complete it, we would do the following:

• We would copy the formulas in cells B11, E11 and F11 down to the remainder of the item rows of the invoice.  Note that if we do this, the resulting formulas will no longer correctly refer to the database table.  We could fix this by changing the cell references for the database to absolute cell references.  Alternatively - and even better - we could create a range name for the entire product database (such as “Products”), and use this range name instead of the cell references.  The formula would change from this...      =IF(ISBLANK(A11),"",VLOOKUP(A11,'Product Database'!A2:D7,2,FALSE)) ...to this...       =IF(ISBLANK(A11),"",VLOOKUP(A11,Products,2,FALSE)) ...and then copy the formulas down to the rest of the invoice item rows.
• We would probably “lock” the cells that contain our formulae (or rather unlock the other cells), and then protect the worksheet, in order to ensure that our carefully constructed formulae are not accidentally overwritten when someone comes to fill in the invoice.
• We would save the file as a template, so that it could be reused by everyone in our company

If we were feeling really clever, we would create a database of all our customers in another worksheet, and then use the customer ID entered in cell F5 to automatically fill in the customer’s name and address in cells B6, B7 and B8.

If you would like to practice with VLOOKUP, or simply see our resulting Invoice Template, it can be downloaded from here .

• Ablebits blog

Advanced VLOOKUP in Excel: multiple, double, nested

These examples will teach you how to Vlookup multiple criteria, return a specific instance or all matches, do dynamic Vlookup in multiple sheets, and more.

It is the second part of the series that will help you harness the power of Excel VLOOKUP. The examples imply that you know how this function works. If not, it stands to reason to start with the basic uses of VLOOKUP in Excel .

Before moving further, let me briefly remind you the syntax:

Now that everyone is on the same page, let's take a closer look at the advanced VLOOKUP formula examples:

How to Vlookup multiple criteria

The Excel VLOOKUP function is really helpful when it comes to searching across a database for a certain value. However, it lacks an important feature - its syntax allows for just one lookup value. But what if you want to look up with several conditions? There are a few different solutions for you to choose from.

Formula 1. VLOOKUP with two criteria

A usual VLOOKUP formula won't work in this situation because it returns the first found match based on a single lookup value that you specify.

To overcome this, you can add a helper column and concatenate the values from two lookup columns ( Customer and Product ) there. It is important that the helper column should be the leftmost column in the table array because it's where Excel VLOOKUP always searches for the lookup value.

So, add a column to the left of your table and copy the below formula across that column. This will populate the helper column with the values from columns B and C (the space character is concatenated in between for better readability):

=B2&" "&C2

And then, use a standard VLOOKUP formula and place both criteria in the lookup_value argument, separated with a space:

=VLOOKUP("Jeremy Sweets", A2:D11, 4, FALSE)

Or, input the criteria in separate cells (G1 and G2 in our case) and concatenate those cells:

=VLOOKUP(G1&" "&G2, A2:D11, 4, FALSE)

In case your lookup table is in another sheet , include the sheet's name in your VLOOKUP formula. For example:

=VLOOKUP(G1&" "&G2, Orders!A2:D11, 4, FALSE)

Alternatively, create a named range for the lookup table (say, Orders ) to make the formula easier-to-read:

=VLOOKUP(G1&" "&G2, Orders, 4, FALSE)

For more information, please see How to Vlookup from another sheet in Excel .

Formula 2. Excel VLOOKUP with multiple conditions

In theory, you can use the above approach to Vlookup more than two criteria. However, there are a couple of caveats. Firstly, a lookup value is limited to 255 characters, and secondly, the worksheet's design may not allow adding a helper column.

Luckily, Microsoft Excel often provides more than one way to do the same thing. To Vlookup multiple criteria, you can use either an INDEX MATCH combination or the XLOOKUP function recently introduced in Office 365.

For example, to look up based on 3 different values ( Date , Customer name and Product ), use one of the following formulas:

=INDEX(D2:D11, MATCH(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), 0))

=XLOOKUP(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), D2:D11)

• G1 is criteria 1 (date)
• G2 is criteria 2 (customer name)
• G3 is criteria 3 (product)
• A2:A11 is lookup range 1 (dates)
• B2:B11 is lookup range 2 (customer names)
• C2:C11 is lookup range 3 (products)
• D2:D11 is the return range (quantity)

For the detailed explanation of the formulas, please see:

• XLOOKUP with multiple criteria
• INDEX MATCH formula with multiple criteria

How to use VLOOKUP to get 2 nd , 3 rd or n th match

As you already know, Excel VLOOKUP can fetch only one matching value, more precisely, it returns the first found match. But what if there are several matches in your lookup array and you want to get the 2 nd or 3 rd instance? The task sounds quite intricate, but the solution does exist!

Formula 1. Vlookup Nth instance

Suppose you have customer names in one column, the products they purchased in another, and you are looking to find the 2 nd or 3 rd product bought by a given customer.

The simplest way is to add a helper column to the left of the table like we did in the first example. But this time, we will populate it with customer names and occurrence numbers like " John Doe1 ", " John Doe2 ", etc.

To get the occurrence, use the COUNTIF function with a mixed range reference (the first reference is absolute and the second is relative like $B$2:B2). Since the relative reference changes based on a position of the cell where the formula is copied, in row 3 it will become $B$2:B3, in row 4 - $B$2:B4, and so on.

Concatenated with the customer name (B2), the formula takes this form:

=B2&COUNTIF($B$2:B2, B2)

The above formula goes to A2, and then you copy it down to as many cells as needed.

After that, input the target name and occurrence number in separate cells (F1 and F2), and use the below formula to Vlookup a specific occurrence:

Formula 2. Vlookup 2nd occurrence

If you are looking for the 2nd instance of the lookup value, then you can do without the helper column. Instead, create the table array dynamically by using the INDIRECT function together with MATCH:

=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11"), 2, FALSE)

• E1 is the lookup value
• A2:A11 is the lookup range

Please note that the above formula is written for a specific case where data cells in the lookup table begin in row 2. If your table is somewhere in the middle of the sheet, use this universal formula, where A1 is the top-left cell of the lookup table containing a column header:

=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+1+ROW(A1))&":B11"), 2, FALSE)

How this formula works

Here is the key part of the formula that creates a dynamic vlookup range :

INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11")

The MATCH function configured for exact match (0 in the last argument) compares the target name (E1) against the list of names (A2:A11) and returns the position of the first found match, which is 3 in our case. This number is going to be used as the starting row coordinate for the vlookup range, so we add 2 to it (+1 to exclude the first instance and +1 to exclude row 1 with the column headers). Alternatively, you can use 1+ROW(A1) to calculate the necessary adjustment automatically based on the position of the header row (A1 in our case).

As the result, we get the following text string, which INDIRECT converts to a range reference:

INDIRECT("A"&5&":B11") -> A5:B11

This range goes to the table_array argument of VLOOKUP forcing it to start searching in row 5, leaving out the first instance of the lookup value:

How to Vlookup and return multiple values in Excel

The Excel VLOOKUP function is designed to return just one match. Is there a way to Vlookup multiple instances? Yes, there is, though not an easy one. This requires a combined use of several functions such as INDEX, SMALL and ROW is an array formula .

For example, the below can find all occurrences of the lookup value F2 in the lookup range B2:B16 and return multiple matches from column C:

{=IFERROR(INDEX($C$2:$C$11, SMALL(IF($F$1=$B$2:$B$11, ROW($C$2:$C$11)-1,""), ROW()-1)),"")}

There are 2 ways to enter the formula in your worksheet:

• Type the formula in the first cell, press Ctrl + Shift + Enter , and then drag it down to a few more cells.
• Select several adjacent cells in a single column (F1:F11 in the screenshot below), type the formula and press Ctrl + Shift + Enter to complete it.

How to Vlookup in rows and columns (two-way lookup)

Two-way lookup (aka matrix lookup or 2-dimentional lookup ) is a fancy word for looking up a value at the intersection of a certain row and column. There are a few different ways to do two-dimensional lookup in Excel, but since the focus of this tutorial is on the VLOOKUP function, we will naturally use it.

For this example, we'll take the below table with monthly sales and work out a VLOOKUP formula to retrieve the sales figure for a specific item in a given month.

With item names in A2:A9, month names in B1:F1, the target item in I1 and the target month in I2, the formula goes as follows:

The core of the formula is the standard VLOOKUP function that searches for an exact match to the lookup value in I1. But since we do not know in which exactly column the sales for a specific month are, we cannot supply the column number directly to the col_index_num argument. To find that column, we use the following MATCH function :

MATCH(I2, A1:F1, 0)

Translated into English, the formula says: look up the I2 value in A1:F1 and return its relative position in the array. By supplying 0 to the 3rd argument, you instruct MATCH to find the value exactly equal to the lookup value (it's like using FALSE for the range_lookup argument of VLOOKUP).

Since Mar is in the 4th column in the lookup array, the MATCH function returns 4, which goes directly to the col_index_num argument of VLOOKUP:

VLOOKUP(I1, A2:F9, 4, FALSE)

Please pay attention that although the month names start in column B, we use A1:I1 for the lookup array. This is done in order for the number returned by MATCH to correspond to the column's position in table_array of VLOOKUP.

How to do multiple Vlookup in Excel (nested Vlookup)

Sometimes it may happen that your main table and lookup table do not have a single column in common, which prevents you from doing a Vlookup between two tables. However, there exists another table, which does not contain the information you are looking for but has one common column with the main table and another common column with the lookup table.

The goal is to copy prices to the main table based on Item IDs . The problem is that the table containing prices does not have the Item IDs , meaning we will have to do two Vlookups in one formula.

For the sake of convenience, let's create a couple of named ranges first:

• Lookup table 1 is named Products (D3:E10)
• Lookup table 2 is named Prices ( G3:H10 )

The tables can be in the same or different worksheets.

And now, we will perform the so-called double Vlookup , aka nested Vlookup .

First, make a VLOOKUP formula to find the product name in the Lookup table 1 (named Products ) based on the item id (A3):

=VLOOKUP(A3, Products, 2, FALSE)

Next, put the above formula in the lookup_value argument of another VLOOKUP function to pull prices from Lookup table 2 (named Prices ) based on the product name returned by the nested VLOOKUP:

=VLOOKUP(VLOOKUP(A3, Products, 2, FALSE), Prices, 2, FALSE)

How to Vlookup multiple sheets dynamically

Sometimes, you may have data in the same format split over several worksheets. And your aim is to pull data from a specific sheet depending on the key value in a given cell.

Like in the previous example, we start with defining a few names:

• Range A2:B5 in CA sheet is named CA_Sales .
• Range A2:B5 in FL sheet is named FL_Sales .
• Range A2:B5 in KS sheet is named KS_Sales .

As you can see, all the named ranges have a common part ( Sales ) and unique parts ( CA , FL , KS ). Please be sure to name your ranges in a similar manner as it's essential for the formula we are going to build.

Formula 1. INDIRECT VLOOKUP to dynamically pull data from different sheets

If your task is to retrieve data from multiple sheets, a VLOOKUP INDIRECT formula is the best solution – compact and easy-to-understand.

For this example, we organize the summary table in this way:

• Input the products of interest in A2 and A3. Those are our lookup values.
• Enter the unique parts of the named ranges in B1, C1 and D1.

And now, we concatenate the cell containing the unique part (B1) with the common part ("_Sales"), and feed the resulting string to INDIRECT:

INDIRECT(B$1&"_Sales")

The INDIRECT function transforms the string into a name that Excel can understand, and you put it in the table_array argument of VLOOKUP:

=VLOOKUP($A2, INDIRECT(B$1&"_Sales"), 2, FALSE)

The above formula goes to B2, and then you copy it down and to the right.

If your main table is organized differently, the lookup values in a row and unique parts of the range names in a column, then you should lock the row coordinate in the lookup value (B$1) and the column coordinate in the name parts ($A2):

Formula 2. VLOOKUP and nested IFs to look up multiple sheets

In situation when you have just two or three lookup sheets, you can use a fairly simple VLOOKUP formula with nested IF functions to select the correct sheet based on the key value in a particular cell:

=VLOOKUP($A2, IF(B$1="CA", CA_Sales, IF(B$1="FL", FL_Sales, IF(B$1="KS", KS_Sales,""))), 2, FALSE)

In this case, you do not necessarily need to define names and can use external references to refer to another sheet or workbook.

That's how to use VLOOKUP in Excel. I thank you for reading and hope to see you on our blog next week!

Practice workbook for download

You may also be interested in.

• How to Vlookup multiple matches in Excel
• How to use VLOOKUP & SUM or SUMIF functions in Excel
• How to do a case-sensitive Vlookup in Excel
• How to fix Excel VLOOKUP errors
• 5 ways to VLOOKUP in Excel - which is fastest?

Table of contents

478 comments

I have a worksheet with different file numbers for the same account

Account number is in column A and file numbers in B

Please give me a formula where it will give me all the multiple values in column B for an account number in column A

Hi! Look for the example formulas here: How to Vlookup multiple values in Excel with criteria .

please tell me the formula for different columns data to lookup for same values . for ex : order id AB022365 , column heading 1 order status( delivered/undelivered , colun2 (complaint), column 3( branch)and so on . but formula must work together .

Hi! Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you the following guide: How to highlight duplicate cells and rows in Excel . If this does not help, explain the problem in detail.

Hello, I am looking for formula where I have a row on product with multiple information in columns. I have to base information from two column and return back value from column 1. For example:

Houses Cars Office Matt 3 2 2 Venkat 4 1 3 Lars 2 3 6 Sam 8 4 5

Output I want to know person with 2 houses and 1 car I want to know person with 3 houses and 4 cars

How can this be done?

Hi! If I understand your task correctly, you can find the examples and detailed instructions here:: Excel INDEX MATCH with multiple criteria . The formula might look like this:

=INDEX(A1:A10,MATCH(1,(B1:B10=2)*(C1:C10=1),0))

I'm looking for a formula that will look up a date in column A, then lookup a name in column C, then look up a category from row 1, and return the value when all three has been met. I have tried using the following =INDEX(AU2:DJ139,MATCH(C2,AU2:AU5000)*(B21,AW2:AW5000)*(F20,AU1:DJ1),0) but get #N/A returned. Any suggestions as to what formula will look up all three and return the value would appreciated.

Hi! To do a multi-criteria search, look for the example formulas here: Excel INDEX MATCH with multiple criteria . Also note that all ranges in the formula must be the same size. I hope I answered your question. If something is still unclear, please feel free to ask.

Good afternoon, I am unable to find a formula to produce a correct value in a spreadsheet. I have two worksheets (DR and PI).

DR contains complete records and PI contains the records I am reviewing. I need excel to find PI cell value of A9 and PI cell value of I9 in a single row in the DR raw data, and when those values match return the value of DR column 11 to PI.

Hi! To search for two values, use the INDEX MATCH functions . However, your information is not enough to give you more accurate advice. In which cells will you search for these values? To understand what you want to do, give an example of the source data and the expected result.

I am using the VLOOKUP function to search for values in one workbook (Workbook 2 cells: F513 = 515,600.05 and cell F518 = 96,560.46) 6 columns to the right of column A, based on multiple values in another workbook, within ONE cell (Workbook 1 cell A15 = 401-05-0000, 403-01-0000. I would like the results to be added together and the result placed in one cell (Workbook 1 cell G15. How should I modify the VLOOKUP function below at the red arrow to do this?

=VLOOKUP(A15,'[BVAR Balanza julio 2023.xlsx]Balanza de Comprobación'!$A$387:$F$834,6,FALSE)

Thank you David

Sorry, ignore "at the red arrow to do this?". I could not post a screen shot. I just want to know how to modify the VLOOKUP to accomplish the above.

Thank you, David

Hi! To sum multiple values based on several criteria, try using the SUMIFS function. Read more: Excel SUMIFS and SUMIF with multiple criteria – formula examples .

How many sources of importrange i can use for vlookup?

I am not sure I fully understand what you mean.

HI I have two sheets.

Sheet 1 is where i want to see the result (Allocated time) and Sheet 2 is the source. There are two criteria to look at 1 is the country 2 is the service provider 1-5. Main goal is to catch the number of hours assign for each service provider in a given country.

Sheet 1 Belgium Provider 1 Allocated Time based on Sheet 2 Belgium Provider 2 Allocated Time based on Sheet 2 Belgium Provider 3 Allocated Time based on Sheet 2 Belgium Provider 4 Allocated Time based on Sheet 2 Belgium Provider 5 Allocated Time based on Sheet 2

In the source sheet 2, you'll notice that the provider is not in sequence like in Sheet 1

Sheet 2 Belgium Provider 3 Allocated time Belgium Provider 5 Allocated time Belgium Provider 2 Allocated time Belgium Provider 1 Allocated time Belgium Provider 4 Allocated time

Hi, I have a query how to vlookup on multiple conditions.

my data I have emp id in column A, emp details in Colom D and values in column E my qurey is I want to vlookup for column A 1st and match with column D and get the results of E in to my results cell. column D have multiple rows like basic pay, DA, HRA etc column A have emp id

here is the sample data

1010408 RAMESH RUPIREDDY Personnel#: 1010408 BASIC PAY 1,85,430.00 1010408 RAMESH RUPIREDDY Name: RAMESH RUPIREDDY Basic GPF Arr 7,57,185.00 1010408 RAMESH RUPIREDDY Desig ADE STAGNATION PAY 12,240.00 1010408 RAMESH RUPIREDDY Seat No: CPRS-S2-1 Stagnation Pay GPF -52,860.00 1010408 RAMESH RUPIREDDY BANK: SBHY0020138 Stagnation Pay EPF 69,779.00 1010408 RAMESH RUPIREDDY A/c No 52135288942 D.A. 11,655.00 1010408 RAMESH RUPIREDDY Net Pay: 181559 D A(GPF) Arr -4,17,824.00 1010408 RAMESH RUPIREDDY PPO No D A(EPF) Arr 2,391.00 1010408 RAMESH RUPIREDDY H.R.A 21,000.00 1010408 RAMESH RUPIREDDY HRA Arr 78,001.00

Request to provide the me formula for this

Hi! Unfortunately, this information is not enough to understand what you need. Specify what results you want in column E for each row of your data sample. Describe your task in more detail.

my day is like this

Personnel#: 1010408 BASIC PAY 1,27,185.00 GIS -120 Name: RAMESH RUPIREDDY STAGNATION PAY 4,405.00 GPF Contribution -8,000.00 Desig ADE Stagnation Pay GPF 17,620.00 DA Cr to GPF 4,370.00 Seat No: CPRS-S2-1 D.A. 37,685.00 LIC -5,074.00 BANK: SBHY0020138 D A(GPF) Arr 4,726.00 Professional Tax -200 A/c No 52135288942 H.R.A 15,000.00 INCOME TAX -50,000.00 Net Pay: 135743 GENERATION ALLOWAN 24,637.00 SFMS -500 PPO No COAL HANDLING ALLO 455 bank loan -36,650.00 CONVEYANCE ALLOWAN 1,500.00 society -5,170.00 Shift Allw 6,822.00 Sl.No 2 Leave encashment A 2 Total Deductions -1,10,084.00 CUG ALLOWANCE 83 PLF ALLOWANCE 2,767.00 TELANGANA INCREMEN 955 24X7 POWER INCREME 1,985.00

Earnings 25,115.00 Earnings 2,20,712.00

Total Earnings 2,45,827.00 ************ ***************************** ****************** ********************************** ********************** ***************** Personnel#: 1010410 BASIC PAY 1,31,590.00 GIS -360 Name: SANTOSH PASPULATTI Basic GPF Arr 3,46,520.00 GPF Contribution -7,895.00 Desig ADE D.A. 37,685.00 DA Cr to GPF 17,428.00 Seat No: CPRS-S2-1 D A(GPF) Arr 96,198.00 LIC -27,822.00 BANK: SBHY0020138 H.R.A 15,000.00 Professional Tax -600 A/c No 52135320498 HRA Arr 43,500.00 INCOME TAX -1,96,300.00 Net Pay: 474143 GENERATION ALLOWAN 24,637.00 SFMS -1,500.00 PPO No Gen.Allw Arr 6,358.00 COAL HANDLING ALLO 455 Total Deductions -2,51,905.00 Coal Handling Allo 117 Sl.No 3 CONVEYANCE ALLOWAN 1,500.00 Conveyance Allw Ar 387 Shift Allw 6,822.00 Shift Allw Arr 1,761.00 CUG ALLOWANCE 83 CUG Allowance Arr 83 PLF ALLOWANCE 1,598.00 TELANGANA INCREMEN 955 TELANGANA INCREMEN 2,515.00 24X7 POWER INCREME 2,280.00 24X7 POWER INCREME 6,004.00

Earnings 5,05,041.00 Earnings 2,21,007.00

Total Earnings 7,26,048.00 ************ ***************************** ****************** ********************************** ********************** ***************** Personnel#: 1010411 BASIC PAY 1,35,995.00 GIS -120 Name: RAMESH BANDI F.P.I 75 GPF Contribution -20,000.00 Desig ADE D.A. 38,946.00 LIC -6,125.00 Seat No: CPRS-S2-1 GENERATION ALLOWAN 24,637.00 Professional Tax -200 BANK: SBHY0020138 Gen.Allw Arr -367 INCOME TAX -37,100.00 A/c No 52135294116 COAL HANDLING ALLO 455 SFMS -500 Net Pay: 138147 Shift Allw Arr -1,320.00 PPO No PLF ALLOWANCE 536 Total Deductions -64,045.00 TELANGANA INCREMEN 955 24X7 POWER INCREME 2,280.00 Sl.No 4 Earnings -1,151.00 Earnings 2,03,343.00

Total Earnings 2,02,192.00 ************ ***************************** ****************** ********************************** ********************** ***************** Personnel#: 1035357 BASIC PAY 2,48,330.00 GIS -120 Name: SURESH KUMAR ERIKI STAGNATION PAY 10,560.00 GPF Contribution -15,533.00 Desig ADE F.P.I 860 LIC -3,154.00 Seat No: CPRS-S2-1 D.A. 74,141.00 Professional Tax -200 BANK: SBIN0021031 H.R.A 15,000.00 INCOME TAX -99,600.00 A/c No 52108831007 GENERATION ALLOWAN 24,637.00 SFMS -500 Net Pay: 270998 DUST ALLOWANCE 275 PPO No CONVEYANCE ALLOWAN 1,500.00 Total Deductions -1,19,107.00 Shift Allw 6,822.00 CUG ALLOWANCE 83 Sl.No 5 PLF ALLOWANCE 2,767.00 TELANGANA INCREMEN 1,750.00 24X7 POWER INCREME 3,380.00

Earnings 2,767.00 Earnings 3,87,338.00

Total Earnings 3,90,105.00

what I want is I want get all these into a table format

like Sno Personnel#: BASIC PAY STAGNATION PAY F.P.I D.A. GENERATION ALLOWAN COAL HANDLING ALLO CONVEYANCE ALLOWAN Shift Allw CUG ALLOWANCE PLF ALLOWANCE TELANGANA INCREMEN 24X7 POWER INCREME Total Earnings GSLISA GIS GPF Contribution Professional Tax INCOME TAX CC CHARGES CC CHARGES SFMS Total Deductions

the raw data is consist of 100's of employees I want the data transposed to table format with above headers and corresponding values, each employee data is separated with ******* row and for each emp have the earning in one column and deduction in another column

really appreciated your help

thank you in advance.

is there a way to share the sample file let me know so it will be much easier to understand

I want formula for two sheet vlookup then i want c column value against b& a

Hi! Look for the example formulas here: How to VLOOKUP across multiple sheets in Excel with examples .

? I have sheet for real estate customer payment for rent each customer with deferent code number have how can i make the formula for each code that remind me to pay then the status for each customer after unpaid show paid on that date.

please support me

Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.

WELL STRING TEST DATE BLPD A 01/01/2015 1,246 B 05/10/2018 879 C 07/03/2015 1,135 D 12/09/2015 674 A 06/10/2015 978 B 28/12/2015 0 C 20/01/2015 1,498 D 02/07/2015 957 A 10/08/2015 1,189 B 15/11/2015 654 C 21/05/2015 1,359 D 31/01/2015 31 .....Continue

This is the data i have. In this data the well string name is going to repeat multiple times with different test date & BLPD value.

Now i have another set of data as follows:

WELL STRING ACTIVITY DATE TEST DATE NEAREST TO ACTIVITY DATE BLPD A 04/02/2015 ? ? B 07/11/2018 ? ? C 12/04/2015 ? ? D 13/08/2015 ? ? A 05/12/2015 ? ? B 26/11/2015 ? ? C 10/03/2015 ? ? D 04/08/2015 ? ? A 12/07/2015 ? ? B 14/10/2015 ? ? C 25/06/2015 ? ? D 31/07/2015 ? ?

How to enter the formula for question mark?

Sorry, I do not fully understand the task. To understand what you want, give examples of the desired result.

For example: In 1st row of second data base... Well String A has activity date 04/02/2015. In row 1 Column C3 of database 2, we want "TEST DATE NEAREST TO ACTIVITY DATE". Now we go to data base 1 & search the test date nearest to activity date 4/02/2015 for Well String A. In database 1, we can see there are 3 test dates against well string A & the test date nearest to activity date(04/02/2015) is 01/01/2015. So in database 2: R1C3 will be 01/01/2015 & R1C4( BLPD: Barrel Liquid per day) will be 1,246 as given in database1. I want Column 3 & Column 4 of database 2 to be derived from database 1 with the help of formula. Please suggest.

Hello! Use the FILTER function to get values for Well String A only. Then use INDEX MATCH function to find the BLPD value for the nearest date.

=INDEX(FILTER('1'!A1:C12,'1'!A1:A12='2'!A1), MATCH(MIN(ABS('2'!B1-FILTER('1'!B1:B12,'1'!A1:A12='2'!A1))), ABS('2'!B1-FILTER('1'!B1:B12,'1'!A1:A12='2'!A1)),0),3)

Hope this is what you need.

Post a comment

Get Ahead with Excel: An Intermediate Guide with VLOOKUP Examples, Formulas, Syntax, and Practice Samples

By Andy Marker | March 30, 2018

Link copied

Excel’s VLOOKUP function is a basic but powerful feature available for you to make the most of the data in your spreadsheets. For new users, VLOOKUP may seem like a mystical, complicated affair, but when you break it down, it becomes a simple ally for getting your data into the right places, whether you are pulling it from other workbooks, worksheets, or just across the page.

What follows is an intermediate-level course on VLOOKUP and its uses. In this guide, you will start with the basic functionality of VLOOKUP. Next, you’ll learn about troubleshooting your formulas and why you’ll occasionally encounter problems after sorting, along with some tips, rules, and tricks on how to better use VLOOKUP.

You’ll discover intermediate uses of VLOOKUP that include learning how to combine VLOOKUP with MATCH, perform a nested VLOOKUP with multiple criteria, and use VLOOKUP to get second, third, or multiple occurrences of the same value, as well as how and why to use INDEX-MATCH instead of VLOOKUP. In addition to building your skills in VLOOKUP for Excel, you’ll find a tutorial for using the feature in Google Sheets and Smartsheet.

These examples and tutorials are good for Excel versions 2007 through 2016.

What Is the Formula for VLOOKUP in Excel?

In Excel, VLOOKUP is a lookup/reference function that helps you find an item in a table or range of cells vertically by their row. Four arguments comprise the syntax of the VLOOKUP function; the arguments are the value you want to use as a reference, the range or table of cells that hold the value you seek, the column number for your return value, and whether you want an exact or approximate match. Respectively, these arguments in the VLOOKUP formula are lookup_value, table_array, col_index_num, and [range_lookup].

There are two ways to input formulas in Excel: You can type the formula directly in the cell or use Excel’s Formula Wizard.

Follow these steps to use Excel’s Formula Wizard for a VLOOKUP function.

• Click the Formulas tab.

• When the Insert Function box appears, type vlookup into the search box, and click Go . Click OK .

The Functions Argument box will then appear, which walks you through inputting the VLOOKUP formula.

How VLOOKUP Works

To understand how VLOOKUP works, let’s use your favorite coffee order. The menu for your coffee shop is a good example of how you often perform a version of VLOOKUP in your head, scanning across from your drink of choice to the price under the preferred size of your drink.

In Excel, VLOOKUP scans the cell in the data table and looks for the data and the location you specify. When you hone in on the Hot menu, you see that there are three columns (sizes): 12 oz., 16 oz., and 20 oz. If you were ordering a 16-oz. chai latte, you would scan down the column of drinks until you find the chai latte, then across the row until you stop at the price in the appropriate (16 oz.) column. From there, you would determine that the drink costs $3.39. This process is not much different from what happens in Excel using the VLOOKUP function.                                             

VLOOKUP Example: How to Perform an Exact Match

We’ll use the same coffee example in Excel. Download the Excel Sample Data file to follow along. A note about the sample file: All the data you need for the majority of the examples explained throughout this tutorial are included in the file. Simply scroll through the tabs to find the corresponding example. Some worksheets already have the VLOOKUP formulas completed in the cells noted in the tutorial. To do it yourself, delete the existing formula and enter the one in this tutorial.  

In the Excel worksheet, you can scan down the column to find chai latte, then across to the column for 16 oz. However, because the majority of Excel spreadsheets will not be a simple coffee order, Excel provides functions to help you look up your data. In this example, you could use VLOOKUP to determine the cost of your 16-oz. chai latte. Follow these steps to perform an exact match VLOOKUP in Excel.

• On the worksheet, you see the same Hot menu from the coffee shop blackboard image above. Use the VLOOKUP function, and click cell B19 , where you will enter your formula.

• The next argument is col_index_col. This is the column number in the data table that you are scanning to get to the row hosting the piece of data you defined in the first argument.  In other words, in the first argument, you told Excel what data piece it was looking for: in this case, “Chai latte”. This defines where Excel should start. In the second argument, you told Excel the parameters of the data table. Now that you’ve defined the data table parameters, you are telling Excel which column to scan for your answer. In this case, enter 3. This is the third column from the lookup values. The lookup values must always go in the first column. Then type a comma (,) .

• Type an asterisk ( * ) after your drink order. The asterisk tells Excel to disregard any characters after “Chai latte”. Now, Excel returns the correct price.

This example was an exact match VLOOKUP and returned the exact value you were seeking — not an approximation.

The fourth argument in the VLOOKUP formula, [range_lookup], is a Boolean argument, meaning that you can give it a TRUE/FALSE value or representation of a TRUE/FALSE value such as 1/0. In this formula, FALSE is an exact match, and TRUE is an approximate match.

The argument name, [range_lookup], may be misleading to many users. The approximate match does not work well with text strings and is mostly meant for strings of numbers, especially when you are looking for a value from within a range. For this type of example, you would perform an approximate match VLOOKUP.

VLOOKUP Example: How to Perform an Approximate Match

The Bonus Example worksheet in the Excel Sample Data file is an example of how to calculate sales bonuses using VLOOKUP. We added the commission table to the worksheet as a reference to show what commission percent each salesperson has earned for the quarter. Bonuses in this company start after an employee reaches $40,000 in sales and bump up percentage points as they reach each threshold. Note the sales column in the reference table is arranged in ascending order, which is important for a lookup of this type.

Follow these steps to perform an approximate match VLOOKUP in Excel.

1.     Click the Bonus Example worksheet tab.

2.    Click cell C2 , and type =VLOOKUP( in the formula bar. This is your first VLOOKUP formula. The tool tip bar appears when you type this formula introduction.

3.     Type B2 (the value that Excel will use as a reference in the table, called the lookup_value) for the first argument in your VLOOKUP. It is highlighted in the same color as the argument it represents in your formula. Between each argument, type a comma ( , ).

4.     The second argument is table_array, which is the data table Excel uses to look up your values. You can either highlight this table manually by clicking the first cell and holding down your mouse until you have a complete selection, or you can type the range F4:G10 . Type a comma ( , ).

5. The third argument is col_index_num, which tells Excel the column in your data table to find the return data. Type 2 for this value. Type a comma ( , ).

At this point, you can type a close parenthesis ) and click Enter on the keyboard or complete the formula with a fourth argument. The fourth argument automatically defaults to the approximate match, but if you want the practice, go to the next step. 6. The fourth and final argument in the approximate match VLOOKUP is [range_lookup]. If you choose to enter this argument, type TRUE or 1 .

7. Type the close parenthesis ) and click Enter on the keyboard. The complete formula is =VLOOKUP(B2,F4:G10,2,1) .

As you can see, Mike Hayes earned a 6 percent commission this quarter because he is over the threshold of $150,000 but not yet at $200,000 in sales.

8.     To get the formulas into the rest of the sales team’s row, click cell C2 , right-click on the mouse, and click Copy . Right-click on the mouse, click Paste Special , click Formulas (or the Paste Formula icon), and highlight cells C3 through C10 by selecting the range C3:C10 . Click Enter on your keyboard.

This tiles the formula down the Commission % column.

Uh-oh! Some of your formulas have an #N/A error. This occurs because tiling your formula down the Commission % column also tiles the formula down your commission data table, and Excel is looking at blank cells for your table range. To fix this, add absolute references ($) to your formula.

9.     In your first formula in cell C2 , add $ before each column and row identifier in your data table range F4:G10, so it reads $F$4:$G$10 . An easy and quick way to do this is to highlight F4:G10 and click F4 on the keyboard. Click Enter on the keyboard.

10. Repeat step 8, copying C2 and pasting the rest of the column. 

Uh-oh! You still have an #N/A error. However, this error is due to Sylvie King not meeting the lowest threshold eligible for a sales commission in the data table, so Excel did not find a value for $23,000 in the data table. To fix this error, you have the choice to add a lower threshold to your data table — or hope that Sylvie meets $40,000 by next quarter!

VLOOKUP uses data in a defined data table range to look vertically down a column. It specifies to Excel that the data is in a column form. A similar function is HLOOKUP, which performs a lookup in a defined data table range with the identical syntax, but it tells Excel that your data is set up in rows, or horizontally.

Many people learn the basics of VLOOKUP and HLOOKUP early in their Excel education. You may also combine the two functions when your data table is in a matrix format. In other words, your lookup values should be on the top and left side of your table. This is a two-dimensional lookup, using two pieces of information.

How to Build a Structured Reference Table

Excel data tables help you organize your data. After you build tables in Excel, they automatically resize and incorporate additional data in your formulas and keep your formulas consistent as they automatically populate. Named data tables give you an edge while building and locating formulas in large workbooks.

Follow these steps to build a structured reference table in Excel.

• Click the Insert tab, and click on any cell outside of the data table. Click Tables and Table , or use the Ctrl + T shortcut on the keyboard. Note: This information is already filled out in the sample data file; to create a new table follow these steps in a blank sheet.

The Create Table dialogue box appears.

If your data table has headers, ensure that you click the My table has headers check box. Review the parameters of the table to ensure that all your data is included. Click OK. The data table autoformats with the alternate fill color.

To add a table name, click the Design tab (in some versions of Excel, this will appear in the Table tab). Under T able Name , type the name StructuredReference . The name must start with either a letter or underscore (_), must not have a space or special characters, and must not conflict with an existing name in the workbook.

Once you name the table, you can add more headers, which are automatically formatted to match the rest of the table.

VLOOKUP Example: How to Create a Lookup Table

You can also create a lookup table, in a process similar to creating any table in Excel. Follow these steps to create a VLOOKUP table.

• Enter headings in the first row. This example already has two headers, Name and Sales QTD , and data entered into the table.
• Column A, the first column, has and should have the reference values. In this example, the reference values are the names of the sales staff.
• If you have other data in this worksheet, such as additional data tables, leave at least one blank row on the bottom of the table and one blank column on the right of the table. This separates the lookup table from other data.  

How to Create a VLOOKUP for Combined Values

You will encounter some tables with values in the lookup column that are not unique. For example, the table below lacks a unique value for Shirt in the first column, as there are three listings. However, the second column lists three different sizes ( Small, Medium , and Large ). If you were to order a shirt, you would need the size to specify the price. In reality, the company that sells these products has only one record for each product and size combination.  

Follow these steps to create a VLOOKUP for combined values. The Excel Sample Data file has pre-existing data. This tutorial shows you how to turn that data into a table that Excel recognizes.

• Copy cell A3 and paste the formula in all the remaining cells in column A.

This creates a unique value for each entry in column A and allows you to type and use a VLOOKUP function for your data. The VLOOKUP formula for the price of each unique product (in this formula, we are looking for the price of a Shirt-Small) in column A is as follows:

=VLOOKUP(F4,A2:D14,4,FALSE)

To see if it works, click on any cell outside the table on the Excel Sample Data file sheet, copy the VLOOKUP formula into the formula bar, and click OK .

For a Shirt-Small, your return value is $15.99.

VLOOKUP Examples: Beginner Formulas

The following are basic concepts and examples in VLOOKUP that will work with versions of Excel 2007 and later. After you master these concepts, you can then move to more advanced material and make use of the power of VLOOKUP.

How to Do VLOOKUP from Another Worksheet

In reality, very few VLOOKUP formulas are used to look up data in the same worksheet. Most VLOOKUPs pull data from another worksheet.

Follow these steps to perform a VLOOKUP from another worksheet.

1. Click on the VLOOKUP Separate worksheet1 worksheet tab.

This data table has the product list downloaded from an accounting program.

2. Click on VLOOKUP Separate worksheet2 worksheet tab.

This worksheet has the lookup box. You will draw from the VLOOKUP Separate worksheet1 as the data table to populate this worksheet’s lookup box.

3. Click on cell C4 in VLOOKUP Separate worksheet2 , and type the following formula:

=VLOOKUP(B3,'VLOOKUP Separate worksheet1'!A2:D14,4,FALSE)

Let’s break down this formula:

When you get to the second argument, table_array, you are pulling data from the first worksheet, VLOOKUP Separate worksheet1. To do so, you must tell Excel that your data is on another worksheet, and for the syntax, you’ll start with the name of the worksheet surrounded by single quotation marks (‘), followed by an exclamation point (!), then the cell range:

You can also manually go to the other worksheet and designate the table_array by selecting the range of cells. Excel will automatically recognize that you are on another worksheet and add the correct syntax.

The last two arguments are the same as you’ll usually find in a VLOOKUP. In this case, they are:

4.  Click Enter on the keyboard.

How to Do VLOOKUP from a Different Workbook

You will also commonly do a VLOOKUP from another workbook.

Follow these steps to execute a VLOOKUP from another workbook:

1.  Click on the VLOOKUP Separate workbook1 worksheet tab in the VLOOKUP2.2 Sample File Excel file that you can download here .

As source data, this worksheet has product data for a clothing manufacturer.

2.  Open the VLOOKUP2.2 Excel file. There is only one worksheet tab: VLOOKUP Separate workbook2 . For this function, both workbooks should be open while you are writing the formula. After the formula has been written, the source workbook does not need to be open. Excel will update its location after you close it in the formula.

This worksheet has the reference data and the lookup box, and it draws data for the VLOOKUP from the first workbook and worksheet.

3.  In cell C3 of the VLOOKUP Separate workbook2 of VLOOKUP2.2 workbook, type the following formula:

=VLOOKUP(B3,'[VLOOKUP2 Sample File.xlsx]VLOOKUP Separate workbook1'!$A$2:$D$14,4,FALSE)

When you get to the second argument, table_array, you are looking to pull data from the first workbook, VLOOKUP Separate workbook1 . To do so, you must tell Excel that your data is on another worksheet and in another workbook. For the syntax, you’ll add the name of the workbook in brackets [] , then the name of the worksheet with an exclamation point ( ! ) before the cell range. Both the workbook name and the worksheet name are surrounded by single quotations ( ‘ ):

You can also manually go to the other workbook and designate the table_array by selecting the range of cells for the lookup. Excel will automatically recognize that you are on another workbook and add the correct syntax. When you close the lookup table workbook, your VLOOKUP formula will still work, but it will display the full path for the lookup workbook, as shown below:

4. Click Enter on the keyboard.

VLOOKUP Example: Combining Data Sets

If you want to combine data sets, a piece of data must anchor them both. In other words, both sets of data should have at least one piece of data in common, so they can cross from one table to the other.

The following example includes data from the American Community Survey (ACS) on educational attainment in the United States. Age and education levels stratify the data. There are two separate tables, downloaded separately from ACS: one for males and one for females. Since ACS stratifies its data the same way, it is possible to combine these Excel tables to further organize it for your reporting needs.

Excel can help do this with a VLOOKUP, but only because both data sets have a common column. Even if the order of the column was sorted differently, it would not matter, because the data is still present in the first row for each table.

How to Combine Data Sets in Excel

Follow these steps to combine data sets in Excel.

1.  For this exercise, there are two worksheet tabs, Education US-Males and Education US-Females , in the Excel Sample Data file. Note: On Education US-Males, we added extra column headers so that you can include data from the Education US-Females worksheet. Again, the data on the second sheet does not have to be ordered in the same way if the row headers are present on both sheets in the first column.

2.  On the Education US-Males worksheet, click on cell J3 and type the following formula:

=VLOOKUP(A3,'Education US-Females'!A2:I35,6,FALSE)

Here’s the breakdown of the formula:

This formula matches the Females Estimate column in the Education US-Females worksheet, which is column 6 in the Education US-Males worksheet. You also need columns 7, 8, and 9. The formulas for loading these into your new table are exactly the same, except for the column numbers in argument three.

3.  In cells K3, L3, and M3 Education US-Males worksheet, type the following formulas, respectively:

K3=VLOOKUP(A3,'Education US-Females'!A2:I35, 7 ,FALSE)

L3=VLOOKUP(A3,'Education US-Females'!A2:I35, 8 ,FALSE)

M3=VLOOKUP(A3,'Education US-Females'!A2:I35, 9 ,FALSE)

As you can see, the underlined numbers are the only changes to the formula, and they refer to the column number where the data is drawn.

4.  To keep the data consistent for the next step, you must add absolute references to the data table (table_array). To do so, click on your first formula in cell J3 , and select and highlight the third argument, 'Education US-Females'!A2:I35 . On the keyboard, click the F4 key. One click adds the absolute reference ( $ ) in the correct place. Click Enter on the keyboard.

5.  Perform step 4 for cells K3, L3, and M3 . Now your cells have absolute references built in, and you can apply their formula to the remainder of your data table.

6.  Select and copy cells J3 through M3 .

7.  Paste these cell formulas into the other rows of your table using either the Ctrl + V shortcut or by right-clicking on the mouse and clicking Paste Formulas . With the latter method, you keep the formatting intact.

At this point, your table is complete. You can play with the table and titles, ensuring that they are accurate for your needs. You can also turn the whole data table into an Excel “table” for future use.

VLOOKUP Formulas with Wildcard Characters

In VLOOKUP formulas, you can use wildcard characters to help look up values or spellings you are unsure of:

• Use a question mark (?) to match any single character.
• Use an asterisk (*) to match any sequence of characters.

How to Use Wildcard Characters in VLOOKUP

Follow these steps to use wildcard characters in VLOOKUP.

1.  Click on the Wildcards tab in the Excel Sample Data file.

This worksheet has a table of sales team members and their quarter-to-date (QTD) sales. There is a Sales QTD lookup box based on the salesperson’s name. Wildcard characters can help in the following scenarios:

Look up the salesperson’s name starting or ending with certain characters. In this scenario, you are not sure of the exact spelling of Mike Hayes’ name. You can click cell D4 (Name lookup box) and type Mike H* . Excel recognizes that the asterisk (*) stands for several missing letters.

You could also add the asterisk to the beginning of Mike’s name to yield the same result or use several asterisks to replace letters you are unsure of.

You could also add the wildcard to your VLOOKUP formula. For example, instead of entering your salesperson’s name in cell D3, you could add what you know into the VLOOKUP formula.

Troubleshooting the VLOOKUP Formula

Excel compares your reference value (lookup_value) with the data in the table and matches it, before scanning to your return value. If the value is not present, you will receive an #N/A error. However, even if your value is present in the table, there are still three possible reasons you could get an error:

• A Mismatch in Format: Either your reference value or the table data is formatted as a number and the other is formatted as text. You can fix this error by reformatting so that they match.
• Extra Spaces: Your reference or your table data has leading, lagging, or embedded spaces. To check for extra spaces in a cell, use the LEN function, which returns the number of characters in a cell. To correct for excess leading or lagging spaces, use the TRIM function.
• Other Characters: If the TRIM function does not solve your problem, you could still have other characters in either the reference or data table. Functions that help solve this problem include SUBSTITUTE or CLEAN, or you can add macros. Use SUBSTITUTE to change from characters like the forward slash to blank spaces or empty strings. Use CLEAN to remove nonprinting characters.

Problems When Sorting the VLOOKUP Formula

Initially, your VLOOKUP may return the correct results. But sometimes you may start seeing incorrect results after you sort the data table. This problem is not unique to VLOOKUP, as it can be found in other lookup functions such as INDEX-MATCH and HLOOKUP. To fix this problem, review your formulas and ensure that they are referencing the correct worksheet. This is an Excel bug that is easy to fix.

Excel 2013 introduced the FORMULATEXT function. Adding FORMULATEXT to the side of your cells enables you to see the formulas in their entirety and to check for errors.

The formulas for the data table are in column C.

• Click on a cell in row E or F (you can use any cell that doesn’t already have data).
• Type =FORMULATEXT and then click on the cell with the formula you want to see.

Now you can take a closer look at the formula and see what needs to be changed without affecting the original cell.

Excel VLOOKUP: Tips, Rules, and Troubleshooting

This review of the Excel VLOOKUP basics will help you build up to the intermediate material in the next sections. Some details to remember:

• You can sort your tables in any way, but your left column should not have duplicate values.
• Use absolute cell references ( $ ) in your table_array argument to ensure that you can copy and paste your formula to other cells.
• Click F4 to add absolute references to your table_array.
• VLOOKUP formulas read from left to right.
• In approximate match lookups, make sure your lookup value is larger than the smallest value you have identified. Otherwise, you will return an #N/A error.
• In approximate match lookups, always sort the data in ascending order in the data table (table_array).
• Values in VLOOKUP formulas are not case-sensitive, so you do not have to worry about uppercase or lowercase when typing formulas.
• Your col_index_num cannot be less than one (1). One signifies the first column.
• VLOOKUP can only look to the right in its search.
• The last argument, [range_lookup], is automatically set to TRUE (approximate match), unless you specify it as FALSE (exact match).
• The VLOOKUP formula works the same in all versions of Microsoft Excel.
• The #N/A error means that Excel cannot find the value you are seeking.
• The table data you are searching for may be on the same worksheet, on a different worksheet, and even in a different workbook.

VLOOKUP Examples: Intermediate Formulas

As you gain more experience with VLOOKUP, you will want to perform more complex functions. Before you move on, review the basic syntax and usage of VLOOKUP by reading this article .

How to Combine VLOOKUP and HLOOKUP Functions

Follow these steps to combine VLOOKUP and HLOOKUP functions.

1.  Click the Medals Example worksheet tab in the Excel Data Sample File .

This worksheet has a matrix of data with headers in both the row across the top and the column along the left. This is a fictitious table of gold, silver, and bronze medals earned in the 2012 Summer Olympics by each country. To the right side of the data table are both VLOOKUP and HLOOKUP boxes to enter the country and type of medal. Type the VLOOKUP with a nested HLOOKUP formula in the return value box ( F8 ).

2.  In the vertical lookup box, type Germany , and in the horizontal lookup box, type Bronze . This gives you values to follow in this example.

3.  Insert a blank row above row 5. Since VLOOKUP is only capable of referring to a number in the col_index_num, type numbers 2, 3, and 4 in cells B5, C5, and D5 , respectively.

Type =VLOOKUP( in cell F8 to signal to Excel that you are starting a VLOOKUP function.

4.  As with the earlier VLOOKUP formula example, add the first two arguments separated by commas below:

So far, your formula is   =VLOOKUP(F5,A3:D28 .

5.  Add the second function in the third argument, col_index_num. Instead of inserting a column number, add HLOOKUP as a nested function in a new set of parentheses, like a formula within a formula. Type HLOOKUP( to signal to Excel that you are starting another function.

6. Add the four HLOOKUP functions:

7.  Add the last VLOOKUP argument, FALSE .

8.  Click Enter on the keyboard.

How to Combine VLOOKUP with MATCH

Combining the VLOOKUP and MATCH functions enables you to construct a flexible lookup formula that is easier to copy across a worksheet. The MATCH formula replaces col_index_num in the  VLOOKUP formula. This combination also helps prevent problems when you add new columns to the lookup table or if you rearrange the columns. This scenario is similar to using a VLOOKUP-HLOOKUP combination, except you do not need a proxy number for your row.

Follow these steps to perform a VLOOKUP-MATCH.

1.  Click the VLOOKUP-MATCH Example worksheet tab.

This worksheet has a data table that is a matrix with headers in both the top row and the first column. There are VLOOKUP and MATCH boxes to help you visualize where the formula draws its data. The return value box (F8) is where you will type the VLOOKUP with the nested MATCH formula.

2. Notice Germany in the VLOOKUP box and Bronze in the MATCH lookup box. This provides values to follow along with in this example.

3.  Like the VLOOKUP-HLOOKUP combination, VLOOKUP-MATCH replaces the third argument (col_index_num) in the VLOOKUP formula. This is a nested MATCH function. The overall formula in cell F8 will be:

=VLOOKUP(F5,A4:D28,(MATCH(G5,A4:D4,0)),FALSE)

Excel performs the internal function (MATCH) first, then the VLOOKUP function.

The MATCH formula nested in the overall formula is:

Place this formula (nested) inside the VLOOKUP formula, replacing the third argument. The VLOOKUP formula is:

4.     Click cell F8 and type =VLOOKUP( .

5.  Type the first two arguments in the VLOOKUP formula, F5 and A4:D28 , separated by a comma.

6. Type the MATCH formula, (MATCH(G5,A4:D4,0)) , as the third argument in the VLOOKUP function.

7. Type a comma ( , ) and the last VLOOKUP argument, FALSE . Close the combined function with a parenthesis ). Click Enter on the keyboard.

As you can see, in this data table, Germany won 14 bronze medals. If you change both the values in the VLOOKUP and MATCH boxes, you consistently get the correct return value from the data table.

How to Combine IF and VLOOKUP Functions

When you add the IF function to the VLOOKUP, the cell remains blank instead of showing an error if your VLOOKUP does not have a value to return.

Follow these steps to add an IF function to your VLOOKUP.

1.  Click on the VLOOKUP-IF Example sample file.

This worksheet shows geography and the population from the 2010 United States Census. Without any value specified in the Geography VLOOKUP box, the return value in the Population box displays the #N/A error. If you type Guam in cell D4 , you would also receive this error.

The current formula for this VLOOKUP is =VLOOKUP(D4,A2:B55,2,FALSE) , which works well for values found in the data table.

2.  To combine the VLOOKUP and the IF function, wrap the VLOOKUP with the IF formula using the ISNA function to check for an #N/A error. The format for an IF function is:

IF(something is true, do something else, do something else because the comparison is false)

IF statements are logic tests that can have two different results based on whether a comparison is true or false. ISNA checks for a #N/A error. The logic for this IF statement is:

IF(there is an #N/A error, enter blank cell, otherwise enter the VLOOKUP result)

Your VLOOKUP statement is used twice in this formula. Type the following arguments in cell D4:

=IF(ISNA(VLOOKUP(D4,A2:B55,2,FALSE)),"",VLOOKUP(D4,A2:B55,2,FALSE))

Here is the IF formula broken down into its three arguments:

3. Click Enter on the keyboard.

Note: When entering a Geographic area that is not in the data table, the Population cell still returns a blank cell.

VLOOKUP Function Example: How to Combine IFERROR and VLOOKUP

In Excel 2007, Microsoft added the IFERROR function. Compared to the above IF-ISNA function, the IFERROR function requires less time and computing power. In the IF-ISNA function above, you ran the VLOOKUP once to see if it failed, and if it did not fail, you ran it again. The IFERROR function does the lookup only once and allows you to specify what it does if it fails.

The syntax of the IFERROR function is as follows:

=IFERROR(value,value_if_error)

So if Excel checks the value and returns an error, you can choose what value to display. Excel checks for the following errors: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL!.

Follow these steps to combine the IFERROR and VLOOKUP functions.

1.     Click the VLOOKUP-IFERROR Example worksheet tab in the Excel Sample Data file.

This worksheet shows the data from the 2010 Census by geography with no value specified in the Geography box. But the Population box shows an #N/A error.

2.  In cell E4 , wrap the current VLOOKUP formula with the IFERROR formula. The VLOOKUP formula is currently =VLOOKUP(D4,A3:B55,2,FALSE) . To wrap this with an IFERROR function, type the following arguments around the VLOOKUP:

=IFERROR(VLOOKUP(D4,A3:B55,2,FALSE),"Not Found")

Here are the two arguments in the IFERROR formula:

3.  Click Enter on the keyboard.

In this example, you have specified that the return value in case of an error is Not Found . The same return would apply if you entered another geography not listed in the table, such as Brazil .

How to Perform a Nested VLOOKUP with Multiple Criteria

One of the prime requirements of the VLOOKUP function is that your main data table and your lookup table have data in common. However, a table occasionally translates and acts as an intermediary for this data. See the following three tables:

Table 1 has the same column of data available in Table 2, so Table 2 can fill in the data for Table 3 as an intermediary. This is the purpose of nesting formulas: They can draw data from each other.

You must use VLOOKUP in an intermediary step to fill in the data you seek for your final table. Without Table 2, you cannot answer your query. Practically, you are using Table 2 to look up the matching ID to the Paycode and match the Paycode to the Name from Table 1. You will enter the VLOOKUP formula in the Paycode column of Table 3.

Follow these steps to perform a nested VLOOKUP with multiple criteria.

1.  Click on the Nested VLOOKUP worksheet tab in the Excel Sample Data file.

2.  Type your first VLOOKUP formula in cell B13 :

=VLOOKUP(VLOOKUP([@Name],Table1[#All],2,FALSE),Table2[#All],3,FALSE)

The Tables in this Excel worksheet have been named and saved as Tables 1, 2, and 3 . The practice of naming the tables changes the cell name in your formulas to the table titles. For example, cell A13 is [@Name] in the formula above. When you specify the whole of a table in Excel formulas, Excel writes it as Table1[#All] . Breaking down this formula, Excel performs the first VLOOKUP first. Nested formulas (those in parentheses) are calculated first. The first formula is:

=VLOOKUP([@Name],Table1[#All],2,FALSE

Without the additional VLOOKUP formula surrounding this VLOOKUP function, your return value would show 12394 . However, you wrapped the internal VLOOKUP with another VLOOKUP formula ( =VLOOKUP(Return value from first VLOOKUP function),Table2,3,FALSE ) . Therefore, your return value in cell B13 is A.

3.  Copy cell B13 and use Paste Formula to insert it into the reminder of the cells in the Paycode column for Table 3 . Click Enter on the keyboard.

Notice that the formula recognizes that the data is part of a defined table in Excel and does not change based on its cell. This shows the flexibility of Excel tables.

How to Use VLOOKUP to Get Second, Third, or Fourth Occurrences of the Lookup Value

You have already learned that Excel can return only the first matching value in a VLOOKUP. However, some Excel experts want to return other or all occurrences of the matching values. That is, they want to find each unique occurrence, though the data appears to be duplicate. For example, a database has the last several years of orders from a clothing company. Some of the orders are repeats, although they were done at different times and may have been for different items.

Use the following method to make each record unique and show these multiple occurrences. VLOOKUP returns only one value at a time to each cell, but you can make it so that each duplicate becomes unique.

Follow these steps to return other occurrences of the matching value.

1.  Click on the Multiple Occurrences worksheet tab in the Excel Sample Data file.

This worksheet displays a sales team and their individual orders to a merchandiser. Column A does not have unique values in every row. The merchandiser wants to know the subsequent purchases for each person.

2.  Add a new column to the left of the Name column ( Column A ) by right-clicking A at the head of the first column and clicking Insert. A new column A is created and the remainder of the table moves to the right.

3.  Type the following COUNTIF formula in cell A2 :

=MerchOrders[@Name]&COUNTIF($B$2:B2,B2)

Here’s the breakdown for the formula:

4.  Copy cell A2 and use the Paste Formula option to copy it down the table.

5.  Name column A Name-Unique .

6.  Click anywhere in the table to reveal the Design tab. Click the Design tab and click Resize Table . This action opens the Resize Table pop-up box. Now, you can add the first column into your predefined table by changing the first letter from B to A as shown below.     

7.    Click OK .

8.  Use the regular VLOOKUP formula to find your orders. In cell F4 , type:

=VLOOKUP(F3,Table6[#All],3,FALSE)

Note: The VLOOKUP formula would not be helpful for retrieving all the matching values for this at once.

How to Combine VLOOKUP and INDIRECT Functions

Using the INDIRECT function with the VLOOKUP returns a range. Use this shortcut if you do not want to constantly change the range of cells when updating your worksheets. In this combined function, you are pulling data from multiple worksheets that have the same formatting. For example, you could use this to combine reports. Normally, you can reference only one additional worksheet in VLOOKUP, but adding the INDIRECT function allows you to reference more worksheets, as long as they have an identical setup.

Follow these steps to combine the VLOOKUP and INDIRECT functions.

1. Click on the INDIRECT-Summary worksheet tab in the Excel Data Sample file. You also have two additional worksheets for this exercise: Store 1 Inventory and Store 2 Inventory . The naming of the worksheets is crucial, as you need to replicate those names exactly on the Summary worksheet.

2. On the INDIRECT-Summary worksheet, notice that the two column headers located in cells C2 and D2 match the names of the two worksheets you are pulling data from. You are looking to pull the different stores’ inventory numbers into one tracking sheet: INDIRECT-SUMMARY .

3. In cell C3 of the INDIRECT-Summary worksheet, type the following formula: =VLOOKUP($B3,INDIRECT("'"&C$2&"'!"&"$B$2:$C$8"),2,FALSE) The VLOOKUP formula is the same as the one you have been writing up to this point. The first argument (Lookup_value) is Product 1 with absolute references $ on the column letter, but not on the row number so that the formula can tile down but not across to the right. The INDIRECT function is located in the second argument (table_array). INDIRECT functions have two components: the reference text (ref_text) and A1 [a1]. The A1 component is optional and defaults to TRUE when omitted, meaning the text referenced is an A1-style reference. The reference text in this formula is: ("'"&C$2&"'!"&"$B$2:$C$8") This creates a range reference to the cells where we want to pull the data. Here is a breakdown of their functions:

• “ ‘ “: A single quote within double quotes (with spaces added for clarity) precedes the sheet name in case there are space characters in the worksheet name.
• &: Concatenate.
• C$2: The absolute reference to cell C2, which is the name of the worksheet we are referencing.
• “!”: The exclamation point surrounded by double quotes means we are looking for another worksheet.
• "$B$2:$C$8": The data table’s address for the data return we are seeking.​

5. Copy cell C3 and use the Paste Formula function in the remainder of the Summary table.

Using INDEX-MATCH Instead of VLOOKUP Sample

Many users like and are comfortable with VLOOKUP because they learned it early in their Excel education. It also performs the function they need. INDEX-MATCH is another lookup function when you nest functions. Here are three reasons why VLOOKUP is not the best choice for this use, and INDEX-MATCH is a better option for a lookup function:

• You need a formula that can look right to left.
• You have an enormous number of columns.
• Your spreadsheet computation is too slow.

The MATCH function searches for a specific item in a cell range and returns the position of that item. The syntax is MATCH(lookup_value,lookup_array,[match type]) . Use this function alone if you need the position of an item, but not the actual item.

The INDEX function provides the value within a table, range, or array. The syntax for an INDEX function is INDEX(array,row_num,[column_num]) . You can use this syntax in a two-dimensional lookup, as it gives you the opportunity to specify both the row and column location. In a nutshell, INDEX provides the value located at an exact position.

Follow these steps to use INDEX-MATCH for a lookup.

1.  Click on the INDEX-MATCH worksheet tab in the Excel Data Sample file.

As you can see, there is a list of the sales team’s quarterly sales to date (Sales QTD).

2.  To perform a lookup with INDEX-MATCH, type the following formula in cell E3 :

=INDEX(B2:B10,MATCH(D3,A2:A10,0),1)

Breaking down this formula, you have the MATCH nested in the INDEX formula. Excel performs this function first:

=MATCH(D3,A2:A10,0)

For the INDEX function:

=INDEX(B2:B10,MATCH,1)

VLOOKUP Examples in Google Sheets

Google Sheets is an Excel competitor, but allows more collaboration options as it was born in the cloud. Many functions in Excel are also in Google Sheets, and many Excel spreadsheets may be converted to Google Sheets and vice versa. However, you cannot use one program as a reference for the other. For example, in the case of VLOOKUP, you may not add an Excel spreadsheet reference into the formula on your Google Sheet and expect it to pull data from that Excel sheet.

Certain functions in Google Sheets work in the same way they do in Excel. VLOOKUP is one of those functions.

How to Perform a VLOOKUP from a Different Google Sheet

Follow these steps to perform a VLOOKUP from a different Google Sheet.

1.  Click on VLOOKUP Separate worksheet1 worksheet tab in the Google Sheet Sample file . (Note: Some formulas shown in this tutorial already appear in the Google Sheet Sample file .) For this exercise, you also have a VLOOKUP Separate worksheet2 to use.

2.  Type the following formula into cell C3 on VLOOKUP Separate worksheet2 :

To signify that you are looking at data from another sheet, the sheet name for the Range is surrounded by single quotes ( ‘ ) and followed by an exclamation point ( ! ).

Google Sheets does a few things differently than Excel:

• The formula in the overhead bar is colored to match the formula in the cell and remains that color.
• Google Sheets provides a return value as you type the formula in a call-out box above the formula.
• Google Sheets automatically saves your work and updates from multiple users in real time, and it supports VLOOKUP with wildcard characters.
• The formulas are defined slightly differently than those in Excel. They work in the same manner, but they are meant to be more intuitive to understand.

How to Perform a VLOOKUP Across Sheets in Smartsheet

Smartsheet is a work management platform that allows you to use higher-level functions across your sheets. Since Smartsheet does not store sheets in the traditional “workbook” format, you can reference any sheet you can access with your login address.  

Follow these steps to perform a VLOOKUP across sheets.

1.  Click on the Smartsheet VLOOKUP_Smartsheet1 and view. This sheet has information imported from a product database on products and their correlating prices. For this exercise, see also VLOOKUP_Smartsheet2 , which has designated space for a VLOOKUP for this example. In this sample, you want to look up data from VLOOKUP_Smartsheet1 and add it to VLOOKUP_Smartsheet2 using the VLOOKUP formula.

2.  With Smartsheet, you can either type in the whole formula or use the function button that Smartsheet located in the Numbers section on the sheet. In this example, you will use the Function button. Open the VLOOKUP_Smartsheet2 sheet and click on the Price2 cell, then click on the drop-down arrow next to the Function button. Click Advanced Functions and VLOOKUP . You can also find the VLOOKUP function under Functions/All Functions .

The VLOOKUP formula loads into the Price2 cell, with notes on syntax and examples.

The syntax for VLOOKUP in Smartsheet is as follows:

=VLOOKUP(search_value,lookup_table,column_num,[match_type]

The VLOOKUP method in Smartsheet is similar to that of Excel and Google Sheets, but is more intuitive and user-friendly. The currently required argument in the formula is highlighted. As with Excel, you can select the cells where your current argument is located or type the argument directly into the formula. Separate each argument by typing a comma ( , ).

3.  The formula for this function is the following:

=VLOOKUP(Product2,{PriceTable},4,false)

Select cell Product2 for the first argument and follow the selection with a comma ( , ).

4.  Once you type the comma after the first argument, the second argument is highlighted in the help pop-up box. To access a data table that is present on another sheet, you must click the Reference Another Sheet hyperlink.

The Reference Another Sheet pop-up box appears. Choose from all the sheets that your Smartsheet email allows you to access. You can choose data from any of these sheets. For this example, choose VLOOKUP_Smartsheet1 .

5.  Click VLOOKUP_Smartsheet1 . To select your table range, Smartsheet recommends clicking the column header of the columns you want to include. To select multiple columns, hold down the Shift or Ctrl key on the keyboard and click it with your mouse. Click and highlight all the cells. Once the cell range is highlighted, you can also change the name of the range in the Sheet Reference Name box to make it easier to read in your formula. For this exercise, replace VLOOKUP_Smartsheet1 Range 6 with PriceTable. When you name your range, remember that Smartsheet does not allow you to submit a name of a cell or table that is already in use in that sheet.

6.  Click Insert Reference on the Reference Another Sheet pop-up box. This action closes the pop-up and returns you to VLOOKUP_Smartsheet2 .

Since you are referencing a data table that comes from another sheet, the lookup_table reference is surrounded by curly brackets {} .

7.  You can type the last two arguments in this function manually. Close your second argument with a comma ( , ) and type 4, then false . The last argument must be lowercase.

8.  Type the close parenthesis ) and click Enter on the keyboard to complete this function.

For more information about VLOOKUP function across sheets, watch this video .

In Smartsheet, the following are best practices and tips for using formulas across sheets:

• Use cross-sheet formulas on any sheet you can access. Note: You must have at least editor access on the destination sheet and viewer access on the source sheet.
• Reference the entire column in your formula for accuracy. This way, if your data table changes, Smartsheet can recognize the parameters in your formulas, and the formulas will stay intact.
• Custom-name ranges in cross-sheet formulas: This practice allows you and colleagues viewing your formulas to better identify the ranges selected.
• Know your limits. A maximum of 25,000 cells is available for cross-sheet references in these formulas in Smartsheet. This is significantly larger than past cell links maximums, which were 5,000.
• Use cell links when calculations are not necessary. Cell links enable users to input data from a cell in one sheet into another sheet, without the benefit of a calculation. If you need to use calculations with your data to perform any number of higher-level functions, go with functions such as VLOOKUP, SUMIF, or COUNTIF.

Connect Data Across Your Work with VLOOKUP in Smartsheet

Empower your people to go above and beyond with a flexible platform designed to match the needs of your team — and adapt as those needs change. 

The Smartsheet platform makes it easy to plan, capture, manage, and report on work from anywhere, helping your team be more effective and get more done. Report on key metrics and get real-time visibility into work as it happens with roll-up reports, dashboards, and automated workflows built to keep your team connected and informed. 

When teams have clarity into the work getting done, there’s no telling how much more they can accomplish in the same amount of time.  Try Smartsheet for free, today.

Discover why over 90% of Fortune 100 companies trust Smartsheet to get work done.

VLOOKUP with numbers and text

Related functions .

To use the VLOOKUP function to look up information in a table where the first column contains numbers that are actually text, you can use a formula that concatenates an empty string ("") to the numeric lookup value, coercing it to text. In the example shown, the formula in H3 is:

where id (H4) and planets (B5:B13) are named ranges . With the number 5 in cell H2, the formula in H5 returns "Jupiter".

Note: This example is a workaround to the problem of mismatched numbers and text, which causes VLOOKUP to return an #N/A error. If there is no mismatch, the workaround is not necessary and you can use a normal VLOOKUP formula .

Generic formula

Explanation .

In this example, the goal is to configure VLOOKUP to perform a lookup in a table where the first column contains numbers entered as text, and the lookup value is a true number. This mismatch between numbers and text will cause VLOOKUP to return an #N/A error.

Typically, the lookup column in the table contains values that look like numbers , but are in fact numbers entered as text . When a true number is passed into VLOOKUP as the lookup_value , VLOOKUP returns #N/A, even though there appears to be a match. The screen below shows an example of this problem:

The cause of the #N/A errors is that the numbers in column B are actually text, so a lookup value of 5 fails, even though it seems like VLOOKUP should match cell B9 and return information about Jupiter. The formulas in H5:H7 are as follows:

All of these formulas return #N/A since they are all affected by the mismatch. One quick solution to the problem is to enter the lookup value in id (H4) as text instead of a number. You can do this by prefacing the number with a single quote ('). VLOOKUP will then correctly find the table and perform the lookup. A better solution is to make sure the lookup values in the table are indeed numbers. Once you have converted the first column to numeric values, the standard VLOOKUP formulas above will work.

However, if you don't have control over the table, you can modify the VLOOKUP formula itself to change the numeric lookup value to text in order to match the lookup table. You can do this by concatenating an empty string ("") to the lookup value inside VLOOKUP. The formulas below show what this looks like. The first formula is a standard VLOOKUP, the second formula shows the modified version:

The modified version will take care of the error:

If you aren't sure if the first column in the lookup table is text or numbers, use the ISTEXT function or the ISNUMBER function to test the values.

Both numbers and text

If you can't be certain when you'll have numbers and when you'll have text, you can cater to both by wrapping VLOOKUP in the IFERROR function and using a formula that handles both cases:

Here, we first try a normal VLOOKUP formula that assumes both lookup value and the first column in the tables are numbers. If that throws an error, we try again with the revised formula above. If that formula also fails, VLOOKUP will return an #N/A error as always.

Related formulas

• VLOOKUP two-way lookup

• VLOOKUP calculate grades

• Get employee information with VLOOKUP

• Merge tables with VLOOKUP

• VLOOKUP without #N/A error

Related functions

• VLOOKUP Function

The Excel VLOOKUP function is used to retrieve information from a table using a lookup value. The lookup values must appear in the first column of the table, and the information to retrieve is specified by column number . VLOOKUP supports approximate and exact matching...

• ISTEXT Function

The Excel ISTEXT function returns TRUE when a cell contains a text value , and FALSE if the cell contains any other value. You can use the ISTEXT function to check if a cell contains a text value, or a numeric value entered as text.

• IFERROR Function

The Excel IFERROR function returns a custom result when a formula generates an error, and a standard result when no error is detected. IFERROR is an elegant way to trap and manage errors without using more complicated nested IF statements.

Related videos

• How to use VLOOKUP

• How to replace nested IFs with VLOOKUP

• How to use VLOOKUP for approximate matches

• Why VLOOKUP is better than nested IFs

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.

Related Information

• 23 things you should know about VLOOKUP
• Core Formula

Get Training

Quick, clean, and to the point training.

Learn Excel with high quality video training. Our videos are quick, clean, and to the point, so you can learn Excel in less time, and easily review key topics when needed. Each video comes with its own practice worksheet.

Help us improve Exceljet

Your email address is private and not shared.

Excel Tutorial

Excel formatting, excel data analysis, table pivot, excel functions, excel how to, guided projects, excel examples, excel references, excel vlookup function, vlookup function.

The VLOOKUP function is a premade function in Excel, which allows searches across columns.

It is typed =VLOOKUP and has the following parts:

Note: The column which holds the data used to lookup must always be to the left.

Note: The different parts of the function are separated by a symbol, like comma , or semicolon ;

The symbol depends on your Language Settings .

Lookup_value: Select the cell where search values will be entered.

Table_array: The table range, including all cells in the table.

Col_index_num: The data which is being looked up. The input is the number of the column, counted from the left:

Range_lookup: TRUE if numbers (1) or FALSE if text (0).

Note: Both 1 / 0 and True / False can be used in Range_lookup .

How to use the VLOOKUP function.

• Select a cell ( H4 )
• Type =VLOOKUP
• Double click the VLOOKUP command
• Select the cell where search value will be entered ( H3 )
• Mark table range ( A2:E21 )
• Type the number of the column, counted from the left ( 2 )
• Type True (1) or False (0) ( 1 )
• Enter a value in the cell selected for the Lookup_value H3(7)

Let's have a look at an example!

Use the VLOOKUP function to find the Pokemon names based on their ID# :

H4 is where the search result is displayed. In this case, the Pokemons names based on their ID#.

H3 selected as lookup_value . This is the cell where the search query is entered. In this case the Pokemons ID# .

The range of the table is marked at table_array , in this example A2:E21 .

The number 2 is entered as col_index_number . This is the second column from the left and is the data that is being looked up.

An illustration for selecting col_index_number 2 .

Ok, so next - 1 (True) is entered as range_lookup . This is because the most left column has numbers only. If it was text, 0 (False) would have been used.

Good job! The function returns the #N/A value. This is because there have not been entered any value to the Search ID# H3 .

Let us feed a value to it, type H3(7) :

Have a look at that! The VLOOKUP function has successfully found the Pokemon Squirtle which has the ID# 7 .

One more time, type (H3)4 :

It still works! The function returned Charmanders name, which has 4 as its ID#. That's great .

COLOR PICKER

Report Error

If you want to report an error, or if you want to make a suggestion, do not hesitate to send us an e-mail:

[email protected]

Top Tutorials

Top references, top examples, get certified.

Stack Exchange Network

Stack Exchange network consists of 183 Q&A communities including Stack Overflow , the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Displaying multiple columns using VLOOKUP in Excel

Lets say in a worksheet, lets call this SHEET_2, I have student information.

On another sheet, lets call this SHEET_1, I want to be able to lookup a students ID number and have all the students marks from assignments, tests, etc to be displayed.

Lets say there are 5 different marks per student. On sheet1, I type in the students ID number, and on row 2 I want the mark of assignment 1 to be displayed, and the row below, row 3, I want the mark of assignment 2 to be displayed. How would I go about this using a single formula?

Currently I have =VLOOKUP(StudentID,Sheet_2,2,FALSE) to display assignment one

=VLOOKUP(StudentID,Sheet_2,3,FALSE) to display assignment two on the row below.

How could I combine this into a single formula where I don't have to have a separate col_index_num for each row?

• microsoft-excel
• worksheet-function

• Perhaps you could use ROW() instead of a number for the col_index_num ? –  Christofer Weber Dec 1, 2016 at 6:15
• 1 How is the data structured on sheet 2? –  fixer1234 Dec 1, 2016 at 7:41
• @fixer1234 column A has the student ID, Column B has the Students name, Columns C-H has grades of the students. I'll post an image later on if needed. –  user669017 Dec 1, 2016 at 17:02
• @fixer1234 I included an image of sheet 2 –  user669017 Dec 1, 2016 at 19:53
• Thanks for the replies, I think using ROW gave me my desired result –  user669017 Dec 1, 2016 at 22:55

Use the following:

(Column 2 would return the student's name, not Assignment 1's result.)

The trick to it is using the array constant {3,4} for the fields to return. It gives you both of them in a single formula.

TRANSPOSE() then takes the row form those results are given to you in and makes them a column of returns so that they fall in rows 2 and 3 for you (so presumably your entry in row 1, then these).

Of course, several other ways to do it as well, but this seems about the simplest.

You must log in to answer this question.

• Featured on Meta
• Incident update and uptime reporting
• Updates to the Acceptable Use Policy

Hot Network Questions

• What is the earliest known historical reference to Tutankhamun?
• Are Berkeley cardinals easier to refute in ZFC than Reinhardt cardinals?
• order of 2 modulo a power of prime
• What are some good references on the history of ethics in statistics?
• Is there a SETH (Strong Exponential Time Hypothesis) for CSP (Constraint Satisfaction Problem)?
• USB-C hub plug replacing
• "Decision in process" after the median number of days from submission meaning
• DM 1v1d us against CR9 Monsters
• Can a "You must use this property as primary residence for x years" clause be added to home-sale? Would it differ if the buyer were taking a loan?
• why are wind turbines installed slightly "nose up" as opposed to the blade disk being perpendicular to the ground?
• Maintaining a parallel fork of a project that contains the original authors' company name
• Use unicode text in tex document
• Does learning thorough statistical theory require learning analysis?
• Minimum voltage to register a 1 on 74HC devices
• Does it ever make sense to create one bulkified and one unbulkified logic route in Apex?
• Expand conditional in the outer specification for a tabularray table
• Shuffling two lists into each other
• Two notions of generalized quotient/substructure
• If 'SILVER' is coded as ‘LESIRU' and 'GOLDEN' is coded as 'LEGOND', then in the same code language how 'NATURE' will be coded as?
• A question about a phrase in "The Light Fantastic", Discworld #2 by Pratchett
• What potential issues may arise from using the Kiwi online travel agency?
• Are uVia, blind via, buried via and backdrilled via only relevant to high speed design?
• How to change the colour of a particle system
• Bat mitzvah - I'm female aged 50. Never been taught or had a bat mitzvah

Your privacy

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy .