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

  1. How i can get the corrosponding value of column B , column C and column D in sheet 1 from sheet 2 with excel formula. Value In column A of both the sheet are matching but not serial wise.

  2. Thank you for such a clear and easy to understand training. Most appreciated.

  3. need to know how to copy and paste some cells from a page of a spread sheet to an email WITHOUT any formula data included and look exactly as the copied cells

    1. Select your entire spreadsheet, do a copy paste, next do a copy paste special and choose Values. Once you do this the exact number and not the formula will paste to your spreadsheet.

  4. Thank you! This is VERY helpful!

  5. VLookup expanded here is "Too" Complicated..!!!

  6. 'v-03'!H33 I have four formulas that are similar but only one doesn't calculate total please help

  7. Explain V lookup together to Choose function Any Example?

  8. Helpful excel,but can you sheet provide for website.
    Thanks

  9. How would I use vlookup formula to access various files - ie I want the user to enter in the file name into a cell (A1) and the various VLOOKUP formulas would use the A1 text to feed into the external file reference.

    example:
    =VLOOKUP("Die Size", 'C:\Users\Dwayne\Desktop\[(A1).xlsx] Set-up'!$A$16:$AB$16,13,TRUE)

    And in this case the A1 is a cell reference in the same worksheet; and then the user could get the various info without editing every formula.

    advice appreciated!

  10. If the unique identifiers is repeated a few time in the table for example "Product1" appears three times and I would like the corresponding data or all three times it appears, may I know how it can be done. The three corresponding data is text and should appear on three different lines. Thanks for your help.

  11. hello

    please help me.

  12. Big thanks to Microsoft for inventing vlookup function.
    As a Senior manager I rely on this, not my staff.
    They all leave me. They hate me.

  13. I have to register daily sales details, for example 01-01-2018 Raju sales in rupees Rs.1000/-, Ramu sales in rupees Rs.1500/-, now I need 01-01-2018 individual sales amount in another sheet

    1. You may do with helper column which you will put serial number to make each 01-01-2018 unique then the lookup formular will use those unique ref to pull their respective sales

  14. Hello! I have a VLookup that I am using with the IF function to pull pay rates (regular/overtime) and match it with employee names. My data is in 3 columns (Employee,Reg. Rate, OT Rate) and the lookup is checking 2 columns (Employee, Pay Type) for data. It's working great EXCEPT, I cannot get the N/A to hide if the columns are blank. Below is the current formula:

    =IF(B8="overtime", VLOOKUP(A8, EmployeeRates, 3, FALSE), VLOOKUP(A8, EmployeeRates, 2, FALSE))

    Any help would be greatly appreciated.

    1. =iferror(IF(B8="overtime", VLOOKUP(A8, EmployeeRates, 3, FALSE), VLOOKUP(A8, EmployeeRates, 2, FALSE)),o)

  15. Your wildcard examples work on no data, but the exact values you used in your example. I tried to replicate your using the cell contents example with the animal table using =VLOOKUP("*"&C1&"*",$A$2:$B$13, 1,FALSE) where C1 has Coy typed into it. It should find "Coyote" and return the speed but it does not. I cannot figure out what is going wrong.

  16. How do I identify records from one worksheet A which is not in worksheet B and then copy only those record from A to B which is not in B.Please help.
    Thanks

  17. So I need to run a report where I need to count up how many times an item shows up across 12 different sheets (each sheet is a month of data). Is there a way to use vlookup to look in an entire workbook without having to input every worksheet name in the formula?

    1. Hello, Jason,

      If you need to count up how many times an item shows up in your sheets, please try the combination of COUNTIF and SUMPRODUCT functions. First you need to create a range containing the names of the relevant sheets, and then use the following formula:

      =SUMPRODUCT(COUNTIF(INDIRECT("'"&A2:A14&"'!A2:A10"),C2))

      Where &A2:A14& is the list with the sheets names; A2:A10 is the data range that you want to count the specified value across worksheets; C2 is the cell with the value you want to count up.

      Hope this will help.

  18. I am trying to reference another spreadsheet with the name of customers in Column B and needing to reference their spending which is located in column D and divide column D by 4. There are 700+ customers so I want to ensure that I reference the correct customer with their spending instead of having to key all manually.
    Any help you can provide will be greatly appreciated.

    1. Hello, Deb,

      I'm sorry, it is not very clear what result you want to get. Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? I kindly ask you to shorten your tables to 10-20 rows/columns and include the link to your blog comment.

      We'll look into your task and try to help.

  19. I am trying to reference another spreadsheet with the name of customers in Column B and needing to reference their spending which is located in column D and divide column D by 4.
    Any help you can provide will be greatly appreciated.

  20. I can't get my Vlookup to work. I used the same formula last year with no issues. I have excel 2016. I have 2 sheets in one workbook and here is my formula
    =VLOOKUP(A2,'Pay Register Total'!A$2:F$1762,6,FALSE)
    The formula returns #N/A on every line.

    I have the same data, supplier number, in the first column on both sheets.

    Please help!!
    thanks,
    Margaret

    1. Check the format of each column being used in your Vlookup. The #N/A often results due to formatting mismatch.

  21. I think you are point 7 inverted.

    7. And finally, remember about the importance of the last parameter. Supply TRUE for exact match or FALSE for approximate match, and it will save you a lot of headache.

    1. Hi Suhendra,

      Good catch, thank you! Fixed.

  22. I knew there had to be a way to look up one cell and then return a neighboring cell, and I found it here. Thank you so much for this simple tutorial, it saved me a ton of time!

  23. I've solved it, realised should be TRUE instead of FALSE thanks anyway

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

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

    Thanks

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

    Thanks

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

  28. You are the best one in word

  29. Thanks too match it is very useful for my

  30. you are really cute.

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

    HERE IS WHAT I USED TO FILL IN COLUMN J BUT WANT TO PUT IN "DID NOT ATTEND" WHERE THE #NA CAN'T QUITE FIGURE IT OUT WHERE TO PUT IN STATEMENT
    =VLOOKUP(I2,$A$2:$C$5,3,FALSE)

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

    1. Hello, Josh,

      if your table starts from A1, you can use try the following formulas for every lookup you indicated:
      =VLOOKUP("kitten heel",B2:C10,2,FALSE)
      =VLOOKUP(823,A2:C10,3,FALSE)
      =VLOOKUP(471,A2:C10,2,FALSE)

      Hope this helps!

      1. thanks for that

  33. 10087-22
    1747-33
    10047-45
    10008-55
    10099-66
    IN THIS SITUATION HOW CAN I MAKE SORTING BY NUMBERS

  34. 1747
    10047
    1008
    10087 ITHIS SITUATIOPN HOW CAN I MAKE SORTING SMALL TO LARGE

  35. hi

  36. Worth reading.

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

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

  39. HOW TO RECONCILE TWO DIFFERENT SHEETS HAVING SIMILAR DATA.

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

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

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

    I salute you !!!!??

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

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

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

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

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

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

    This is very helpful.

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

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

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