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 6. Total comments: 234

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  25. Thanks very helpful lession

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

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

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

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

  30. WOW!

  31. Hello,

    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?

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

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

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

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

    1. Hello Matt,
      This is exactly what the VLOOKUP function does. Enter the formula into column I of workbook 1 to pull the corresponding comments from Workbook2, e.g.:
      =VLOOKUP($A2,[Workbook2.xlsx]Sheet5!A2:E26,5,FALSE)

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

  36. Hi,

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

    Example:Its alwasy lock to Numbers.xlsx
    =VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)

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

      1. Hi Svetlana,

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

        Here the condition in Workbook1
        =VLOOKUP(M7,[ECA_partslist.xlsx]vlookup!A1:AF15,2,FALSE)

        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)

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

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

    Help

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

  39. Hai

    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

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

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

    Regards

    dhanuskodi

  42. 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
    Regards,
    Afzaal

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

    What am I doing wrong here?

    1. Tim,

      Most likely you have inadvertently activated the Show Formulas mode in your worksheet. To turn it off, press the CTRL+` shortcut. If it's not the case, check out other possible reasons and fixes: Why is Excel showing formula, not result?

    2. Hello Tim,
      Please make sure you enter the equals sign before the formula, e.g.
      =VLOOKUP(40,Sheet2!A2:B15,2)

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

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

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

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

  48. Hi,
    I have an issue where in sheet 1 I have different no like
    1
    2
    3
    4
    5
    6
    and I have to put data from 2 different sheet which have

    2
    4
    6
    in sheet 2

    1
    3
    5
    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

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

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

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