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

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.

  1. Click the Formulas tab.
  2. Click the Insert Function icon on the top-left corner of the ribbon.

    VLOOKUP formula example

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


VLOOKUP insert function example

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


VLOOKUP functions argument


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.


VLOOKUP coffee example

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 with coffee data

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.

  1. Click the Coffee Example worksheet tab in your sample workbook.
    VLOOKUP data coffee example
  2. 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.
  3. Type =VLOOKUP (to let Excel know that you are creating a VLOOKUP formula) in the formula bar. Excel will open the tool tip box to help you keep track of the components of the formula. As you enter your formula, Excel bolds the portion you are inputting in the tool tip box to help you track the calculation.
    VLOOKUP formula excel example
  4. Type the first argument, lookup_value, as cell A19, which has the value you want to look up. By placing this value outside of your data table (menu), you can change the data and still have the correct results when you look up a value. Type a comma (,) between each argument.
    VLOOKUP first argument example
  5. Type the second argument, table_array. This is the entirety of your data table. You can manually type A1:D14, or you can use your mouse to highlight the cells on the table and Excel populates its parameters into the formula. Type a comma (,).
    VLOOKUP second argument example
  6. 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 (,).
  7. The last VLOOKUP argument for an exact lookup is [range_lookup]. Type FALSE or 0. Your lookup will now be exact.
    VLOOKUP false argument example
  8. Type a close parenthesis ) and click Enter on the keyboard.

    VLOOKUP N/A error example

    Uh-oh! As you can see from the example, you receive an #N/A error. This means the data you seek is either not where you told Excel to look or it is in the wrong format. In this case, Excel does not recognize the data present as the data it seeks because there is additional data in the cell: the choice of mate, masala, or rooibos.

    VLOKUP Excel error

    To account for other data in the cell, Excel has extra characters that help out. For example, if your data table is loaded from an accounting program or other software, it may add extra spaces to each cell. Excel recognizes these extra spaces as characters and could produce the #N/A error.
  9. Type an asterisk (*) after your drink order. The asterisk tells Excel to disregard any characters after “Chai latte”. Now, Excel returns the correct price.


VLOOKUP asterick

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.




VLOOKUP approximate match example

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.




VLOOKUP approximate match formula

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 (,).




Excel approximate match argument

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 (,).




VLOOKUP table array argument

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 (,).




VLOOKUP col index number example


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.





VLOOKUP approximate match true

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




VLOOKUP approximate match example


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.




Excel VLOOKUP paste special

This tiles the formula down the Commission % column.




VLOOKUP approximate match error

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.




VLOOKUP shortcut Excel

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


VLOOKUP fix errors

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 data table range

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.

  1. Click on the Structured Reference worksheet tab in the Excel Sample Data file.
    VLOOKUP structured reference
  2. 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.


VLOOKUP table example

The Create Table dialogue box appears.


VLOOKUP create table example

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.


VLOOKUP autoformat example

To add a table name, click the Design tab (in some versions of Excel, this will appear in the Table tab). Under Table 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.


VLOOKUP table name example

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


VLOOKUP format table name

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.

  1. Click on the Create Lookup Table worksheet tab in the Excel Sample Data file.
    VLOOKUP Excel lookup table
  2. Enter headings in the first row. This example already has two headers, Name and Sales QTD, and data entered into the table.
  3. 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.
  4. 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.






$           15.99



$           18.99



$           32.99



$           34.99



$           36.99



$           15.99



$           16.99



$           17.99



$           18.99



$           19.99



$           20.99


one size

$             5.99

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.

  1. Click the VLOOKUP for Combined Values worksheet tab in the Excel Sample Data file.
    VLOOKUP combined values Excel
  2. Insert a column to the left of the Product column to create the Product-Size column, which will combine both the product and size.
    VLOOKUP examples
  3. Click on cell A3 and type =B3&”-“&C3 in the formula bar.
  4. Copy cell A3 and paste the formula in all the remaining cells in column A.


VLOOKUP combined value Excel

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:


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 combined value Excel

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.




VLOOKUP worksheet example

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

2. Click on VLOOKUP Separate worksheet2 worksheet tab.




VLOOKUP worksheet example

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:






The vertical lookup reference

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:





'VLOOKUP Separate worksheet1'!A2:D14

The range of cells Excel looks through to find the lookup reference in the table

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:






The column number where the return data is located



The exact value


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.


VLOOKUP workbook example

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.


VLOOKUP multiple workbooks example

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)


Let’s break down this formula:






The vertical lookup reference

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 ():





'[VLOOKUP2 Sample File.xlsx]VLOOKUP Separate workbook1'!$A$2:$D$14

The range of cells Excel looks through to find the lookup reference in the table

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:


Excel VLOOKUP multiple workbook formula

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






The column number where the return data is located



The exact value


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.


VLOOKUP combine data sets example






VLOOKUP combine data sets Excel example

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:






The vertical lookup reference


'Education US-Females'!A2:I35

The range of cells Excel looks through to find the lookup reference in the table



The column number with the required data



The exact value defined



VLOOKUP Excel combine data sets 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.


Excel VLOOKUP formula example

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.


Excel VLOOKUP formula example

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.


VLOOKUP sample

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 data table Excel sample

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.


VLOOKUP wildcards example

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.


VLOOKUP wildcard example

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.


Excel example VLOOKUP wildcard

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.

Wildcard VLOOKUP D3

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.


VLOOKUP Excel 2013 example

The formulas for the data table are in column C.

  1. Click on a cell in row E or F (you can use any cell that doesn’t already have data).
  2. 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.


HLOOKUP Excel example

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.


HLOOKUP arguments example


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







Vertical lookup reference



Data table parameters



HLOOKUP Excel example

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.


HLOOKUP formula example Excel

6. Add the four HLOOKUP functions:






Vertical lookup reference



Row parameters in the data table



The row where the return value is located



An exact value



HLOOKUP formula example Excel

7.  Add the last VLOOKUP argument, FALSE.

8.  Click Enter on the keyboard.


VLOOKUP Excel HLOOKUP formula example

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.


VLOOKUP match example

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:


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

The MATCH formula nested in the overall formula is:






The lookup reference



The range of cells Excel looks through to find the lookup reference in the table

Match type


The first value exactly equal to the lookup value

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






The vertical lookup reference



The range of cells Excel looks through to find the lookup reference in the table


MATCH formula

The MATCH value that defines the column number



The exact value

4.     Click cell F8 and type =VLOOKUP(.


VLOOKUP match Excel

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


VLOOKUP match argument

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


VLOOKUP match formula

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


VLOOKUP match formula example

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.


VLOOKUP and match values

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.


IF Function Excel example

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.


IF Function VLOOKUP Excel example

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


IF Function VLOOKUP formula example

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:


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






If the VLOOKUP returns an #N/A error

Value if true

“ ”

Blank cell

Value if false


Use the VLOOKUP result

3. Click Enter on the keyboard.


IF Function VLOOKUP Excel example

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:


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.


IFERROR Excel example

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:






The value the VLOOKUP returns


“Not Found”

The value returned if the first argument returns an error

3.  Click Enter on the keyboard.

Return Value Not Found

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.

Return Value Not Found

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:


VLOOKUP multiple criteria example

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.


Nested VLOOKUP Excel multiple criteria

2.  Type your first VLOOKUP formula in cell B13:


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:







The vertical lookup reference, cell B13



The range of cells Excel looks through to find the lookup reference in the table, Table 1 cells A1:B10



The column number with the required data



The exact value defined


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.


VLOOKUP multiple criteria formula

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.


VLOOKUP multiple criteria formula example

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.


VLOOKUP lookup value Excel

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.


VLOOKUP lookup value example

3.  Type the following COUNTIF formula in cell A2:


Here’s the breakdown for the formula:






Refer to cell B3



Concatenate symbol



The range of cells to count unique values



The criteria that controls which cells are counted


VLOOKUP lookup value formula

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




VLOOKUP lookup value Excel example

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.


VLOOKUP resize table

7.    Click OK.


VLOOKUP sample resize table example

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



VLOOKUP function example

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.




Excel Indirect functions example




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.




VLOOKUP indirect functions example

3. In cell C3 of the INDIRECT-Summary worksheet, type the following formula:


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:


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.
  • &: Concatenate.
  • "$B$2:$C$8": The data table’s address for the data return we are seeking.​

4. Click Enter on the keyboard.

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


Indirect functions VLOOKUP Excel example

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.


Excel index match example

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:


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







Where the reference value to lookup is located



The data table where the lookup value may be found

[Match type]


This means you want an exact match

For the INDEX function:







Where the reference value to lookup is located



The value returned by the MATCH function – which is the row number of the reference data



The column number of the reference data

3.  Click Enter on the keyboard.


Excel Index match formula example


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.


VLOOKUP Google sheet

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

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

Let’s break down this formula:






Where to look up the search value


'VLOOKUP Separate worksheet1'!A2:D14

The location of the table data for Google Sheets to sort through



The column number to find the data



The exact match that is not sorted

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 sheet VLOOKUP formula

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.


How to use VLOOKUP Smartsheet example


Using VLOOKUP example Smartsheet

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.


Smartsheet VLOOKUP function

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


How to use VLOOKUP formula Smartsheet

The syntax for VLOOKUP in Smartsheet is as follows:


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:







The value you are looking to match



The data table that your information is drawn from



The column number in the data table that you reference  for the return value



This is an exact match and must include a space after the comma

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


VLOOKUP Smartsheet advanced function example

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.


Smartsheet VLOOKUP Reference Another Sheet

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.


VLOOKUP Smartsheet choose sheet

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.


VLOOKUP range Smartsheet example

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


VLOOKUP table Smartsheet

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.


how to use VLOOKUP Smartsheet

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.

Try Smartsheet for Free Get a Free Smartsheet Demo