*The tutorial shows how to use VLOOKUP in Excel with many examples, explains how to Vlookup from another sheet and different workbook, search with wildcards, and a lot more.*

This article begins a series covering VLOOKUP, one of the most useful Excel functions and at the same time one of the most intricate and least understood. We will try to explain the basics in a very plain language to make the learning curve for an inexperienced user as easy as possible. We will also provide formula examples that cover the most typical usages of VLOOKUP in Excel.

What is VLOOKUP? To begin with, it is an Excel function :) What does it do? It searches for the value you specify and returns a matching value from another column. More technically, the VLOOKUP function looks up a value in the first column of a given range and returns a value in the same row from another column.

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

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

The VLOOKUP function is available in all versions of Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 and lower.

The syntax for the VLOOKUP function is as follows:

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

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

*Lookup_value*is the value to search for.

This can be a value (number, date or text), cell reference (reference to a cell containing a lookup value), or the value returned by some other function. Unlike numbers and cell references, text values should always be in enclosed in "double quotes".As an example, please have a look at the below formula and try to "translate" it into English:

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

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

*Table_array*is two or more columns of data.The VLOOKUP function searches for the lookup value in the

**first column**of the table array, which may contain various text values, numbers, dates, and logical values.So, you can now read the above formula a little further: search for "lion" in the range A2:A6 (because A is the 1st column in our table array). So far, so good, right?

*Col_index_num*is the number of the column from which the value should be returned. The counting starts from the leftmost column in the table array, which is 1.Meaning, our sample formula will return a matching value from column B, which is 2nd in the table array.

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

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

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

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

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

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

When using VLOOKUP formulas in real-life worksheets, the main rule of thumb is this: **lock table array** with absolute cell references (like $A$2:$C$11) to prevent it from changing when copying a formula to other cells.

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

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

To pull the animal names from column B:

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

To extract speed from column C:

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

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

If you investigate the formula in a lower row, you will notice that the lookup value reference has adjusted for that specific row, while the table array is locked:

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

- The VLOOKUP function cannot look at its left. It always searches in the
**leftmost column**of the table array and returns a value from a column to the right. If you need to pull values from left, use the INDEX MATCH combination that can does not care about the positioning of the lookup and return columns. - The VLOOKUP function is
**case-insensitive**, meaning that uppercase and lowercase characters are treated as equivalent. To distinguish the letter case, use VLOOKUP case sensitive formulas. - Remember about the importance of the last parameter. Use TRUE for approximate match and FALSE for exact match. For full details, please see VLOOKUP TRUE vs. FALSE.
- When searching for approximate match, make sure the data in the lookup column is sorted in ascending order.
- If the lookup value is not found, a #N/A error is returned. For information about other errors, please see Why Excel VLOOKUP is not working.

I hope vertical lookup is starting to look a bit more familiar to you. To strengthen your knowledge, let's build a few more VLOOKUP formulas.

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

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

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

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

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

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

- If the spreadsheet name contains
**spaces**or**non-alphabetical characters**, it must be enclosed in single quotation marks, e.g. 'Price list'!$A$2:$C$9. - In case you use a VLOOKUP formula for multiple cells, remember to
**lock table_array**with the $ sign, like $A$2:$C$9.

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

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

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

If either a workbook name or worksheet name contains spaces or non-alphabetical characters, you should enclose them in single quotes like this:

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

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

- Open both files.
- Start typing your formula, switch to the other workbook, and select the table array using the mouse.
- Enter the remaining arguments and press the Enter key to complete your formula.

The result will look somewhat like the screenshot below:

Once you **close** the file with your lookup table, the VLOOKUP formula will continue working, but it will now display the full path for the closed workbook:

For more information, please see How to refer to another Excel sheet or workbook.

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

To create a named range, simply select the cells and type the name you want in the *Name* box to the left of the Formula bar. For the detailed steps, please see How to name a range in Excel.

For this example, we gave the name *Prices_2020* to the data cells (A2:C9) in the lookup sheet. And now, we can Vlookup with this compact formula:

`=VLOOKUP(A2, Prices_2020, 3, FALSE)`

Most names in Excel apply to the **entire workbook**, so you don't need to specify the worksheet's name when using named ranges.

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

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

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

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

`=VLOOKUP(A2, Price_table, 3, FALSE)`

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

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

*Question mark*(?) to match any single character.*Asterisk*(*) to match any sequence of characters.

Wildcards prove really useful in many situations:

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

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

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

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

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

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

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

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

The below screenshot shows the results:

Below are a few more VLOOKUP formulas with wildcards.

Find the last name ending with "son":

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

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

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

Pull a 5-character last name:

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

From the previous example, you already know that it is possible to concatenate an ampersand (&) and a cell reference to make a lookup string. To find a value that contains a given character(s) in any position, put an ampersand before and after the cell reference.

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

Extract the key:

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

Extract the name:

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

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

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

If *range_lookup* is set to FALSE, a Vlookup formula searches for a value that is exactly equal to the lookup value. If two or more matches are found, the 1st one is returned. If an exact match is not found, the #N/A error occurs.

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

An approximate match Vlookup works with the following caveats:

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

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

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

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

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

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

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

- Sort the first column of
*table_array*from smallest to largest. - Use TRUE for the
*range_lookup*argument or omit it.

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

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

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

And returns the following results:

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

Undoubtedly, VLOOKUP is one of the most powerful and useful Excel functions, but it's also one of the most confusing ones. To make the learning curve less steep and experience more enjoyable, we included a couple of time-saving tools in our Ultimate Suite for Excel.

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

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

- Run the VLOOKUP Wizard.

- Choose your main table and lookup table.
- Specify the following columns (in many cases they are picked automatically):
**Key column**- the column in your main table containing the values to look up.**Lookup column**- the column to look up against.**Return column**- the column from which to retrieve values.

- Click the
**Insert**button.

The following examples show the wizard in action.

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

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

**Extra bonus!** Due to the clever use of cells references, the formulas can be copied or moved to any column, without you having to update the references.

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

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

- Select your main table.
- Select the lookup table.
- Choose one or several common columns as the unique identifier(s).
- Specify which columns to update.
- Optionally, choose the columns to add.
- Allow the Merge Tables Wizard a few seconds for processing… and enjoy the results :)

If you like our tools and decide to get a license, don't miss the 15% off coupon code that we've created especially for our blog readers: AB14-BlogSpo

That's how to use VLOOKUP in Excel at the basic level. In the next part of our tutorial, we will discuss advanced VLOOKUP examples that will teach you how to Vlookup multiple criteria, return all matches or Nth occurrence, look up across multiple sheets with a single formula, and more. I thank you for reading and hope to see you next week!

Excel VLOOKUP formula examples

Excel formulas
Excel functions
Vlookup in Excel
Merge data in Excel
SumIf
Excel CountIf
Excel Compare
Excel If statement
Excel Charts
Pivot Table
Updates
Excel conditional formatting
Excel formatting
Excel time
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Outlook templates

## 193 Responses to "Excel VLOOKUP tutorial for beginners with formula examples"

I've solved it, realised should be TRUE instead of FALSE thanks anyway

I knew there had to be a way to look up one cell and then return a neighboring cell, and I found it here. Thank you so much for this simple tutorial, it saved me a ton of time!

I think you are point 7 inverted.

7. And finally, remember about the importance of the last parameter. Supply TRUE for exact match or FALSE for approximate match, and it will save you a lot of headache.

Hi Suhendra,

Good catch, thank you! Fixed.

Oh... so humble ... u took time to read, replied and accepted also. I can see great courtesy. I learn such 'open-mind' nature from people like u....

I can't get my Vlookup to work. I used the same formula last year with no issues. I have excel 2016. I have 2 sheets in one workbook and here is my formula

=VLOOKUP(A2,'Pay Register Total'!A$2:F$1762,6,FALSE)

The formula returns #N/A on every line.

I have the same data, supplier number, in the first column on both sheets.

Please help!!

thanks,

Margaret

Check the format of each column being used in your Vlookup. The #N/A often results due to formatting mismatch.

I am trying to reference another spreadsheet with the name of customers in Column B and needing to reference their spending which is located in column D and divide column D by 4.

Any help you can provide will be greatly appreciated.

I am trying to reference another spreadsheet with the name of customers in Column B and needing to reference their spending which is located in column D and divide column D by 4. There are 700+ customers so I want to ensure that I reference the correct customer with their spending instead of having to key all manually.

Any help you can provide will be greatly appreciated.

Hello, Deb,

I'm sorry, it is not very clear what result you want to get. Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? I kindly ask you to shorten your tables to 10-20 rows/columns and include the link to your blog comment.

We'll look into your task and try to help.

So I need to run a report where I need to count up how many times an item shows up across 12 different sheets (each sheet is a month of data). Is there a way to use vlookup to look in an entire workbook without having to input every worksheet name in the formula?

Hello, Jason,

If you need to count up how many times an item shows up in your sheets, please try the combination of COUNTIF and SUMPRODUCT functions. First you need to create a range containing the names of the relevant sheets, and then use the following formula:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&A2:A14&"'!A2:A10"),C2))

Where &A2:A14& is the list with the sheets names; A2:A10 is the data range that you want to count the specified value across worksheets; C2 is the cell with the value you want to count up.

Hope this will help.

How do I identify records from one worksheet A which is not in worksheet B and then copy only those record from A to B which is not in B.Please help.

Thanks

Your wildcard examples work on no data, but the exact values you used in your example. I tried to replicate your using the cell contents example with the animal table using =VLOOKUP("*"&C1&"*",$A$2:$B$13, 1,FALSE) where C1 has Coy typed into it. It should find "Coyote" and return the speed but it does not. I cannot figure out what is going wrong.

Hello TC,

We have uploaded the VLOOKUP sample worksheet with all the formula examples, so you can check the wildcard formulas.

Hello! I have a VLookup that I am using with the IF function to pull pay rates (regular/overtime) and match it with employee names. My data is in 3 columns (Employee,Reg. Rate, OT Rate) and the lookup is checking 2 columns (Employee, Pay Type) for data. It's working great EXCEPT, I cannot get the N/A to hide if the columns are blank. Below is the current formula:

=IF(B8="overtime", VLOOKUP(A8, EmployeeRates, 3, FALSE), VLOOKUP(A8, EmployeeRates, 2, FALSE))

Any help would be greatly appreciated.

=iferror(IF(B8="overtime", VLOOKUP(A8, EmployeeRates, 3, FALSE), VLOOKUP(A8, EmployeeRates, 2, FALSE)),o)

I have to register daily sales details, for example 01-01-2018 Raju sales in rupees Rs.1000/-, Ramu sales in rupees Rs.1500/-, now I need 01-01-2018 individual sales amount in another sheet

You may do with helper column which you will put serial number to make each 01-01-2018 unique then the lookup formular will use those unique ref to pull their respective sales

Big thanks to Microsoft for inventing vlookup function.

As a Senior manager I rely on this, not my staff.

They all leave me. They hate me.

hello

please help me.

If the unique identifiers is repeated a few time in the table for example "Product1" appears three times and I would like the corresponding data or all three times it appears, may I know how it can be done. The three corresponding data is text and should appear on three different lines. Thanks for your help.

Hi Adrian,

The VLOOKUP function cannot return multiple matches, you'd need an array formula for that. A few formula examples can be found here: How to Vlookup multiple matches in Excel.

How would I use vlookup formula to access various files - ie I want the user to enter in the file name into a cell (A1) and the various VLOOKUP formulas would use the A1 text to feed into the external file reference.

example:

=VLOOKUP("Die Size", 'C:\Users\Dwayne\Desktop\[(A1).xlsx] Set-up'!$A$16:$AB$16,13,TRUE)

And in this case the A1 is a cell reference in the same worksheet; and then the user could get the various info without editing every formula.

advice appreciated!

Helpful excel,but can you sheet provide for website.

Thanks

Explain V lookup together to Choose function Any Example?

Hi Maina,

We published a separate tutorial on Excel CHOOSE function with formula examples. Hope you will find it useful :)

'v-03'!H33 I have four formulas that are similar but only one doesn't calculate total please help

VLookup expanded here is "Too" Complicated..!!!

Thank you! This is VERY helpful!

need to know how to copy and paste some cells from a page of a spread sheet to an email WITHOUT any formula data included and look exactly as the copied cells

Select your entire spreadsheet, do a copy paste, next do a copy paste special and choose Values. Once you do this the exact number and not the formula will paste to your spreadsheet.

Thank you for such a clear and easy to understand training. Most appreciated.

How i can get the corrosponding value of column B , column C and column D in sheet 1 from sheet 2 with excel formula. Value In column A of both the sheet are matching but not serial wise.

i have 2 coloms in one colom have absenties names and another column have full name list, how i can find presents name list from both list

Thawfeeque:

Depending on what you want to accomplish there are a couple of things you can do with find duplicates.

Go to this address and see if it helps. If you still have questions post them and someone will try to help.

https://www.ablebits.com/office-addins-blog/2015/08/26/excel-compare-two-columns-matches-differences/#highlight-matches-differences

Hello,

I got zero results in my VLOOKUP formula. My formula is =IF([Insurance Charge]="Yes",120, IF(E2="No",0,VLOOKUP(E2,G2,'Rental Information'!A3:D7,FALSE))) And the question is Use an exact-match VLOOKUP function nested in an IF function to return the monthly insurance charge if the renter has purchased insurance (“Yes” in the Insurance column). Use the furniture’s Group Code in the Rental Data worksheet and the Monthly Insurance column in the Rental Information worksheet Furniture Rental Charges table (predefined name RentalCharges) to return the appropriate charge. If the renter has not purchased insurance, return 0. Don’t forget to multiply it by the Rental Period to get the final amount.

What's wrong with my formula?

My question above is urgent. Thank you.

My VLookup isn't working. I run a report in a software that copies data into an Excel file. I then save the file as an Excel Workbook, and re-open the file in the same instance as my other workbook.

I then copy the data to my other excel workbook, where I have a VLOOKUP set up that references the descriptions of the data with their category code (which isn't included on the original report). However, I'm getting the #N/A error type.

I double checked the formatting and it's identical. Spelling and spacing is also identical. What's odd is, if I delete the data and re-type it into the space the VLookup works. I'd prefer not having to do that each time and am wondering what else might be causing this?

I am not able to filter my spreadsheet, i have value 1 to 1000 numbers but i want to filter it based on some random numbers or list of random number.

How i can do it excel. Please suggest for the same.

Appreciate your help.

need further clarification please

I am happy with your excel examples thank you and need more examples for learning

I found this site very useful, wherever i forgot or stuck somewhere, it help me solve my problems. Thanks and best regards,

Hello

please make a video for this

Hi Samsoor,

We already a video for this, and you can find it here:

Video: Excel VLOOKUP explained

Hi I am using this formula to look up herbicide name by active ingredient

=LOOKUP(CO80,'Herb list2'!$A$2:$B$77,'Herb list2'!$B$2:$B$77)

IS there any way to change this formula to set to exact match (FALSE)??

I tried

=LOOKUP(CO80,'Herb list2'!$A$2:$B$77,'Herb list2'!$B$2:$B$77, FALSE) an got error msg

"You've entered too many arguments for this function"

U R right lots of headaches!!

MADAM HOW CAN I USE VLOOKUP FORMULA IN MULTI SHEETS?

Madam,

How can i attach data from datasheet of other folders?

How can select the data at a time vlookup results

How can I find a texts with same first 2 characters and return the value?

like

ASD-12345

ASD-o9093

DFD-00000

Am I able to search to return the results only with ASD-xxxx? Thank you

My formula is not working. I am using Vlookup to search in a differente workbook and Iall I am getting is "#REF". Already check the column's format and validated instruction. I am not sure what is wrong.

=VLOOKUP(C3,'[Plan Membership Comparison.xls]Sheet5'!$A$2,5)

Hi Roxanna,

In the table_array argument, you need to supply a lookup range, while you only have a single cell ($A$2).

Is there a way of looking up a rate of pay (for an individual worker) from a table where there are multiple agencies, multiple classes of worker (e.g. Healthcare assistant, nurse, doctor) and the rate varies depending on the time/day? Thanks!

DEAR SIR

I AM RAVI , NOW I AM WORKING VLOOKUP FORMULA ,I WANT TO FIX THE LOOK UP VALUE , HOW ITS WORK

i learn everday and i learn many things

I am impressed with vlookup method but i have a problem of how to apply it! I have two sheets of workers, my intension was to send data of workers (454)to my boss but what has been sent are only 435 workers, my question is how can I use vlookup to identify the names which were not sent?