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

  1. you are really cute.

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

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

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

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

  6. hi

  7. Worth reading.

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

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

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

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

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

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

    I salute you !!!!??

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

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

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

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

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

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

    This is very helpful.

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

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

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

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

  24. SIr/Maim

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

  25. Thank you so much!!!
    extremely helpful

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  42. I have been learning excel from the internet and this is best website on he subject!

  43. This text area does not support formatted Excel data. Please improve to explain question properly.
    Kind regards,
    Waseem

  44. Main Table: Bill of matrial 1

    Colorants Green Orange Maroon Colorants Green
    Lemon Yellow 80.00 75.00 Lemon Yellow 68.00
    Ochre Ochre -
    Bright Red 25.00 60.00 Bright Red -
    Brick Red 20.00 Brick Red -
    Sky Blue Sky Blue -
    Navy Blue 20.00 20.00 Navy Blue 17.00
    TOTAL: 100.00 100.00 00.00 TOTAL: 85.00
    We have generated "Bill of material 1" from Main Table. In Table "Bill of material 1" we do not need zero value or blank cell. Instead we want Table to show initial three rows with data.
    In this we apply formula:
    =INDEX(B$4:$B$9,MATCH(F5,$A$4:$A$9,0),MATCH($G$3,$B$3:$D$3,0))/$B$10*$G$10
    Kindly help to update formula.
    Regards,
    Waseem.

  45. Hello,
    What is the formula to compare the exact numbers in two sheets.
    I would like to compare the data in two sheets and find out a exact matches / numbers.
    Eg: In Sheet1 : Column A: 2352,25897,25666,29981
    In Sheet2 : Column A: 29981,23514,2352,25555,25369

    Thanks,
    Sanju

  46. Hello,

    I'm using the lookup function and it's behaving oddly: it won't find any text string in one column that starts with P to Z. I have no idea about this! Thanks!

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

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

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

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

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