My Online Training Hub

Microsoft Office Online Training

Get our newsletter and join over 80,000
people who learn Excel with us every week
You are here: Home / VLOOKUP

Leave a Reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Comments

  1. I have a Excel file(original) with part numbers, description and prices. We have added columns for minimums and location.

    ok now prices have changed, so I now have another spreadsheet with more part numbers and updated prices. I need to get the new prices, descriptions and prices to the original, without the “Min” & “Loc” columns’ rows shifting when new part numbers are inserted

    Reply

    • Of course Raj, you can copy it and paste it in an excel cell 🙂
      You will have to adjust the ranges to your data range. If you need help, use our foum to open a new topic, you can upload sample files if needed.
      Catalin

      Reply

  4. Hi Maam Mynda Tracy

    I need your help!

    can i have this formula in Microsoft excel

    REGULAR TIME 8:00AM – 12:00PM

    Here the situation:

    time in 8:45 AM
    time out 12:00 PM

    Result: 0:45 minutes late

    Can you help me?

    Reply

    • Hi Johnny,
      The formula should be built like this(the paranthesis are important, use them as below):
      =(Regular End Time-Regular Start Time)-(time out-time in). This will be equal to: 4:00-3:15=0:45
      Catalin

      Reply

  6. Hi mynda I am working vba and MS excel making dashboard
    as beginner would need all those dashboard which were posted on your website how can i get it can you mail me some excel dashboard need to learn it

    Reply

    • Dear Mynda Treacy,

      I have try to use formula to extract data from a tabel and place it to different sheets but i have fail to do that, I use vlookup and iF function but I couldn’t make it (some of the data are the same).
      I can send you the file so you can understand the situations.
      What formula you suggest me to use?

      Thanks,
      Dritan

      Reply

      • Hi Dritan,
        If you have duplicates, this tutorial will help you: lookup-and-return-multiple-matches There is a sample file you can download and test.
        You can use our Help Desk to upload a sample file if you still have problems. A detailed description of what are you trying to do will help us understand your situation.

        Cheers,
        Catalin

        Reply

  8. I have a recurring problem when pasting values into a spreadsheet from a database query (Hyperion especially). A vlookup (already in place and working) using the pasted values won’t work until I edit each value in the formula bar and hit ENTER. Then the vlookup works.

    I can read the values and execute other functions on that value. It just won’t work for vlookup.

    Any advice would be appreciated.

    Does anyone know a

    Reply

    • Hi David,

      Have you tried pasting the values in with Paste Special > Values as opposed to just pasting them in using CTRL+V or right-click > Paste?

      Another option is to run them through the Text to Columns tool, or use a formula like TEXT for text or VALUE for numbers to fix the format of the cells.

      Let me know if any of those options doesn’t work. If you can send me an extract of your workbook and the formula not working via the help desk, I can take a look at it.

      Kind regards,

      Mynda.

      Reply

  9. Hello Mynda,

    My Problem:
    I want to Lookup the Code in Workbook1 that is located is listed in Workbook2,
    then I want to Match the Qty Dispense in Workbook1 against what is listed in Workbook2,
    finally, if the Color-Code and Qty Dispense match, I then want to return the PRICE from WORKBOOK2 to cell “C” in Workbook1.

    Hello Mynda,

    My Problem:
    I want to Lookup the Code in Workbook1 that is located is listed in Workbook2,
    then I want to Match the Qty Dispense in Workbook1 against what is listed in Workbook2,
    finally, if the Color-Code and Qty Dispense match, I then want to return the PRICE from WORKBOOK2 to cell “C” in Workbook

    WORKBOOK1 WORKBOOK2
    CODE1 QTY DISPENSE PRICE TAB1 TAB2 TAB3 TAB4 TAB5
    CODE1 CODE2 CODE3 CODE4 CODE5
    RED110 5 $3.00 RED110 PURPLE7 ROSE10 BLUE2
    YELLOW99 100 $4.00 5 70 120
    ROSE10 70 $5.00 $3.00 $5.00 $5.00
    LILAC11 25 $3.00 10 140 240
    BLUE2 120 $5.00 $6.00 $10.00 $10.00
    PINK3 30 $3.00 5 70 240
    PURPLE7 120 $4.00
    ORANGE9 24 $3.00

    Reply

    • Additional Question:
      Can I do a multiple MATCH? Example:
      VLOOKUP(A2&H2, INDIRECT(“‘”&INDEX(SheetList,MATCH(1&2,–(COUNTIF(INDIRECT(“‘”&SheetList&”‘!$A$1:$D$25″),A2)>0),0))&”‘!$A$1:$D$25”), 3,FALSE)

      My VLOOKUP values work (A2&H2), it recognizes my value.
      But, my MATCH values are not being recognize. What is the proper syntac “MATCH(1&2, “?

      Reply

      • Hi Mary,

        The MATCH should work like that. Are you sure the values are present in the lookup range? i.e. 1&2 will become 12 as text not a number, so it will be looking for a value formatted as text too.

        Try this:

        Type ’12 in a cell B1 (the ‘ formats the 12 as text and will only be visible when viewed in the formula bar). Then in another cell enter this MATCH formula: 

        =MATCH(1&2,B1,0)

        Your result should be 1. Now if you type the number 12 in B1 you’ll get a #N/A error. Hopefully this will help you understand the difference.

        Kind regards,

        Mynda.

        Reply

    • Hi Mary,

      I had a look at this last night but I couldn’t follow the data as the comment has squashed it all together. Are you able to send me your workbooks via the help desk so I can take a look?

      Cheers,

      Mynda.

      Reply

  10. I am working on a job costing worksheet where I enter data from my field workers on a daily basis. I enter worker rates of pay, material prices per unit, equipment use by type and rate/hour (or day). I incorporate lookup tables to get my prices and then summarize my totals at the end of the day. This system works well for one day jobs. I am trying to create an efficient means to total costs for jobs exceeding one day. Jobs can take anywhere from one day to several months. Any thoughts on how to organize and total data when the number of entries differs by job?

    Reply

    • Hi Vic S,

      Your description seems too broad for me.

      However, If I’m not wrong, your problems are more on not having unique figures
      to make VLOOKUP work, right? Well, the best way to do that is to add a column
      that would create uniqueness when combined to your existing values (in your table)
      to be looked up. I am just guessing of course. So please send a mock file and we’ll study
      and provide better solutions here: HELP DESK.

      Cheers,

      CarloE

      Reply

  11. I am trying to check the common data in the two fields with same name,but vlookup does not picking the same ones.Please assist me why the vlookup function does not retrieve when comparing two fields.

    Reply

    • Hi Santhosh,

      If it cannot find a name that you are certain is in the list then it is likely to be a subtle formatting difference between the two cells. Often one field is formatted as text and the other is not. Press F2 to edit the cells and check if there is an apostrophe at the front of one cell and not the other. Alternatively it might be a case of trailing spaces.

      If that’s not it then please send me the file and I’ll take a look.

      Kind regards,

      Mynda.

      Reply

  12. Essam AlKhaldi

    Dear Eductor Treacy:

    Having just sent an E-mail, I feel I must thank you so much for Your Humanitarian Program,

    Times and Again,
    Thank You Educators, Treacy,

    Reply

  13. I want to learn Vlookup. can you provide me the details how can i learn any cd or demo is there.

    pls do the needful

    thanks.

    Reply

    • Hi Olafur,

      Put each value in it’s own cell and then SUM them. Format the cells with custom number format [h]:mm to ensure the total displays correctly.

      Kind regards,

      Mynda.

      Reply

  15. Dear Sir/Madam,

    I am very much interested to know to learn vlookup formulas so please provide the same.

    Reply

  16. I keep getting N/A in my V look what could be the reasons. I checked and my cells are all the same no apostrpphy or text

    Reply

      • I’d like to offer some advice. We use VLOOKUP every day. Data from one source will be formatted differently than from another. Often the problem is the data that appears to be numeric is actually text. We have tried formatting both sets of data exactly the same, still get the N/A. To resolve, we multiply the data by 1 with the result in a blank column, then copy paste as values back into the original cells. Seems to force Excel to recognize the data as numeric. We have also found trailing spaces in some of our text data (poorly designed database). We use the “trim” formula to remove the trailing spaces.

        Reply

  17. I need to get a percenile from a non numerical list. Example: Out of 50 entries showing facility in column F, what percentage is SJMC. (all other entries are various facilities.)

    To add to my dilema, I have to then break it down by month and yearly quarters.
    HELP!!!!!

    Reply

    • Hi Pam,

      Use Tables for better result. You can simply highlight your data and Go to Insert, Click Tables.
      Now let’s Just assume that your dates are in column E and facilities in column F. Note: I have excluded the headers
      from the count.

      You may use a formula like these:
      All items 

      =COUNTIFS(F2:F50,"=SJMC")/COUNTA(F2:F50)
or
=COUNTIFS(Table1[Facility],"=SJMC")/COUNTA(Table1[Facility])

      Monthly

      =COUNTIFS(Table1[Facility],"SJMC",Table1[Date],">=1/1/2013",Table1[Date],"< =1/31/2013")/COUNTIFS(Table1[Date],">=1/1/2013",Table1[Date],"< =1/31/2013")

      Yearly

      =COUNTIFS(Table1[Facility],"SJMC",Table1[Date],">=1/1/2013",Table1[Date],"< =12/31/2013")/COUNTIFS(Table1[Date],">=1/1/2013",Table1[Date],"< =12/31/2013")

      The difference between the yearly and monthly formula is merely the parameter dates.

      More on Tables, Countifs

      Cheers,

      CarloE

      Reply

  18. Can you please show me how to calculate percentage where lets say i have several material stored on shelves but would like to caculate in percentage how mush spaced is used and how many unused.

    Reply

  21. Dear Madam/Sir,
    I want to learn Vlookup. can you provide me the details how can i learn any cd or demo is there.

    pls do the needful

    thanks.

    Reply

  22. I have to columns with different Column Names but the data in 2 columns are same .

    Ex Col1 has 182 row information and Col2 has 208 row information.

    Comparision needed

    Col1=Col2
    Col1!=Col2
    Col2!=Col1

    Reply
YouTube Channel Follow us on Facebook Twitter Follow Google+ RSS Feed or Email

Blog Categories

Resources

Excel Resources

Affiliate Program

Become an Affiliate
Secured by Sucuri Rect
✕
Hang On, Don't Go Just Yet.
Excel Tips & Tricks Book

As a thank you for visiting how would you like a
10% Discount Code to use with any of my courses?

I'll also give you a copy of my eBook packed with
100 Excel Tips & Tricks.

And I'll send you my weekly Excel blog posts.

Enter your email address below to receive all of these.

Regards, Mynda (Excel MVP)

mynda treacy mvp
100% Privacy. We don't spam.
✕
SUBSCRIBE AND LEARN EXCEL
Excel Tips & Tricks Book


Learn great Excel features, tips and tricks each week from my newsletter.

Just enter your email below, and as an extra bonus, I'll send you a copy of my '100 Excel Tips & Tricks eBook' - for nothing!

Thanks,

Mynda

 



100% Privacy. We don't spam.
✕
100 FREE EXCEL TIPS & TRICKS
Excel Tips & Tricks Book


Want to learn more great Excel tips and features?

Enter your email below and I'll send you my weekly blog post.

Plus, by signing up you'll get a copy of my '100 Excel Tips & Tricks eBook' - for nothing!




 

100% Privacy. We don't spam.