Excel VLOOKUP tutorial for beginners with formula examples

This article begins a series covering one of the most useful Excel functions - VLOOKUP. At the same time, it's one of the most intricate and least understood.

In this tutorial, I 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 explore some formula examples that cover the most common usages of VLOOKUP in Excel.

Excel VLOOKUP function - syntax and basic uses

So 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 the specified 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" in VLOOKUP stands for "vertical". It is used to differentiate VLOOKUP from the HLOOKUP function that looks up a value in the top row of an array (H stands for "horizontal").

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

Excel VLOOKUP syntax

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 parameters, or arguments. The first three parameters are requited, the last one is optional.

  1. lookup_value is the value to search for.

    This can be either a value (number, date or text) or a cell reference (reference to a cell containing a lookup value), or the value returned by some other Excel function. For example:

    • Look up for number: =VLOOKUP(40, A2:B15, 2) - the formula will search for the number 40.
    • Look up for text: =VLOOKUP("apples", A2:B15, 2) - the formula will search for the text "apples". Please pay attention that you always enclose text values in "double quotes".
    • Look up for value in another cell: =VLOOKUP(C2, A2:B15, 2) - the formula will search for the value in cell C2.
  2. table_array is two or more columns of data.

    The VLOOKUP function always searches for the lookup value in the first column of table_array. Your table array may contain various values such as text, dates, numbers, or logical values. Values are case-insensitive, meaning that uppercase and lowercase text are treated as identical.

    So, our formula =VLOOKUP(40, A2:B15,2) will search for "40" in cells A2 to A15 because A is the first column of table_array. Hopefully, everything is clear so far : )

  3. col_index_num is the column number from which the value should be returned.

    The left-most column in the specified table_array is 1, the second column is 2, the third column is 3, and so on.

    Well, now you can read the entire formula =VLOOKUP(40, A2:B15,2). It searches for "40" in cells A2 through A15 and returns a value from column B in the same row (because B is the 2nd column in the specified table array).
    Excel VLOOOKUP function

  4. range_lookup determines whether to search for an approximate match (default - TRUE or omitted) or exact match (FALSE). This parameter is optional but very important. Further on, you will find a few formula examples showing how to Vlookup with exact or approximate match.

Excel VLOOKUP examples

I hope vertical lookup is starting to look a bit more familiar to you. Now, let's build a few VLOOKUP formulas for real data.

How to do vlookup in Excel from another worksheet

In practice, VLOOKUP is rarely used to find data in the same worksheet. Most often you will have to look up and pull the matching data from a different sheet.

To do Vlookup from a different Excel sheet, you should enter the worksheet's name and an exclamation mark in the table_array argument before the range of cells, e.g. =VLOOKUP(40, Sheet2!A2:B15,2). The formula indicates that the lookup range A2:B15 is located in Sheet2.

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 a mouse.

The formula you see in the screenshot below searches for the text "Product 1" in column A in the "Prices" worksheet:

=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)
A VLOOKUP formula to search in another worksheet

Tip. It is a good idea to lock the table array by using absolute cell references (with the $ sign). This will prevent the lookup range from changing when you copy your formula to other cells.

How to do vlookup from a different workbook

To perform vertical lookup between two different Excel workbooks, you should supply the workbook's name in square brackets before the worksheet's name.

For example, that's how you look up the number 40 in Sheet2 of the Numbers.xlsx workbook:


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


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

  1. Open both workbooks. This step is not obligatory, but makes it easier to write a formula because you won't have to type the workbook's name manually, thus preventing misprints.
  2. Start typing your formula, and for the table_array argument, switch to the other workbook and select the lookup range there.

In the formula you see in the screenshot below, the lookup workbook PriceList.xlsx and the lookup worksheet is "Prices".
A VLOOKUP formula to search in a different worksheet

Once you close the workbook with your lookup table, your VLOOKUP formula will work anyway, but it will display the full path for the lookup workbook, as shown below:
When the lookup workbook is closed, the formula shows the full path to it.

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

How to use a named range or table in VLOOKUP formulas

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

To create a named range, just select the cells and type any name in the Name box, to the left of the Formula bar.
Creating a named bar in Excel

And now you can write the following formula to get the price of "Product 1":

=VLOOKUP("Product 1",Products,2)

Most range names in Excel apply to the entire workbook, so you don't need to specify the worksheet's name, even if your lookup range resides in a different worksheet. If it is in another workbook, put the workbook's name before the named range, for example:

=VLOOKUP("Product 1",PriceList.xlsx!Products,2)

Such formulas are far more understandable, aren't they? Besides, using named ranges can be a good alternative to absolute cell references. Since a named range doesn't change when a formula is copied to other cells, you can be sure that your lookup range will always remain correct.

If you have converted a range of cells into a fully-functional Excel table (Insert tab > Table), then you can select the lookup range using a mouse, and Microsoft Excel will automatically add the columns' names or the table name to the formula:
Using a table name in a VLOOKUP formula

The complete formula may look similar to this:

=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)

or even =VLOOKUP("Product 1",Table46,2).

As well as named ranges, columns names are constant and your cell references won't change no matter where the formula is copied within the same workbook.

Using wildcard characters in VLOOKUP formula

Like in many other formulas, you can use the following wildcard characters with Excel VLOOKUP function:

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

Using wildcard chars may prove really useful in many cases:

  • When you do not remember the exact text you are looking for.
  • When you want to find some word that is part of the cell's contents. Usually, the VLOOKUP function searches by the entire content of a cell, as if you selected the option "Match entire cell content" in the standard Excel Find dialog.
  • When a lookup column contains extra leading or trailing spaces. If it is the case, you may rack your brain trying to figure out why the normal formula does not work.

Example 1. Look up text starting or ending with certain characters

Suppose, you want to find a certain customer in the below database. You cannot remember his surname, but you know it starts with "ack". So, you do a vlookup in this way:


A VLOOKUP formula based on cell's value

Once you are sure you've found the correct name, you can use a similar formula to get the sum paid by that customer. You only have to change the 3rd parameter in the formula to the appropriate column index, 3 in our case:


Here a few more examples of VLOOKUP formulas with wildcard characters:

=VLOOKUP("*man",$A$2:$C$11,1,FALSE) - find the name ending with "man".

=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE) - find the name starting with "ad" and ending with "son".

=VLOOKUP("?????",$A$2:$C$11,1,FALSE) - find a 5-character last name.

Note. For a wildcard VLOOKUP formula to work correctly, you always have to add FALSE as the last parameter. If more than one entry meets the wildcard criteria, the first found value will be returned.

Example 2. Wildcard VLOOKUP formulas based on cell's value

And now, let's see how to look up a value in some cell when you know only part of it. Suppose, you have license keys in column A and license names in column B. You want to find a license name corresponding to some license key, of which you have only a few characters. Enter the characters in C1 (lookup value) and use the following formula to pull the License Name:


Please notice that we use an ampersand (&) before and after a cell reference in the 1st parameter to concatenate a text string.

As you can see in the screenshot below, the VLOOKUP function returns "Jeremy Hill" because his license key contains the group of chars stated in cell C1:
vlookup wildcard

Excel VLOOKUP with exact or approximate match

And finally, let's have a closer look at the last argument of the Excel VLOOKUP function, range_lookup. Though optional, this parameter is highly important because you are likely to get different results depending on whether you supply TRUE or FALSE to the same formula.

For starters, let's see what Microsoft Excel actually means by "exact match" and "approximate match".

Excel VLOOKUP exact match

If range_lookup is set to FALSE, the formula searches for a value exactly the same as lookup_value. If there are two or more values that match the lookup value exactly, the 1st value found is returned. If an exact match is not found, the #N/A error is returned.

For example, if you use the formula =VLOOKUP(4, A2:B15,2,FALSE), but value 4 is not found in cells A2 through A15, the formula will return #N/A.

Excel VLOOKUP with approximate match

If range_lookup is set to TRUE or omitted, the formula will look up the closest match. More precisely, it searches for an exact match first, but if an exact match is not found, it returns the nearest match, which is the next largest value that is less than lookup_value.

Important note! Vlookup with approximate match requires the values in the first column of table_array to be sorted in ascending order, i.e. from smallest to largest. Otherwise, your formula may not find the correct value.

To better understand the difference between exact match and approximate match, let's make a few more formulas and see what results they return.

Example 1. How to do a vlookup with exact match

As you remember, to search for exact match, you put FALSE in the last argument. Let's take the Animal speed table from the very first example and find out which animal can run 50 miles per hour. I believe you won't have any difficulties with figuring out the formula:




Where E1 is the value to look up.

A VLOOKUP formula with exact match

Please note, our lookup range (column A) contains two "50" values in cells A5 and A6; and the formula returns a value from cell B5. Why is that? Because the VLOOKUP function with exact match returns the 1st found value that matches the lookup value.

Example 2. How to vlookup with approximate match

When using VLOOKUP formulas with approximate match, i.e. with range_lookup set to TRUE or omitted, the first thing you need to do is sort the first column in your table_array in ascending order.

This is very important because your formula will stop searching as soon as it finds the nearest match smaller than lookup_value. If you neglect to sort your data properly, you will end up having really strange results or the #N/A error.
Sort the 1st column in the table_array in ascending order.

And now you can go with one of the following formulas:




Where E1 is the lookup value.

As an example, let's find the animal whose speed is closest to 69 mph. And here's what our vertical lookup has come up with:
A VLOOKUP formula with approximate match

As you see, the formula returns "Antelope" whose speed is 61mph, while we also have Cheetah that runs 70 mile per hour, and 70 is much closer to 69 than 61. So, why does it return "Antelope"? Because VLOOKUP with approximate match retrieves the closest value that is less than the lookup value.

Hopefully, these examples have shed some light on using VLOOKUP in Excel and this function is not alien to you any longer. Now it may be a good idea to revise the essentials to remember the key points better.

Excel VLOOKUP - things to remember!

  1. The VLOOKUP function cannot look at its left. It always searches for the lookup value in the left-most column of the lookup range (table_array).
  2. Excel VLOOKUP is case-insensitive, meaning that uppercase and lowercase characters are treated as equivalent.
  3. If the lookup value is smaller than the smallest value in the first column of table_array, the #N/A error is returend.
  4. If the 3rd parameter (col_index_num) is less than 1, the #VALUE! error is returend. In case it is greater than the number of columns in table_array, the formula will return the #REF! error.
  5. Always use absolute cell references (with the $ sign) for the table_array argument to prevent it from changing when copying your formula to other cells.
  6. When searching with approximate match (range_lookup set to TRUE or omitted), always have the data in the first column of table_array sorted in ascending order.
  7. And finally, remember about the importance of the last parameter. Supply TRUE for approximate match or FALSE for exact match, and it will save you a lot of headache.

Vlookup Wizard for Excel - easy way to write complex formulas

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, embrace the Vlookup Wizard included with of our Ultimate Suite for Excel.

The interactive wizard will walk you through the configuration options to make your own Vlookup formula based on the criteria you specify. Here's how:

  1. Select any cell in your main table, and click the Vlookup Wizard button on the Ablebits Data tab, in the Merge group.
    Vlookup Wizard for Excel
  2. The Vlookup Wizard will show up and ask you to choose your main table and lookup table. Additionally, you specify the following 3 columns:
    • 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.

    On a simple dataset, the wizard picks the column automatically based on the column headers. When you hover over a certain box on the right-hand pane, the corresponding element on the left-side diagram gets highlighted so you better understand what the wizard is doing:
    The interactive wizard to do vlookup in Excel

  3. When finished, click the Insert button. This will insert the formula into all cells of your main table:
    A Vlookup formula is inserted into all cells of the main table.

That was easy, wasn't it?


  • The Back up the worksheet option is selected by default, so you may not worry about the safety of your original data.
  • Due to the proper use of relative and absolute cells references, the formulas can be copied or moved to any column, without you having to update the references.

What else can our wizard do that the standard Excel VLOOKUP cannot? It can look up from right to left!

Left lookup in Excel made easy

As you already know, the Excel VLOOKUP function can only search in the leftmost column of a table. But sometimes you do need to pull values from left. To have it done, use an INDEX MATCH formula that does not care whether your lookup column is on the right or left side of the return column, or have our wizard build such a formula for you automatically :)

Here's an example of a right-to-left lookup in Excel:
Left lookup in Excel

Want to give it a try? A free 14-day trial version of the Ultimate Suite can be downloaded here.

Merge Tables Wizard - quick and easy alternative for Excel VLOOKUP

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. You do not have to invest hours to learn how to use it - the smart wizard will walk you through these 4 easy steps:

  1. Select your main table.
  2. Select the lookup table.
  3. Choose one or several common columns as the unique identifier.
  4. Define whether you want to update values in existing columns or/and add new columns to the end of the table.

Allow the Merge Tables Wizard a few seconds for processing… and here you go:
Merging matching rows from 2 tables using the Merge Tables Wizard

If this looks like a tool that may be helpful in your work, you are welcome to download a trial version.

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

In the next part of our Excel VLOOKUP tutorial we will explore more advanced examples such as performing various calculations with VLOOKUP, extracting values from several columns and more. I thank you for reading and hope to see you next week!

You may also be interested in:

189 Responses to "Excel VLOOKUP tutorial for beginners with formula examples"

  1. Pratap Chandra Maharana says:

    More vlookup function details

  2. madhu says:

    Thanks for your valuable feedback.
    Finding solution in a work sheet row's a1 b1 c1 d1..... Contained descriptions and same column have many part numbers. If a cell reference part number by matching index. description will be auto generated. please let me know if any formula is there?

  3. madhu says:

    Hi Madam
    i sent a mail ur mentioned mail id please find the mail .

  4. madhu says:

    Waiting for ur suggestion.

  5. uday Prasad says:



    With best regards

  6. Mohd Dilshad says:

    The Most Important VLOOKUP function in MS Office (Excel). You can big database in find one person of the result. Thank You

  7. Angel says:

    How I use the VLOOKUP formula in the situation below,

    for example;

    5 4 4 3 4 4 4 5 5 4 5 2

    the total number of 5 in this row is 4, how can I formula it so that I can use sum up a specified number in a row of more than 100 numbers or more?

    Thank you.

  8. Angel says:

    I had tried it, it is EXCELLENT

    Thank you.

  9. Michael S says:

    Thank you for the tutorial on using Vlookup with a separate workbook!

  10. jagadish s says:

    Thank for ur introdution of vlookup

  11. Rosie says:

    Hi, first of all, thanks a lot for giving us these tips and explanations, they are very useful.
    What if I want to find a value that is not on the first column? On the Speed/Animal example, what if I want my Vlookup formula to find the value Antelope so it will show me that animal's speed instead of doing it the other way around.
    I have 2 different sheets, I want to be able to create a Vlookup formula on sheet #1 that will allow me to match a value that is on the H column of the 2nd sheet and I want it to give me the value on the column E of the same sheet (I cannot change what's on the 2nd sheet). I tried something like =VLOOKUP(B8,NOMINA!A:H,5,FALSE), but it doesn't work.

  12. Hello Rosie,

    Because Excel VLOOKUP cannot look at its left, it cannot return a value from a column located to the left of the lookup column. In this can you can use an INDEX / MATCH formula like this:


    Where B8 is the lookup value, column E in NOMINA sheet is the lookup column and column H in the NOMINA sheet is the return column.

    For more details about using INDEX MATCH as a more flexible alternative to VLOOKUP, please see this tutorial:

  13. surekha says:

    2 coloum common vlookup actually I need to compare with booking with stock what I am having. Kindly help me. eg : In sheet1 bookings & in another sheet2 stock. model wise colour is common for both sheet. I need to arrive against stock the booking of the customer name.

    Please help me

  14. Matt says:


    I have a date in dd/mm/yyyy HH:MM:SS (26/11/2014 01:51:08)formatt, I want to use a wildcard vlookup. I tried =vlookup("26/11/2014*", etc etc.

    But no joy, can you help?


  15. sham says:

    Thanks very very very much for your good information ms excel. I want to learn form u more and more from u thanks and regards sham india please send more tips on my email

  16. swethakrishna says:

    hi i am unable to understand this and i am new to this vlookup can you help me out of this problem. ple....Svetlana Cheusheva

  17. Girraj says:

    I have an issue where in sheet 1 I have different no like
    and I have to put data from 2 different sheet which have

    in sheet 2

    in sheet 3

    so what single formula we can use so that data can be come in sheet 1 from sheet 2 and 3

    pls suggest

  18. chica says:

    Hi. Hope you well.im struggling to get resolve the problem with my vlookup. It continues show #NA. Can i please e-mail the workbook to you? I urgently need help and need to know how to avoid this in problem in the future. Many thanks.

  19. Thomas says:

    For problem 2 of the vlookup examples how would one go about figuring that out? I understand it's something like vlookup("Jamie"&"Jackie,$B$5:$E$17... but I'm lost from there. How do you compare the two values to come out with the higher value?

  20. Valentina says:

    Hi! I have a list of weeks that gets refreshed periodically, like:
    2015|05 (2)
    2015|06 (3)
    2015|07 (4)
    2015|08 (5)
    2015|09 (6)
    and so on. And I want to make a vlookup reference to them, but on my other table the dates get refreshed with a little difference, like:
    2015|05 (11)
    2015|06 (12)
    2015|07 (13)
    How can I make the reference work? Because I tried to make the range_lookup cell TRUE (an approximate match), but then the result is not correct.
    Thanks a lot!

  21. Terry Tewell says:

    I have two workbooks. Workbook "A" has been completed and now contains questions and the responses of the Interviewee. Workbook "B" contains a number of additional cells which are the bases of the final report to management. I constructed "VLOOKUP" code to pull questions entered in Workbook "A" and pace them in the appropriate column in Workbook "B". What I want is this to happen only once when Workbook "B" is opened. Can I do this using just formulas or do I need to use VBA code?

  22. Tim says:

    When I type in a vlookup formula, the cell shows the formula not the result?

    What am I doing wrong here?

  23. Afzaal says:

    My query:
    I have two column A & B each had got 10000 entries , A is original and B is typed in. I want to find error in B by comparing both column A & B
    Entries e.g. 1-721-95-43
    Please reply with result oriented solution

  24. dhanuskodi says:

    My Query

    I have 12 sheets in a work book. I wanted to consolidate all the entries in all the work sheet in one sheet through vlookup.

    Please reply with result.



  25. NISHIT SHAH says:

    How can I use vlookup formula using with OR function?
    I have more than 40000 line items from which I have to match the data using vlookup and in some cases cells having error eventhough the data is lying in main table.

    Pls help me.
    CA Nishit Shah

  26. Roy says:


    I have a problem.

    I have two work books. One shows part no and price in different columns. Another work book where I prepare quotations. Is there any formula, so that when I enter the part no in the quotation work sheet, price will be automatically come in the price column of the quotation work sheet

  27. atul tripathi says:

    how to use vlookup for sheet 1 to worksheet same row with one colom.

  28. Data Moses says:

    Hi, I have this problem

    Pers # Surname FullNames Job Titles
    k15126 Abure Data Moses Security Officer

    in another sheet2 i have

    Pers # Surname FullNames Job Title Start Date

    k15126 Abure Data Moses Security Officer 2015-10-02

    I want to vlookup start dates in sheet 2 to include in sheet1 to match their pers # Surname FullName and Jobe Titles


  29. Jemi says:


    May i know how to lock vlooklup target to different workbook.

    Example:Its alwasy lock to Numbers.xlsx

    • Hi Jemi,

      Simply change the workbook name [Numbers.xlsx] and the sheet name Sheet2! to different names. If you do Vlookup within the same sheet, you don't need to specify either the workbook or worksheet name.

      • Jemi says:

        Hi Svetlana,

        Thank you for you replay.There is 2 different Workbook.

        Here the condition in Workbook1

        Workbook2: ECA_partlist.xlsx, There is 2 tab sheet in this book name 'PartList' & ' vlookup'

        The situation is,the condition will auto add path if the ECA_partslist.xlsx open in different location with Workbook1 for example:
        =VLOOKUP(M7,'Z:\Project\SO201504081701 Willowglen\[ECA_partslist.xlsx]vlookup'!A1:AF15,2,FALSE)

        • Jemi says:

          I add abit here,

          Its happend when I save the Workbook1 in different location with Workbook 2(ECA_partlist.xlsx. Not only the vlookoup path effected but 'Name Manager' refer to also will add the path.

  30. Mzee says:

    I have two values Male and female. I want to create a fomular that adds either of them. Say everytime I add M, it aggregates it to 1, 2, 3, 4, 5 to nth value. can someone help.

    Thank you.

  31. Matt says:

    I have two Workbooks that I need combined. On the first workbook (Workbook 1) I pull data from a website that has about 500 names in Column A (along with other data corresponding to the person in the row). I have another Workbook (Workbook 2) that I have different comments (Column E) on the person in Column A. Am I able to pull the new data for Workbook 1 and add the comments from my old Workbook 2 to the first blank Column (Column I)? Every time I pull the new info on Workbook 1 some of the names change so I can't just cut and paste the entire column.

  32. peter li says:

    Clear, step by step and with examples. An excellent tutorial!

  33. Sai says:

    Hi Svetlana,

    In excel , I have selected a cell(b1) and made it a dropdown list.
    Also i have created a table(Table2) AT Sheet1!$H$6:$J$8 -> This table contains information like type(H) tool(I) and version(J).
    Note that version column contains dropdown list mentioning version no.s present.
    Also note that the list in b1 is pointing to table_column(H) (eg. by using data validation src=table2)

    Now my requirement is when i select a value at b1 from the list, then a new Field has to be entered in a2 with the values of the table_columns (I & J)

    • Sai says:

      I have got partial answer by using =VLOOKUP(B1,Table2,3,0), but as i said its not giving the drop down list in a2 its just giving the value same as in table.. I want drop down list in a2 by the above formula

  34. Imran says:


    I have two sheets one sheet has entire data with values & another sheet has selected items which doesn't have any values. i want put the values which selected items.

    For Example:If clothes value is $ 10 in existing sheet. and selected items felter with clothes and i want to put the value 10. how can i do this by using vlookup or any other formula?

  35. lerica says:


  36. lerica says:

    Lots of learning while reading the Q & A.. *clap*

  37. ashik says:

    thanks man, i was going nuts over this. ur solution helped. just needed to click false on range lookup

  38. kupci says:

    Awesome! I think this should be added to your page on troubleshooting why the VLOOKUP doesn't work. Can't believe they set the default to approximate match.


  39. chuck says:

    My spreadsheet has a field containing a drop-down list for Part category. In the field next to it, I have a drop-down list for SubCategory. When I select a sepecific Part category, I would like the SubCategory pull-down list to ONLY contain the pertinent subcategories for the Part Category selected, not everything in the SubCategory list. My SubCategory tab containing the items for the drop-down list holds the Subcategories in one column, and the Category to which the SubCategory is pertinent in the adjacent column.
    What is the best way to have ONLY the Subcategories for a specific Part Category to be displayed in the SubCategory drop-down list without having to make a separate set of lists for each part category?

    • Hello Chuck,
      When creating a drop-down list for the SubCategories, go to Data -> Data Validation, select List and instead of entering the list name in the "Source" field, use the INDIRECT function to reference the value in the cell with the Part Category, e.g.:
      You will need to have named ranges for categories and subcategories. The name of the range with Subcategories must coincide with the value selected as the part category. E.g. If you select "Subcategory1" as your Part category in cell H2, then you'll get the "Subcategory1" drop-down list in I2. If this doesn't help, please send a test spreadsheet to support@ablebits.com, we'll do our best to assist you.

  40. Mary says:

    Thanks very helpful lession

  41. Moin Khan says:

    No doubt your site is very helpful, especially for beginners like me.

  42. sameer says:

    hello excel i am working in a school i have to maintain daily register of cash collection and total amount is to be divided into different heads like tution fee exam fee bus fee i want that formula that itself divide that amount into different heads of different class

  43. Jo says:

    Hi, what should I should I input if the look_up value is a text. Thanks

  44. Pravin says:

    I ve this formula =VLOOKUP(D7,INDIRECT(""&G9&"!B7:AL32"),2,0). I want add hyperlink in this formula as a vlookup result how?

  45. Anil says:

    have two columns in a single work sheet and I want to use VLOOKUP formula (I do not want to use MATCH Formula) to compare these two columns each other and get an output of Matching Items.

    1202 16003
    1206 16010
    16003 21307EXQW33
    16010 21307EXQW33C3
    21307EXQW33 1202
    21307EXQW33C3 1206
    22206EXW33 22210EXQW33
    22210EXQW33 22215EXW33KC3
    22215EXW33KC3 22206EXW33

    I want to get :
    1> Compare and give teh matching output.

    • Hello Anil,
      You can use VLOOKUP formula, e.g. =VLOOKUP(A2,$B$2:$B$11,1,FALSE), but it will simply display the value if it occurs in your lookup column.

      If your task is to see whether or not value in column A is repeated in column B, you can use the following formula:

      I hope this helps.

  46. hanmant says:

    how did four sheet use a Vlookup why Formulas

  47. Monika says:

    Why while using vlookup function #NA# results if the value does exist

  48. jerry says:

    In doing the vlookup here is my fomula,=IF(ISERROR(VLOOKUP(D2,F2:F80833, 1, FALSE)),FALSE,TRUE) and I am trying to repeat the formula where D2 changes on the next cell to D3 and so forth to D92563 but I do not want the second portion of my formula F2:F80833 to change. I cannot click and drag because it will change the 2nd portion of my formula to match. What do I do to copy portions of the formula?

  49. Stephen says:


    I'm using the lookup function and it's behaving oddly: it won't find any text string in one column that starts with P to Z. I have no idea about this! Thanks!

  50. Sanju says:

    What is the formula to compare the exact numbers in two sheets.
    I would like to compare the data in two sheets and find out a exact matches / numbers.
    Eg: In Sheet1 : Column A: 2352,25897,25666,29981
    In Sheet2 : Column A: 29981,23514,2352,25555,25369


  51. Waseem Ahmed says:

    Main Table: Bill of matrial 1

    Colorants Green Orange Maroon Colorants Green
    Lemon Yellow 80.00 75.00 Lemon Yellow 68.00
    Ochre Ochre -
    Bright Red 25.00 60.00 Bright Red -
    Brick Red 20.00 Brick Red -
    Sky Blue Sky Blue -
    Navy Blue 20.00 20.00 Navy Blue 17.00
    TOTAL: 100.00 100.00 00.00 TOTAL: 85.00
    We have generated "Bill of material 1" from Main Table. In Table "Bill of material 1" we do not need zero value or blank cell. Instead we want Table to show initial three rows with data.
    In this we apply formula:
    Kindly help to update formula.

  52. Waseem Ahmed says:

    This text area does not support formatted Excel data. Please improve to explain question properly.
    Kind regards,

  53. Simon NG says:

    I have been learning excel from the internet and this is best website on he subject!

  54. Anthony Chua says:

    I just found out that sometimes when looking for EXACT match, and the cell is a number, there could be some rounding off errors. Then #N/A is shown even though the table shows that there is a match.

  55. Brandon says:

    Holy crap! I just had to say thank you for the =VLOOKUP("*"&A1677&"*",'QEP BOLO CONTRACTS'!$B:$T,3,FALSE) formula!

    The "*"&A1677&"*" Saved my spreadsheet!

  56. Brian Browne says:

    What does this mean? Why the 1+ Many thnks

  57. sepedi says:

    hi i was in an interview yesterday and i couldnt even do vlookup and pivo table can u please help me

  58. shri says:

    please tall me , How to work in two book .
    I need a
    I doing some work in book 1
    like a
    123 - shri - India - lovely person
    and I am open new book (book-2)
    when tipping 123 and a take all information of Shri

    plz help me

  59. Eric Mendes says:

    Hi All ,

    I am facing a serious problem as per below , would you mind to assist please ?

    Sheet1 ( called Physical consumption) has four columns : Date , job number , Part Number Quantity Representative

    Sheet 2 ( called system consumption) as lot of columns , but below are the most important I am working with :
    Date , part Number reference

    The problem is : I want to use a formala that helps me to add on the last column on sheet1 that pick up the refence number in column 2 and had it to a specific part number on the same date as the sheet 2 .

    Thanks for your assistance

  60. Moe says:

    Dear Svetlana and Irina,

    Can you please explain why vlookup does not work when it is used to get data from a named range in an external workbook ?

  61. jayanti velani says:

    please can you help me with a formula
    i have 3 columns a,b,c
    a column have 300 products codes
    b column have 50 products codes these codes appear also in column a
    c column have prices for product codes in column b

    i need to insert a column next to column a and want all the prices from column c that matches the product code from column b to a anything that does not
    match a blank cell should come up

  62. sangita says:

    how can i do a vlookup in the same sheet

  63. Sunil Tripathi says:

    i have sheet wherein 10 site has sold same 10 article .. how do get the data from that sheet . which formula should i use to get the data.

  64. Sunil Tripathi says:

    Row Labels Article Article Description Sum of Qty in Un. of Entry Sum of Sales Value inc. VAT
    3478 108007941 CLMT UTENSIL BAR 200G -4 -60
    3497 108007941 CLMT UTENSIL BAR 200G -7 -105
    3503 108007941 CLMT UTENSIL BAR 200G -2 -30
    3506 108007941 CLMT UTENSIL BAR 200G -8 -120
    3530 108007941 CLMT UTENSIL BAR 200G -2 -30
    3532 108007941 CLMT UTENSIL BAR 200G -2 -30
    3550 108007941 CLMT UTENSIL BAR 200G -1 -15

  65. Diana says:

    Thank you for this! It was very helpful and easy to understand!

  66. Kuria says:

    Hi Svetlana Cheusheva,

    I have a file with more than 20 sheets (Departments) and a major file where the rest of the sheet get the data from. I have use a simple way of getting the data from the main file by typing =sign and the the main sheet and last the cell that contains the data i want to appear in departments. This was working well untill i realized after i sort the data in the main file, the departments data get messed up coz am referring to a cell but not the content in it. Is there a formula i can use in a way that if is edit the main file, the changes are reflected in the departments file?

  67. Muhammad Zaheer says:

    First of all thanks for nice and very useful information sharing.

    I want to make database for my shop. In which I want to make one "Inventory" sheet second "Stock in" sheet third "Stock out" sheet. Now question is that how inventory sheet automatically update with transaction from both stock in and stock out sheet. for example I have 5 Energy savers in my inventory. I add 2 more and sold 3 then the inventory sheet automatically update as(5+2-3=) 4 energy saver in stock.

    Thanks in advance.

  68. Sunday says:

    I have the following problem that I d not have idea how to create the formula in Vlookup.
    Create a vlookup which will populate the list of projects to be selected based on the following criteria
    1. Budget of $4,000,000
    2. Populate the Include column to indicate what projects should be selected, based on the project in each region with the highest NPV while staying below the total budget constraint

    Region Project Cost NPV Include
    Europe 1 $1,200,000 $172,036
    Europe 2 $2,000,000 $362,283
    Europe 3 $500,000 $147,487
    Asia 4 $600,000 $72,076
    Asia 5 $700,000 $129.35
    Asia 6 $3,000,000 $527,127
    Asia 7 $1,500,000 $343,632
    Africa 8 $200,000 $52,836
    Africa 9 $2,400,000 $275,936
    Africa 10 $1,000,000 $175,824
    Constraint 4,000,000

  69. sathish says:

    Private Sub Program4()
    Dim LastRow As Long
    Dim LastColumn As Long
    Dim i As Integer
    Dim j As Integer
    LastRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    LastColumn = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
    For i = 2 To LastRow
    For j = 1 To LastColumn
    Sheets("Sheet2").Cells(i, 2) = Application.VLookup(Sheets("Sheet2").Cells(i, 1), Sheets("Sheet1").Range("A2:D7"), Sheets("Sheet1").Cells(i, 4), False)
    Next j
    Next i
    End Sub
    Above example i have an error..What that error is?
    Task is:using two variables and lastrow and lastcolumn by using for loop in vlookup..
    task:Sheet1 Sheet2
    Id Name Salary Dep Id Dep

  70. Egypt says:

    Thank you so much!!!
    extremely helpful

  71. anu says:


    I m facing some problem to apply matching formula in worksheet...
    may u help me,,,,,

  72. Tom says:

    I need to compare to columns and highlight cells in column A when a match is not found in column B.

    I am using Excel 2010. Do you have an example that will work to accomplish this. I will also need to compare the columns in reverse. Highlighting in column B and cells not found in column A.

  73. nani says:

    I need to compare two work books in that one sheet have number of same site id's in column 'A' but description is different then how can i do that,it shows the wrong data why because same id's repeated that's why it shows the wrong data.

  74. sadiq says:

    a b c d g
    49314 Watch 49314 WT00009303
    49314 Watch WT00009303
    49314 Watch WT00009303
    49394 Watch 49314 WT00009303

    vlookup a & c and pick d in g

  75. jackie says:

    suppose i have list of data in different worksheets sheet 1, sheet 2, sheet 3, sheet 1 consists of data up to 1-100 sr. no. sheet 2 101-200 sr. no. and sheet 3 up to 201-300 how can i lookup all at once using vlookup i shows me value error when i trying to select all data from all worksheets

  76. Sir Peter says:

    I can do vlookup now. thank so much for the simple explanation.

    This is very helpful.

  77. Khurram says:

    I appreciate your site.it is very informative.
    In above formula I want col_name in stead of col_index.

    With best regards

  78. Md.Abdul Momin says:

    How to do work vlookup From another excel sheet1 & sheet 2

  79. Myra says:

    I am trying to add amounts from one column AB, if in column L there is text that contains (G).

    How do I write this formula?
    The (G) is not always written in the same exact place. It can be in the front of a sentence, middle, or end.

    So basically need if:
    Column L has (G) then add the amount from column AB.

    and the (G) is written this exact way, so its throwing me off on how to write the formula since it also has parentheses.


  80. Lynn says:

    I created an Excel spreadsheet that used VLOOKUP formulas, with version 2007. I wanted to update it, so I recreated it by copying it to my Excel 2013 version. Everything seemed to work fine, except for one item. When I try to insert a row into the database (Sheet 2) and create a new look up item in my inventory, it returns an "NA" response on Sheet 1. I tried to figure it out, and it seems that it may be because of a previous link to the 2007 version that is not there now. I tried to Edit/Delete or Break the link, but it still doesn't work. Can you help please?

  81. Suraiya says:

    what's the function to calculate Unit Number?

  82. Syed says:

    Excellent tutorial, explained each and every steps clearly with good examples.

    I salute you !!!!👍🙏

  83. kishori says:

    How to find out data is in one column but not in other column?

  84. mike says:

    in vlookup what if there's two or more same data in column of table array?

  85. SANA says:


  86. Rajesh K P says:

    Hello Team,

    is there any formula to pull 2 different name from 2 same code?

    for exp:-

    125 - jack
    126 -Jorge
    125 -Rick

    now i have 2 code and i want 2 different name

    125 - required (jack)
    125 - required (Rick)

  87. Adiwal says:

    (is it possible that i can sum amount of multiple cell by search of single name).

  88. Ermest Fernandez says:

    Worth reading.

  89. Hrender Singh Butola says:


  90. HAKM says:


  91. HAKM says:


  92. josh says:

    item number shoe type price
    583 kitten heel $57.00
    612 mules $40.00
    471 platforms $30.00
    982 pumps $45.00
    773 sandals $37.00
    156 slingbacks $48.00
    229 stacked heel $70.00
    312 stiletto $85.00
    823 wedge $35.00

    lookup value for kitten heel:
    lookup price for item number823:
    lookup shoe type for item number 471:

    how can i find the formula for each of this problems

  93. TOM says:

    A B C
    name age seating
    ishan 33 balcony
    behoora 34 floor
    Adam 51 podium
    smith 42 balcony

    I J
    name seating applicable
    Adam podium
    ishan balcony
    greg #N/A


  94. maqsood says:

    you are really cute.

  95. Hamid hamedie says:

    Thanks too match it is very useful for my

  96. Hamid hamedie says:

    You are the best one in word

  97. onyenezi austin says:

    i love your write but i have no knowledge of vlookup at all. so i find it difficult to understand it. please help me to understand this by sending me more write up on this please. thank you.

  98. Lisa H says:

    Hi I am using a Vlookup and it is returning formula however I now want to do a vlookup against the first vlookup I did, I have done this but a very long time ago and cannot remember, I basically need to use the value from the first lookup to drive another look up.


  99. Lisa H says:

    These are the looks up I tried

    No 1 =(VLOOKUP(C14,'Data '!A:B,2,FALSE)) - this returns what I want

    No 2 =IFERROR(VLOOKUP(D14,'Score sheet'!A3:B103,2,FALSE),"") - This doesn't return what I need.


  100. Lisa H says:

    I need no 2 to look up against what has been returned in look up no 1

Post a comment

Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
60+ professional tools for Excel
60+ professional tools for Excel
2019–2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Sheila Blanchard