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

  1. This formula is extremely awful! I keep getting few arguments, I'm doing evaluation by my school teachers and this keeps happening to me! I tried your formula and nothing worked at all!

  2. I am impressed with vlookup method but i have a problem of how to apply it! I have two sheets of workers, my intension was to send data of workers (454)to my boss but what has been sent are only 435 workers, my question is how can I use vlookup to identify the names which were not sent?

  3. i learn everday and i learn many things

  4. DEAR SIR
    I AM RAVI , NOW I AM WORKING VLOOKUP FORMULA ,I WANT TO FIX THE LOOK UP VALUE , HOW ITS WORK

  5. Is there a way of looking up a rate of pay (for an individual worker) from a table where there are multiple agencies, multiple classes of worker (e.g. Healthcare assistant, nurse, doctor) and the rate varies depending on the time/day? Thanks!

  6. My formula is not working. I am using Vlookup to search in a differente workbook and Iall I am getting is "#REF". Already check the column's format and validated instruction. I am not sure what is wrong.
    =VLOOKUP(C3,'[Plan Membership Comparison.xls]Sheet5'!$A$2,5)

    1. Hi Roxanna,

      In the table_array argument, you need to supply a lookup range, while you only have a single cell ($A$2).

  7. How can I find a texts with same first 2 characters and return the value?
    like
    ASD-12345
    ASD-o9093
    DFD-00000
    Am I able to search to return the results only with ASD-xxxx? Thank you

  8. How can select the data at a time vlookup results

  9. Madam,
    How can i attach data from datasheet of other folders?

  10. MADAM HOW CAN I USE VLOOKUP FORMULA IN MULTI SHEETS?

  11. Hi I am using this formula to look up herbicide name by active ingredient
    =LOOKUP(CO80,'Herb list2'!$A$2:$B$77,'Herb list2'!$B$2:$B$77)
    IS there any way to change this formula to set to exact match (FALSE)??
    I tried
    =LOOKUP(CO80,'Herb list2'!$A$2:$B$77,'Herb list2'!$B$2:$B$77, FALSE) an got error msg
    "You've entered too many arguments for this function"
    U R right lots of headaches!!

  12. Hello
    please make a video for this

  13. I found this site very useful, wherever i forgot or stuck somewhere, it help me solve my problems. Thanks and best regards,

  14. I am happy with your excel examples thank you and need more examples for learning

  15. need further clarification please

  16. I am not able to filter my spreadsheet, i have value 1 to 1000 numbers but i want to filter it based on some random numbers or list of random number.

    How i can do it excel. Please suggest for the same.

    Appreciate your help.

  17. My VLookup isn't working. I run a report in a software that copies data into an Excel file. I then save the file as an Excel Workbook, and re-open the file in the same instance as my other workbook.

    I then copy the data to my other excel workbook, where I have a VLOOKUP set up that references the descriptions of the data with their category code (which isn't included on the original report). However, I'm getting the #N/A error type.

    I double checked the formatting and it's identical. Spelling and spacing is also identical. What's odd is, if I delete the data and re-type it into the space the VLookup works. I'd prefer not having to do that each time and am wondering what else might be causing this?

  18. My question above is urgent. Thank you.

  19. Hello,
    I got zero results in my VLOOKUP formula. My formula is =IF([Insurance Charge]="Yes",120, IF(E2="No",0,VLOOKUP(E2,G2,'Rental Information'!A3:D7,FALSE))) And the question is Use an exact-match VLOOKUP function nested in an IF function to return the monthly insurance charge if the renter has purchased insurance (“Yes” in the Insurance column). Use the furniture’s Group Code in the Rental Data worksheet and the Monthly Insurance column in the Rental Information worksheet Furniture Rental Charges table (predefined name RentalCharges) to return the appropriate charge. If the renter has not purchased insurance, return 0. Don’t forget to multiply it by the Rental Period to get the final amount.

    What's wrong with my formula?

  20. i have 2 coloms in one colom have absenties names and another column have full name list, how i can find presents name list from both list

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

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

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

  24. Thank you! This is VERY helpful!

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

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

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

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

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

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

  31. hello

    please help me.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  48. You are the best one in word

  49. Thanks too match it is very useful for my

  50. you are really cute.

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