How to Use Excel VLOOKUP - Examples

Get started with Excel VLOOKUP function examples. Videos, free workbooks, easy steps. Use VLOOKUP to find product price, or student grades. See how to find and fix VLOOKUP formula problems with troubleshooting.

Video: Find Product Price with VLOOKUP

Watch this short video, to see how to make a VLOOKUP formula, to find a product price. The written steps are below the video.

Get the Product Price Lookup sample file to follow along with the video (download file #4).

Product Price VLOOKUP

In this example, there is an Excel workbook with 2 main sheets:

• Products
• Order

On the Products sheet, there is a small lookup table with 2 columns of product information:

1. Product Name
2. Product Price

On the Order sheet, you can type a product name, and a VLOOKUP formula finds the price for that product

Make Product Lookup Table

Here are the steps to make the product lookup table.

First, enter the product information on the worksheet

1. In row 1, type the headings for the lookup table:
   • A1 - Product
   • B1 - Price
2. The VLOOKUP function looks for values in the first column at the left of a lookup table
• Type the four product names in the column A (Paper, Lamp, Desk, Pencil)
3. The VLOOKUP function returns information from columns to the right of the lookup values
• Type the product prices in column B (5.00, 15.00, 75.00, 0.50)

Format as Excel Table

Next, format the product list as a named Excel Table.

• Select any cell in the list
• On the Excel Ribbon's Home tab, click Format as Table
• Select the format that you'd like
• Make sure the product list range is shown in the first box
• Add a check mark for "My table has headers"
• Click OK

Rename the Excel Table

Next, change the default name that Excel gave to the new table.

• Select any cell in the table
• On the Excel Ribbon, click the Table Design tab
• At the left, in the Table Name box, replace the default name (Table1), with a meaningful name: tblProduct

Make an Order Form

Next, set up a simple order form on the worksheet named Order.

• In cell B2, type the heading, Order Form
• In A4 to A7, type labels - Product, Quantity, Unit Price and Total Price
• Select B4 to B7, and add borders
• Add a light green fill colour to B4 and B5, to show they're for data entry
• Add a light grey fill colour to B6 and B7, to show they have formulas

Formulas will be added to cells B6 and B7 in the next steps.

Add Product Price VLOOKUP Formula

To find the Unit Price for a product, you'll create a VLOOKUP formula in cell B6.

First, enter a product name and quantity in the order form

• In cell B4, type Desk
• In cell B5, type 10

Next, select cell B6, and type this VLOOKUP formula, then press Enter:

• =VLOOKUP(B4,tblProduct,2,FALSE)

VLOOKUP Arguments

There are 4 arguments in the VLOOKUP formula:

1. lookup_value: What value do you want to look up?
• In this example, the product name is in cell B4
2. table_array: Where is the lookup table?
• Our product information is in the table named tblProducts
3. col_index_num: Which column has the value you want returned?
• In this example, the product prices are in column 2 of the lookup table.
4. [range_lookup]: Do you want an approximate match for the product name (TRUE) or an exact match (FALSE) ?
• This example has FALSE as the last argument, so it will return an exact match.
• If the product name is not found, the result will be #N/A.

Add Total Price Formula

The final step is to add a Total Price formula -- the quantity x the unit price.

Select cell B7, and type this formula, then press Enter:

• =B5*B6

That formula multiplies the quantity (B5) by the unit price (B6), to calculate the total price.

VLOOKUP - Approximate Match

In some situations, an approximate match is preferred, so several values will return the same result. For example, when grading student papers,

• scores of 85 or over should receive an A grade
• scores from 70 to 84 should receive a B grade
• and so on

By using approximate matches, we won't need to create a lookup table with every possible score, from zero to 100. We only need 5 rows in the lookup table, as shown in this screen shot.

To view the steps for creating this formula, please watch the VLOOKUP video shown below. The written instructions are below the video.

In this example, the lookup table is created on a sheet named Grades. To create the lookup table, enter the minimum score for each grade in column A. Enter the matching Grade in column B. Sort the Scores in Ascending order.

Cells A2:B6 were named GradeList.

The scores are entered on a sheet named Report Card, where a VLOOKUP formula calculates the grade.

1. On the Report Card sheet, in cell B4, enter the score 77.
2. In cell C4, enter the VLOOKUP formula:
     =VLOOKUP(B4, GradeList,2, TRUE)
   
3. Press the Enter key, and the grade for English -- B -- is returned.

In the screen shot below, the formula has been copied down to row 6, and the you can see the formula in cell C6.

Combine VLOOKUP With MATCH

Instead of typing the column number into a VLOOKUP formula, use the MATCH function to find the correct column in the lookup table. This has a couple of benefits:

• Makes the formula flexible, so it's easier to copy the formula across a worksheet.
• Can prevent problems if new columns are added in the lookup table, or if the lookup columns are rearranged.

This video shows the steps, and there are written instructions for another example, below the video.

VLOOKUP With MATCH for Order Details

In this example, a VLOOKUP formula will return the order details from a lookup table, based on the order ID number. Here is the lookup table, named tblOrders.

NOTE: This example is in Sample Workbook #1, on the sheet named OrdersMATCH.

Here is the worksheet with the VLOOKUP formulas. We want the Region, Order Date and Order Amount for each order, so 3 VLOOKUP formulas are needed.

If the column numbers are typed in the formula, a different formula is needed in each column:

• Region: =VLOOKUP($B6, tblOrdersALL, 2,0)
• OrderDate: =VLOOKUP($B6, tblOrdersALL,3,0)
• OrderAmt: =VLOOKUP($B6, tblOrdersALL,4,0)

The MATCH Function

Instead of typing the column number in the VLOOKUP formula, we can use the MATCH function. The MATCH function finds the position of an item in a list, and returns the position number.

In the screen shot below, the MATCH formula returns 2 as the position of "Region", in the heading cells (A1:D1) for the lookup table.

=MATCH(C5, Orders_ALL!$A$1:$D$1, 0)

NOTE: For this technique to work correctly, the headings on the VLOOKUP sheet must match the lookup table headings exactly. To ensure an exact match, the VLOOKUP heading cells are linked to the lookup table heading cells.

Add MATCH to VLOOKUP

To add the MATCH function to the VLOOKUP formula, just replace the typed column number

=VLOOKUP($B6,tblOrdersALL,2,0)

with the MATCH formula:

=VLOOKUP($B6, tblOrdersALL, MATCH(C5, Orders_ALL!$A$1:$D$1, 0),0)

Copy the VLOOKUP Formula Across

Now, instead of needing a different formula in each column, you can copy the formula across, and use the same formula in all the columns. In each column, it will refer to the heading cell in that column, and find its position in the lookup table.

NOTE: If you are filling the formula across columns with different formatting, follow these steps:

1. Select the cell with the formula that you want to copy
2. Point to the fill handle on the selected cell (small square at the bottom right corner)
3. Press the RIGHT mouse button, and drag across to the last cell that needs the formula
4. Release the mouse button, and click on Fill Without Formatting

Combine IF and VLOOKUP

You can use an IF formula with a VLookup formula, to return exact values if found, and an empty string if not found.

To see the steps for setting up the IF and VLOOKUP formula, you can watch this short video. The written instructions are below the video.

To hide errors by combining IF with VLOOKUP, follow these steps:

1. On the Invoice sheet, in cell A8, enter the product code A28. If the VLookup formula in cell B8 has FALSE as the fourth argument, the result is #N/A, because there is no exact match for the product code in the lookup table.
2. Wrap the VLookup formula with an IF formula (in this example the product list has been named), using the ISNA function to check for an #N/A error:

  =IF(ISNA(VLOOKUP(A8, ProductList,2,FALSE)), "",VLOOKUP(A8, ProductList,2,FALSE))

3. Press the Enter key, and cell appears blank. Because no exact match was found, the VLookup formula returned an #N/A, so the ISNA function result is TRUE. The IF formula converted this to an empty string.

If the lookup table contains any blank cells, a VLOOKUP formula will return a zero, instead of a blank cell. You can use nested IFs to handle the #N/A results, and the empty cell results. For example:
      =IF(ISNA(VLOOKUP(A8, ProductList,2,FALSE)),"",
            IF(VLOOKUP(A8, ProductList,2,FALSE)="","",
              VLOOKUP(A8, ProductList,2,FALSE)))

Combine IFERROR and VLOOKUP

Thanks to Chip Pearson for suggesting this formula.

In Excel 2007, a new function, IFERROR, was introduced. You could use an IFERROR formula with VLookup to check several tables for a value, and return the related information when found. In this example, three regions, West, East and Central, have order sheets. On each sheet is a named range -- OrdersW, OrdersE and OrdersC.

On a sheet named Orders, you can enter an Order ID, then use a VLOOKUP with IFERROR to check each named range, and view the information about the selected order.

1. On the Order sheet, in cell B6, enter a 4 as the OrderID. That order was placed in the Central region.
2. To simply check the East region's table, the VLOOKUP formula in cell C6 would be:
     =VLOOKUP(B6, OrdersE,2, FALSE)
   
3. Press the Enter key, and the VLOOKUP formula returns an #N/A, because Order ID 4 is not in the East regions order table.
4. Because an order could have been placed in any of the three regions, you need a formula that will check each table.
   If the order ID is not found in the first table, the formula should check second table. If the order ID is not in the second table, it should check the third table. If the order ID isn't in the third table, then a "Not Found" message should appear in the cell.
5. The IFERROR formula lets you check a value, then specify what to do if an error is found. If you use IFERROR with the existing formula, you can show "Not Found", instead of the #N/A error: 
    =IFERROR(VLOOKUP(B6, OrdersE,2,FALSE), "Not Found")
6. To check all three tables, you can next IFERROR and VLOOKUP formulas:

      =IFERROR(VLOOKUP(B6, OrdersE,2,FALSE),  
              IFERROR(VLOOKUP(B6, OrdersW,2,FALSE),                IFERROR(VLOOKUP(B6, OrdersC,2,FALSE),"Not Found")))

This checks the OrdersE table and if an error is found, checks OrdersW table, then OrdersC. If the OrderID is not found in any of the three tables, the Not Found message is shown in the cell.

You can also check multiple tables in older versions of Excel, where IFERROR is not available, using a longer formula:

      =IF(NOT(ISERROR(VLOOKUP(B8, OrdersE,2,FALSE))),
                VLOOKUP(B8, OrdersE,2, FALSE),
        IF(NOT(ISERROR(VLOOKUP(B8, OrdersW,2,FALSE))),
                VLOOKUP(B8,OrdersW,2, FALSE),
        IF(NOT(ISERROR(VLOOKUP(B8, OrdersC,2,FALSE))),
                VLOOKUP(B8, OrdersC,2,FALSE), "Not Found"))))

VLOOKUP for Combined Values

In some tables, there might not be unique values any column in the lookup table. For example, in the table shown below, Jacket is listed twice in column A. However, there is only one record for each jacket and size combination -- Jacket Medium in row 4 and Jacket Large in row 5.

If you need to find the price for a large jacket, a VLOOKUP based only on column A would return the price for the first jacket listed (Medium). You would be underpricing the jacket -- selling it for 60.00, instead of 65.00.

To create unique lookup values, you can insert a new column at the left side of the table, and use a formula to combine the product and size. In cell A2, the formula combines the value in B2 and the pipe character and the value in C2.

=B2 & "|" &C2

Copy that formula down to the last row of data, so each row has a unique value in column A.

Note: Instead of the pipe character, you could use another character that isn't included in your data.

Then, in a VLOOKUP formula, combine the product and size as the Lookup_value. In cell H1, the formula combines the value in F1 and the pipe character and the value in G1.

=VLOOKUP(F1 & "|" &G1,$A$2:$D$5,4,FALSE)

Note: The price is now in column 4, instead of column 3.   ▲TOP

Troubleshoot the VLOOKUP formula

Your VLOOKUP formula may return an #N/A, even though the value you're looking for appears to be in the lookup table. Common causes for this are:

Text vs. Number

Space Characters

Other Characters

Text vs. Number

A common cause for this error is that one of the values is a number, and the other is text. For example, the lookup table may contain '123 (text), and the value to look up is 123 (a number).

• If possible, convert the text to numbers, using one of the methods shown here: Convert Text to Numbers
• If you can't convert the data, you can convert the lookup value within the VLOOKUP formula, using one of the solutions below:
  1. Lookup values are Text, Table has Numbers
  2. Lookup values are Numbers, Table has Text
• To figure out which values are text, and which are numbers, see the detailed number or text troubleshooting steps

1. Lookup values are Text, and the table contains Numbers

If the lookup table contains numbers, and the value to look up is text, use a formula similar to the following:

=VLOOKUP(--A7, Products!$A$2:$C$5,3, FALSE)

The double unary (--) converts text to a number, and will work correctly even if the lookup values are numbers.

2. Lookup values are Numbers, and the table contains Text

If the lookup table contains text, and the value to look up is numeric, use a formula similar to the following:

=VLOOKUP(A7 & ""),Products!$A$2:$C$5,3,FALSE)
OR
=VLOOKUP(TEXT(A7,"00000"), Products!$A$2:$C$5,3,FALSE)

The TEXT function converts a number to text, and will work correctly even if the lookup values are text. In the first example, the & operator creates a text string from an unformatted number. In the second example, a number formatted with leading zeros (e.g. 00123) would match a text "number" with leading zeros.

To see the steps for fixing the VLOOKUP problem when the lookup table has text values, watch this short video tutorial.

Spaces in one value, and not the other

Another potential cause for no matching value being found is a difference in spaces. One of the values may contain leading spaces (or trailing, or embedded spaces), and the other doesn't. To test the values, you can use the LEN function, to check the length of each value.

For example:   =LEN(A7)     will return the number of characters in cell A7. It should be equal to the number of characters in the matching cell in the lookup table.

If possible, remove the unnecessary spaces, and the VLOOKUP formula should work correctly. If you can't remove the spaces, use the TRIM function in the VLOOKUP, to remove leading, trailing or duplicate spaces. For example:

    =VLOOKUP(TRIM(A7), ProductList,2,FALSE)

Other Characters

If TRIM function alone doesn't solve the problem, you can try one of the following suggestions:

• SUBSTITUTE Function
• CLEAN Function
• Macro to Remove Web Characters

SUBSTITUTE Function

Use the SUBSTITUTE function to remove unwanted characters. There is an example on the Contextures blog: Clean Excel Data With TRIM and SUBSTITUTE

CLEAN Function

Another way to fix VLOOKUP problems is with the CLEAN function, which can remove some unwanted characters from the text. There is more information on the CLEAN function in this Contextures blog post: 30 Excel Functions in 30 Days: 29 - CLEAN

Web characters in one value, and not the other

If you copied data from a web page, it may contain HTML non-breaking space (&nbsp) characters. David McRitchie has written a macro to remove them, along with other spaces characters  

Problems When Sorting VLOOKUP formula

A VLOOKUP formula may return the correct results at first, but then shows incorrect results if the list of items is sorted. This can occur if the reference to the Lookup value includes a sheet name. For example:

=VLOOKUP('Order Form'!B5, Products!$B$2:$C$6,2,FALSE)

NOTE: This problem can occur with other functions too, such as an INDEX/MATCH lookup formula.

Watch this video to see the steps for fixing the problem, and download the VLOOKUP Sorting problem sample file to follow along. The written instructions are below the video.

Sheet Names in Reference

This type of reference is created if you click on another sheet while building the formula. As soon as you do that, Excel adds the sheet name to any subsequent references in the formula.

In the screen shot above, Dress is in cell B9, and cell C9 shows the correct price of $30.

However, after sorting the products A-Z, the Dress moves up to cell B5, but the formula in cell C5 continues to refer to cell B9. Because of the sheet names in the references, Excel retains the original references, instead of keeping a reference to the current row. Cell C5 is showing the price for a Sweater, instead of a Dress.  ▲TOP

Fix the Problem

To solve the problem, remove any unnecessary sheet names from the VLOOKUP cell references. Here is the revised formula for cell C5:

=VLOOKUP(B5, Products!$B$2:$C$6,2, FALSE)

After the unnecessary sheet names are removed, the list can be safely sorted, and the correct results will show for each item.

Download the Sample Files

1. Get the VLOOKUP sample workbook (Excel 2007 and later). The zipped file is in xlsx format, and does not contain any macros.

2. For Excel 2003, get this version of the VLOOKUP sample file. The zipped file is in xls format, and does not contain any macros.

3. To see the problem that can occur when sorting with VLOOKUP, get this VLOOKUP Sorting problem sample file. The zipped file is in xlsx format, and does not contain any macros.

4. Get the Product Price Lookup workbook, used in the video tutorial. The zipped file is in xlsx format, and does not contain any macros.  ▲TOP

More Tutorials

VLOOKUP Number/Text Troubleshooting

VLOOKUP from Another Workbook

HLOOKUP

CHOOSE Function

Lookup - 2 Criteria

LOOKUP

INDEX / MATCH

Compare Lookup Functions

Functions List