My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

You are here: Home / VLOOKUP

Reader Interactions

Comments

  2. Celestino Diaz

    GOOD DAY MADAM,
    WE CREATE VLOOKUP FROM EXCEL FILE TO ANOTHER EXCEL FILE
    HOW CAN WE GENERATE USING XLOOKUP?
    THIS OUR EXAMPLE FORMULA OF VLOOKUP THAT WE CAN PULL OUT DATA FROM EXCEL TO ANOTHER EXCEL FILE
    =IF(ADMIN_WDataItemCode=””,””,VLOOKUP(ADMIN_WDataItemCode,’C:\Users\USER\Dropbox\ITEM LIST for MRF & TRF\ITEM LIST.xlsx’!ITEMS,2,FALSE))

    ADMIN_WDataItemCode = lookup_value
    C:\Users\USER\Dropbox\ITEM LIST for MRF & TRF\ITEM LIST.xlsx’ = LOCATION OF THE FILE
    ITEMS = table_array
    2 = col_index_num
    FALSE = range_lookup
    HOW CAN I USE THIS TO XLOOKUP
    I WISH YOU CAN HELP ME
    THANK YOU VERY MUCH FOR ALL I LEARN A LOT MADAM…

    CELESTINO SWANSON DIAZ JR.

    Reply

  7. Liz

    Okay I need some help on a VLOOKUP to make something a little easier.
    I have two worksheets within a workbook for which I have a VLookup working.
    It takes the Sheet 1-1 and Sheet 1-2 and Adds them in the Vlookup worksheet named Total Sheet 1. However I need another 50 work sheets 1-1 & Sheet 1-2 and 50 more Vlookup Sheets with the totals. So for example the next set of sheets within the workbook will have 2-1 and 2-2 with a summary total worksheet 2 that Vlookup sums the 2-1 & 2-2. However when copying the worksheets they will copy the Vlookup from 1-1 & 1-2 and then I have to manually change the formula to correctly look at the correct worksheet to do the Vlookup on. Is there a way to not manually have to type in the formula to change sheet 1-1 and 1-2 to 2-1 and 2-2? Thanks!

    Reply

  8. David Bill

    I have a list of the 159 counties in Georgia(US) divided into six recruiting territories. I want to be able to enter a county name of the county and populate a field with the assigned territory number and the recruiters name.

    Reply

  10. jitendra

    required help regarding vlookup file x colum b data required from file y. refrence number of colum a both file same, I required data file from file y colum b to file colum b

    Reply

    • Catalin Bombea

      Hi Jitendra,
      To get data from another file, it’s better to use power query instead of formulas, some functions are not working if the source file is closed.

      Reply

  11. Rafiq

    I want to distribute values in one table having multiple items with single item values in the form of FIFO basis. but value should be not more then reference table in consolidation

    1st Table
    Item party value (reference value)

    A A1 25 25
    A A2 24 24

    B B1 20 20
    B B2 15 10

    2nd Table
    Item Value

    A 50
    B 30

    What is the procedure to distribute 1st table’s reference value from 2nd table value.

    Reply

    • Mynda Treacy

      Hi Rafiq,

      It’s tricky to help without seeing your Excel file structure. Please post your question on our Excel forum where you can upload an Excel file with your sample data and desired result. We can then help you with the solution.

      Mynda

      Reply

  12. Rosy

    Mam,
    I feel very confused. I always have problem in understanding V lookup.But this is very important formulae to learn. Could you please help me out in using this function.

    Thanks!

    Reply

    • Mynda Treacy

      Hi Rosy,

      Please work through the first tutorial linked to on this page. Then if you’re still confused, please post your Excel file with your attempt on our Excel forum where we can see where you’re going wrong and help you further.

      Mynda

      Reply

  13. Tea

    Hello,

    I am looking to use a vlookup formula to see if there has been an increase or decrease when comparing one number to another.

    Please help!

    Thanks!

    Reply

    • Philip Treacy

      Hi Tea,

      Without seeing your data it’s difficult to give a response. From your description why can’t you use something like IF(A>B,0,1) ?

      If you start a topic on our forum and supply your workbook and data it’ll be easier for us to give an answer.

      Regards

      Phil

      Reply

  15. Maschshalieen Van Eyssen

    I would like to figure out an efficient formula or excel formula to calculate FV month to month for multiple sheets at same time; the difficulty is that the additional deposits are at random days during the month and I can only find formulas to calculate regular deposits either at the start or the end of the month. For example if I deposit during June 15th but then the client withdraws on 17th june then the daily calculated interest will adjust for the remainder of the month to the increased principle, and therefore the interest will be higher for the remaining 13/14 days of the month. Is there a formula to avoid having to create a day by day spreadsheet? One where I may be able to enter into the formula the specific day of the month and the deposited amount to adjust interest daily interest calculations for the remainder of the month.I work with multiple investment accounts within our 3rd party and currently i calculate day by day and do adjustments manually.
    I know there is a way to do it all automatically every month end and adjust prime interest rate by click of one button. Its just so time consuming,

    Can you please help.

    Reply

    • Catalin Bombea

      Hi,
      It should be possible. Most of the problems come from keeping data split into multiple sheets. Once you merge the data into a single sheet, there will me much less problems. You can do that with Power Query.
      There may be other solutions, but we need to see a sample file with manual examples of the desired outcome. Upload a sample file on our forum, create a new topic after sign-in.
      Catalin

      Reply

  16. Rick

    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

    • Catalin Bombea

      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

  19. Johnny

    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

    • Catalin Bombea

      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

  21. saursfts

    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

    • Dritan

      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

      • Catalin Bombea

        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

  23. David

    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

    • Mynda Treacy

      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

  24. MARY

    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

    • MARY

      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

      • Mynda Treacy

        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

    • Mynda Treacy

      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

  25. Vic S

    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

    • Carlo Estopia

      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

  26. SANTHOSH

    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

    • Mynda Treacy

      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

  27. Essam AlKhaldi

    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

  28. Asha

    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

    • Mynda Treacy

      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

  30. Dattatreya Kanchagar

    Dear Sir/Madam,

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

    Reply

  31. sabiha

    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

      • Becky

        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

  32. PAM BECKERT

    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

    • Carlo Estopia

      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

  33. HENDRIK

    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

  36. Supriya Arjugade

    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

  37. Pavan

    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

Leave a Reply

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

Current ye@r *