Home > Formulas > Lookup > VLOOKUP

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.

Note: For flexible lookup formulas in Microsoft Excel 365, try the new XLOOKUP function.

What Does VLOOKUP Function Do?

The Microsoft Excel VLOOKUP function does a vertical lookup for a value in the first column in a table, and returns a value from a different column, in the same row, in that table.

• VLOOKUP function can find exact matches in the lookup column, such as product code, and return its price.
• Or, VLOOKUP can find closest match, such as a score number, and return a letter grade for that score.

In the sections below, VLOOKUP examples show how to find an approximate match, or an exact match, and get the data that you need from a lookup table.

What Goes in VLOOKUP Formula?

To look up data with the Excel VLOOKUP function, four pieces of information are used.

1. First, what it should look for, such as the product code.
2. Second, where the lookup data is located, such as an Excel table name.
3. Third, column number in the lookup table, that you want results from, such as Price from column 3.
4. Fourth, the type of match you want - exact or approximate.

Those four items go in the VLOOKUP formula in a specific order, which is called its syntax. Each item in the syntax is called an argument.

VLOOKUP Function Syntax

The VLOOKUP function has the following syntax for its four arguments:

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

Here are the VLOOKUP arguments - the first 3 are required arguments, and the last one is an optional argument:

1. lookup_value: what it should look for, such as the product code — this can be a value, or a cell reference.
2. table_array: where the lookup data is located (2 or more columns) — this can be a range reference or a range name, with 2 or more columns.
3. col_index_num: the column that has the value you want returned, based on the column number within the table.
• Note: This column index number can be different from the worksheet column number
4. [range_lookup]: for an exact match, use FALSE or 0; for an approximate match, use TRUE or 1, with the lookup value column sorted in ascending order.

• Note: If you omit the range_lookup argument, VLOOKUP will return an approximate match.

Product Price VLOOKUP Example

In the sections below, you'll see how to build a simple order form, where you enter a product name, and the product price is automatically filled in for you.

Quick Look at Finished Workbook

Before you start building the VLOOKUP formulas, here's a quick look at the finished product.

In this example, there is an Excel workbook with 2 main sheets -- Products, and Order

1) Products sheet has a small lookup table with 2 columns of product information -- Product, and Price.

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

Product Price VLOOKUP Steps

Here are the main steps for creating the product price VLOOKUP formula

--1) Create Product Lookup table 

--2) Make an Order Form

--3) Add Product Price VLOOKUP

--4) Add Total Price Formula

The detailed instructions are in the sections below.

1) Create Product Lookup Table

The first step is to enter the product information on the Product worksheet

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

Format as Excel Table

Next, format the product list as a named Excel Table. The table will change size automatically, if you add or remove product details in the future.

• First, select any cell in the list
• Next, on the Excel Ribbon's Home tab, click the Format as Table command
• In the gallery of Table Styles, click on the format that you'd like
• Then, in the Create Table dialog box, make sure the product list range, A1:B5, is shown in the first box, "Where is the data for your table?"
• Next, add a check mark for the setting, "My table has headers"
• Finally, click the OK button, to format the product price list as an Excel table.

Rename the Excel Table

Next, change the default name that Excel gave to the new table on the spreadsheet. This step isn't required, but I find it helpful, when creating formulas later.

• First, select any cell in the table
• Next, on the Excel Ribbon, click the Table Design tab
• Then, at the left, in the Table Name box, replace the default name (Table1), with a name that describes the table's contents: tblProduct
• Press Enter, to complete the name, or click anywhere on the worksheet

2) Make an Order Form

Next, follow these steps to set up a simple order form on the worksheet named Order.

• On the Order sheet, in cell B2, type the heading, Order Form
• In A4 to A7, type labels, so it's clear what should be entered in the adjacent cells: Product, Quantity, Unit Price and Total Price
• Next, select cells B4 to B7, and add borders, to make the cells stand out on the worksheet. I used the "All Borders" option from the Borders drop down list.
• Then, add a light green fill colour to cells B4 and B5, to show they're for data entry
• Finally, add a light grey fill colour to cells B6 and B7, to show they have formulas

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

3) 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, to use for testing the new formula

• 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 in this Formula

There are 4 arguments in the VLOOKUP function, and here's how they are used in this formula:

1. lookup_value: What value do you want to look up?
• In this example, the first argument refers to the product name in cell B4
2. table_array: Where is the lookup table?
• For the second argument (table-array), product information is in a lookup table named tblProducts
3. col_index_num: Which column has the value you want returned?
• In the third argument, 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 fourth argument (range lookup), so the formula will return an exact match.
• If the product name is not found, the result will be #N/A.

4) Add Total Price Formula

The final step is to add a Total Price formula -- the quantity multiplied by the unit price for the product.

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

• =B5*B6

In that formula, the * (asterisk) operator multiplies the quantity (B5) by the unit price (B6), to calculate the total price.

Finally, for an additional test, type a different product name and quantity, to see the VLOOKUP formula result.

Ex 2: Product Price for Quantity

In this example, there are 4 columns for product pricing, based on the minimum quantity ordered.

In cell H5, the following formula finds the price, based on product name,. and quantity ordered.

VLOOKUP is combined with MATCH, to get the correct price.

• =VLOOKUP(H3,A4:E7,MATCH(H4,A3:E3,1),FALSE)

This video shows the steps for building the VLOOKUP and MATCH formula. Also, you'll see a problem that can occur if the data is in a named Excel Table.

Video Timeline:

• 00:00 Introduction
• 00:16 Product Prices
• 01:01 MATCH Function
• 02:32 VLOOKUP Formula
• 03:34 Excel Table
• 04:21 Fix the Formula
• 4:56 Get the Sample File

Ex 3: Student Grades - Approximate Match

In some situations, an VLOOKUP 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.

Ex 4: Find Column Number 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, using absolute references to the heading cells on the orders table:

=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

Ex 5: Partial VLOOKUP With Wildcards

In addition to looking for specific text values, you can also use wildcards with the VLOOKUP function.

The following wildcard characters in Excel represent unknown characters, before, between, or after, other characters

• * - asterisk wildcard character represents any number of characters in that position, including zero characters.
• ? - question mark wildcard character represents one character in that position

Find Text String in Lookup Column

In the example shown below, there are two asterisk wildcards in the formula, before and after the reference to cell B2:

=VLOOKUP("*" & B2 & "*",E1:F9,2,FALSE)

The formula finds the first month name that contains the letter that's typed in cell B2, and returns that month's ID number

Use Multiple Wildcards

One or more wildcards can be used in the lookup value.

In the screen shot below, there are two asterisk wildcards in the formula, and a question mark wildcard in cell B2.

Ex 6: Check Multiple Lookup Tables

Usually a VLOOKUP formula checks a single table to find a lookup value. However, if you need to check multiple tables, you can use the IFERROR function with VLOOKUP.

In this example, there are lookup tables for three regions, - West, East and Central, with details on orders placed in each region.

The order details list on each region's sheet has been set as a named range:

• OrdersW, OrdersE and OrdersC

For example, on the Central region's order sheet, shown below, cells A1:D6 is named as OrdersC

Get Details for Order ID

On a sheet named Orders, you can enter an Order ID, in cell B6.

In cells C6 to E6, there are VLOOKUP formulas, with IFERROR. Those formulas check each named range, and return the information about the selected order ID number.

Set up VLOOKUP and IFERROR Formulas

To create the VLOOKUP and IFERROR formulas, follow these steps:

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.

Ex 7: 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 unique jacket and size combination:

• Jacket Medium in row 4
• 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.

Create Unique Lookup Combinations

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.

Add New Column and Formula

To create a unique lookup value, follow these steps:

• First, insert a new column, to the left of column A
• Next, in cell A1, add a heading for the new column: ProdSize
• Then, cell A2, the following formula:
  • =B2 & "|" &C2
• Next, press the Enter key, to complete the formula
• Finally, copy that formula down to the last row of data, so each row has a unique lookup value in column A.

That formula combines three things, using the & (ampersand) operator:

• Product name in cell B2
• Pipe character ( | ), as a visual divider
  • Instead of the pipe character, you could use another character that isn't used in your data
• Product size in cell C2

Create VLOOKUP with Combined Product and Size

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)

VLOOKUP Problems & Troubleshooting

Occasionally, you might run into problems when using the VLOOKUP function.

In the sections below, you'll find tips for avoiding problems, and steps for fixing problems that do occur.

--1) VLOOKUP Problems to Watch For

--2) Troubleshoot VLOOKUP formula 

--3) Problems When Sorting VLOOKUP formula

--4) Faster VLOOKUP with COUNTIF

--5) Video Transcript: Fix VLOOKUP Error

1) VLOOKUP Problems to Watch For

The VLOOKUP function can cause a few problems, so here are a couple things to watch for, when using this function in your Excel workbooks -- slow calculation and sorting problems.

Slow Calculation

VLOOKUP can be slow, especially when doing a text string match, in an unsorted table, where an exact match is requested.

To make VLOOKUP as fast as possible, try these tips:

• Wherever possible, use a table that is sorted by the first column, in ascending order, and use an approximate match.
• Use MATCH or COUNTIF to check for the value first, to make sure it is in the table’s first column. See the Faster VLOOKUP with COUNTIF further down on this page
• For more suggestions on speeding up a lookup formula, see Charles Williams’ page on Optimizing Lookups.

Note: Other functions, such as INDEX and MATCH, or XLOOKUP, can be used to return values from a table, and can be faster

Sorting Problems

After you create a VLOOKUP formula, it might return the correct results at first. However, it might change to incorrect results later, after the list of items is sorted. This sorting problem can cause serious problems, if you don't notice that the results have changed. You could end up charging the wrong prices for all of your products!

To see what causes this problem, and how to fix the problem, and avoid the problem in the future. go to the Problems When Sorting VLOOKUP formula section, further down on this page. There are written steps, and a short video that shows the problem and the fix.

2) Troubleshoot 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:

--A) Text vs. Number

--B) Space Characters

--C) Other Characters

A) 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.

Video: Text vs Numbers

To see the steps for fixing the VLOOKUP problem when the lookup table has text values, watch this short video tutorial. The full transcript for this video is further down the page.

Video Timeline

• 00:00 VLOOKUP Error Problem
• 00:44 Values Not Equal
• 01:37 Fix Values Not Equal
• 02:07 Change VLOOKUP Formula

B) 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)

C) Other Characters

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

• SUBSTITUTE Function
• CLEAN Function
• Unicode 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

Unicode Characters

In some cases, your data might have hidden characters, copied from a website, and the Excel CODE function doesn't recognize those characters. I ran into that problem, and wrote about it on my Contextures blog.

• CODE and CHAR use the basic ANSI character set in Windows, which has a maximum code number of 255.
• The hidden characters are probably from a different character set, and have a code number greater than 255

Usually those characters are at the start or end of the text, and my blog article describes how to find those characters, and use them in your VLOOKUP formula.

Clean Hidden Characters From Data

Instead of using the hidden characters in the VLOOKUP formula, you might prefer to clean the data, and get rid of the hidden characters. If so, the steps below show how to extract the characters from the cell value, using the LEFT and RIGHT functions. Then do a Find and Replace, using those extracted characters

NOTE: Before trying this technique, make a backup copy of your workbook.

1) Extract Hidden Characters

In this example, one of the values with hidden characters was copied to a blank sheet, and pasted in cell B2

• In cell C2, enter this formula: =LEFT(B2,1)
• In cell D2, enter this formula: =RIGHT(B2,1)

Both cells might look empty, after you enter the formulas, if they return hidden characters

• I used the LEN function in cells B3:D3, to see how many characters were in cells B2:D2
• For curiosity, I used the UNICODE function to get the character number of the hidden characters

2) Find and Replace Hidden Characters

Next, if either cell looks empty, try this find/replace technique:

• Select the formula cell that looks empty, e.g. cell C2
• Copy the "empty" cell
• Select the cells with the hidden characters, that you want to fix
• Press Ctrl+H to open the Find/Replace window
• Click in the Find box, and press Ctrl+V to paste the copied cell value
• Leave the Replace box empty
• Press Replace All

That might fix the problem, and if not, try the same steps, but copy the other empty cell, to Find and Replace its value

3) 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.

4) Faster VLOOKUP with COUNTIF

The VLOOKUP function can be slow when doing an exact match for a text string.

In this example, the VLOOKUP formula will find the exact price for a selected product, without using the Exact Match setting.

IMPORTANT: To prevent incorrect results, the lookup table must be sorted by the first column, in ascending order (A-Z)

1. Lookup table must be sorted by the first column, in ascending order (A-Z)
2. COUNTIF function checks for the value, to prevent incorrect results

Here is the formula in cell C7:

• =IF(COUNTIF(B3:B5,B7),VLOOKUP(B7,B3:C5,2,TRUE),0)

Here's how the IF formula works, with these 3 arguments:

1. Test: COUNTIF function checks if value is found, in cells B3:B5
2. True: VLOOKUP function returns product price
3. False: return 0 (zero)

In the screen shot below, the correct price was returned:

• Lamp product was found in column B
• Its price (15) was returned from column C.

5) Video Transcript: Fix VLOOKUP Error

Here is the full transcript for the Numbers and Text Troubleshooting video shown above.

----------------------------

Usually the VLOOKUP formula in Excel works very well. We could enter product code, and the VLOOKUP formula would return the product name or price for that product code.

But in this example, we've typed a code here.

We can see that code in the table but the VLOOKUP is returning an N/A error.

In the formula bar, you can see that VLOOKUP formula.

• We're referring to the value in B8
• looking up in the code column
• asking for the value in the second column

So it should be giving us the product but it isn't. So we'll do a bit of troubleshooting, to see what the problem is, and how we can solve it

Values Not Equal

Sometimes the problem is, things that look the same on the worksheet, aren't really a match.

We'll see if what we typed in B8 is really a match for what's in B2.

In this cell, I'm going to just do a simple test.

• I'll click here
• type an equal sign
• then click on B2
• another equal sign
• and B8

It's coming back FALSE, so there's something different about these values

• If I click on B2, I can see an apostrophe at the start of that number.
  • So it's been entered as text. Maybe this was a download from another file, and it's stored as text there.
• If I click on B8, there's no apostrophe.
  • So this is a number, and the number is not equal to the text, which would have a value of 0.

So how can we fix this?

Fix Values Not Equal

If we select all these cells, one way to fix it, would be to change all of these to numbers, so they match the values we're going to type in here as numbers.

To do that, I can:

• Go to the Ribbon
• On the Data tab, click Text to Columns
• When this comes up, just click Finish

That's automatically changed all of these to numbers, and you can see that our VLOOKUP is working correctly now.

So it's showing me that 123 is the product called Paper.

Change VLOOKUP Formula

In some cases, you can't change your lookup table, so we can change our lookup formula.

Here we have VLOOKUP(B7

So, whatever is in B7, look up in this table. And it can't find this number, because this is text.

So we'll change this lookup to text.

• I've clicked after the B7
• I'll type an & which on my keyboard is Shift + 7
• Then two double quotes, which is just an empty string

So now this used to be a number. When we add an empty string, it's going to automatically become text

I'll press Enter, and now what's entered as a number here, it's converted that to text, so it matches what's in here.

You can add an empty string in your VLOOKUP formula to convert numbers to text, so the lookups work correctly.

Get the Sample Files

• VLOOKUP Examples: Get the VLOOKUP sample workbook. The zipped file is in xlsx format, and does not contain any macros.
• Sorting Problem: 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.
• Product Pricing: Get the Product Price Lookup workbook, used in the video tutorial. The zipped file is in xlsx format, and does not contain any macros. 

More Tutorials

VLOOKUP Number/Text Troubleshooting

VLOOKUP from Another Workbook

HLOOKUP

XLOOKUP function

CHOOSE Function

COLUMNS Function

Lookup - 2 Criteria

LOOKUP

INDEX / MATCH

Compare Lookup Functions

Functions List