Comments on: Excel VLOOKUP tutorial for beginners with formula examples

This VLOOKUP tutorial explains the syntax and provides a number of examples that illustrate the most common usages of the VLOOKUP function in Excel. Continue reading →

Comments page 5. Total comments: 234

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

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

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

    I salute you !!!!??

  4. what's the function to calculate Unit Number?

  5. 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?

    1. Hello, Lynn,

      we will be able to help you if you send us a small sample workbook with data source and the result you want to get to support@ablebits.com. We will take a look at the data and get back in touch. Don't forget to link this article and your comment.

  6. 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.

    Thanks

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

    1. Hello!

      Simply include the sheet name in the table_array reference, as explained in Vlookup from another sheet. For example:

      =VLOOKUP("text",Sheet2!$A$2:$B$10,2,FALSE)

  8. I appreciate your site.it is very informative.
    Vlookup(value,range,col_index)
    In above formula I want col_name in stead of col_index.

    With best regards

    1. Hello Khurram,

      You can use the Match function to create a dynamic column reference. The detailed explanation and formula examples can be found here: Excel Vlookup and Match

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

    This is very helpful.

  10. 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

  11. 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

  12. 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.

  13. 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.

  14. SIr/Maim

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

  15. Thank you so much!!!
    extremely helpful

  16. 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
    1

  17. 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
    Total
    Constraint 4,000,000

  18. 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.

  19. 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?

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

  21. 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

  22. 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.

    1. Hello Sunil Tripathi,

      For us to be able to assist you better, please send us a small sample table with your data in Excel and include the expected result. Thank you.

  23. Hello
    how can i do a vlookup in the same sheet

  24. 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

  25. 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 ?

    1. Hello Moe,

      It should work if you include the name of the workbook where you have the named range, e.g. [workbook name]!NamedRange

  26. 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

  27. hello
    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

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

  29. =E391*(1+VLOOKUP($D391,$C$12:$AJ$15,COLUMNS($C$12:F$12)))
    What does this mean? Why the 1+ Many thnks

  30. 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!

  31. 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.

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

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

  34. 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:
    =INDEX(B$4:$B$9,MATCH(F5,$A$4:$A$9,0),MATCH($G$3,$B$3:$D$3,0))/$B$10*$G$10
    Kindly help to update formula.
    Regards,
    Waseem.

  35. Hello,
    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

    Thanks,
    Sanju

  36. Hello,

    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!

  37. 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?

    1. Hello Jerry,

      You can add a dollar sign before the column and row references to make them absolute:
      =IF(ISERROR(VLOOKUP(D2,$F$2:$F$80833, 1, FALSE)),FALSE,TRUE)

      This will keep the range invariable when you copy the formula.

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

    1. Hi Monika,

      Because it is designed by Microsoft this way. If you'd rather display a blank cell or some message when a lookup value is not found, you can enclose your Vlookup formula in the IFERROR function:

      =IFERROR(vlookup(), "")

      You can find an example of a real-life formula with the detailed explanation and screenshots in this tutorial: Why Excel VLOOKUP is not working

  39. how did four sheet use a Vlookup why Formulas

    1. Hello Hanmant,
      Could you please clarify your question? If you are not sure if the VLOOKUP function does what you need, please describe your task. We'll do our best to assist you.

  40. 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.

    1. 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:
      =IF(ISERROR(VLOOKUP(A2,$B$2:$B$11,1,FALSE))=FALSE,"duplicate","Unique")

      I hope this helps.

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

    1. Hello Pravin,
      Could you please describe your task in more detail? Are you trying to add a hyperlink with the lookup value as the "friendly name"? If possible, please send a test spreadsheet with the description of your data and the expected results to support@ablebits.com.
      We'll do our best to assist you.

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

    1. Hi Jo,

      You should input that text in double quotes, e.g.
      =VLOOKUP("apples", A2:B20, 2)

  43. 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

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

  45. Thanks very helpful lession

  46. 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?

    1. 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.:
      =INDIRECT($H2)
      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.

  47. 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.

    THANKS!!!!

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

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

  50. WOW!

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)