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

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

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

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

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

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

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

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

  8. You are the best one in word

  9. Thanks too match it is very useful for my

  10. you are really cute.

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

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

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

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

  15. hi

  16. Worth reading.

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

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

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

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

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

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

    I salute you !!!!??

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

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

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

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

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

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

    This is very helpful.

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

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

  31. I need to compare two work books in that one sheet have number of same site id's in column 'A' but description is different then how can i do that,it shows the wrong data why because same id's repeated that's why it shows the wrong data.

  32. I need to compare to columns and highlight cells in column A when a match is not found in column B.

    I am using Excel 2010. Do you have an example that will work to accomplish this. I will also need to compare the columns in reverse. Highlighting in column B and cells not found in column A.

  33. SIr/Maim

    I m facing some problem to apply matching formula in worksheet...
    may u help me,,,,,

  34. Thank you so much!!!
    extremely helpful

  35. Private Sub Program4()
    Dim LastRow As Long
    Dim LastColumn As Long
    Dim i As Integer
    Dim j As Integer
    LastRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    LastColumn = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
    For i = 2 To LastRow
    For j = 1 To LastColumn
    Sheets("Sheet2").Cells(i, 2) = Application.VLookup(Sheets("Sheet2").Cells(i, 1), Sheets("Sheet1").Range("A2:D7"), Sheets("Sheet1").Cells(i, 4), False)
    Next j
    Next i
    End Sub
    Above example i have an error..What that error is?
    Task is:using two variables and lastrow and lastcolumn by using for loop in vlookup..
    task:Sheet1 Sheet2
    Id Name Salary Dep Id Dep
    1

  36. I have the following problem that I d not have idea how to create the formula in Vlookup.
    Create a vlookup which will populate the list of projects to be selected based on the following criteria
    1. Budget of $4,000,000
    2. Populate the Include column to indicate what projects should be selected, based on the project in each region with the highest NPV while staying below the total budget constraint

    Region Project Cost NPV Include
    Europe 1 $1,200,000 $172,036
    Europe 2 $2,000,000 $362,283
    Europe 3 $500,000 $147,487
    Asia 4 $600,000 $72,076
    Asia 5 $700,000 $129.35
    Asia 6 $3,000,000 $527,127
    Asia 7 $1,500,000 $343,632
    Africa 8 $200,000 $52,836
    Africa 9 $2,400,000 $275,936
    Africa 10 $1,000,000 $175,824
    Total
    Constraint 4,000,000

  37. First of all thanks for nice and very useful information sharing.

    I want to make database for my shop. In which I want to make one "Inventory" sheet second "Stock in" sheet third "Stock out" sheet. Now question is that how inventory sheet automatically update with transaction from both stock in and stock out sheet. for example I have 5 Energy savers in my inventory. I add 2 more and sold 3 then the inventory sheet automatically update as(5+2-3=) 4 energy saver in stock.

    Thanks in advance.

  38. Hi Svetlana Cheusheva,

    I have a file with more than 20 sheets (Departments) and a major file where the rest of the sheet get the data from. I have use a simple way of getting the data from the main file by typing =sign and the the main sheet and last the cell that contains the data i want to appear in departments. This was working well untill i realized after i sort the data in the main file, the departments data get messed up coz am referring to a cell but not the content in it. Is there a formula i can use in a way that if is edit the main file, the changes are reflected in the departments file?

  39. Thank you for this! It was very helpful and easy to understand!

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

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

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

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

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

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

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

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

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

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

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

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