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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)