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

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

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

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

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

  5. Thanks very helpful lession

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

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

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

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

  10. WOW!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  31. Hi

    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?

    Thanks
    Matt

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

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

    =INDEX(NOMINA!E:E,MATCH(B8,NOMINA!H:H,0))

    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:
    https://www.ablebits.com/office-addins-blog/excel-index-match-function-vlookup/

    1. Actually the returned is column E in the NOMINA sheet and column H is the lookup column

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

  35. Thank for ur introdution of vlookup

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

  37. I had tried it, it is EXCELLENT

    Thank you.

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

      1. Thanks, excellent example

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

  40. EXCELLENT

    WANT TO LEARN MORE FROM YOU..Its too good

    With best regards
    UDAY

  41. Waiting for ur suggestion.

    1. Hello Madhu,

      Thank you for your workbook. I am sorry, we are overloaded with work at the moment. We'll try to look at your task as soon as we can.

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

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

    1. Hi Madhu,

      I think it will be easier for me to write the correct formula if I can see your sample data. If you can send me your workbook at support@ablebits.com and give an example of the expected result, I will try to help.

      1. hi svetlana nice working excel working and as only work
        i am freind and frankily

      2. Hi Svetlana,
        Please help me I want to find value from 2 pairs of different columns in same sheet by inserting Vlookup formula.
        Please suggest inputs.

        regards,
        Arjun Yadav

      3. Hi Svetlana,

        May I send you sample data sheet for vlookup formula.

  44. More vlookup function details

    1. You can find more information about various aspects of Excel Vlookup in the links posted at the end of this tutorial.

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