+ Reply to Thread
Results 1 to 3 of 3

Vlookup from multiple sheets to get an average

  1. #1
    Registered User
    Join Date
    01-25-2019
    Location
    North Carolina
    MS-Off Ver
    2016
    Posts
    1

    Vlookup from multiple sheets to get an average

    I have been tasked with maintaining a performance file, inside of it are sheets for each week of the month (12.30-1.5) (1.6-1.12) etc and this information will be manually entered because I haven't figured out how to Vlookup when data is not in the same position on multiple sheets and if I am honest, I am not the best user of Vlookup. Anyway, we have sheets for each week and then a sheet called MTD. My question is how do I use a Vlookup on the MTD sheet to look up and average the data in the weekly sheets? There are multiple topics within each sheet, quality average, absenteeism, adherence etc. So the vlookup would need to be specific to each category, pulling from each weekly sheet, to average on the monthly sheet to get the MTD stats.

    I have no idea if I have formatted this correctly and I really do appreciate everyone's help. I am not the best user of Excel but before I ask I make sure to try to find the answer myself but for this one I am lost. If you need additional information, please let me know.

  2. #2
    Registered User
    Join Date
    06-29-2011
    Location
    Fort Lauderdale
    MS-Off Ver
    Excel 2018
    Posts
    21

    Re: Vlookup from multiple sheets to get an average

    I am giving you good advice here, make your life so much easier and just take the time to re-organize each sheet so that the data rows and columns are consistent throughout and then keep it this way going forward. If you receive information from others, then provide them with a template so they keep to this format as well.

    Then use Pivot Tables (YouTube has tons of help guides) to consolidate and create the reporting, including averages, that you want.

    I highly recommend what I describe above, that is much easier and safer. Having said this, if you only have a few cells to average, then vlookup and/or offset functions could be used. I am new here and so I cannot post links to help you; however, just search for "vlookup vs offset" and "vlookup vs offset match index" and you will find what you are seeking. Do this in YouTube as well.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    48,605

    Re: Vlookup from multiple sheets to get an average

    Welcome to the forum

    I would offer different advice. It is standard practice to try and keep all data on 1 sheet, and then use other sheets for summaries/analysis.
    I agree that standard layout of data is vital to simplify calculations.

    PT may be the way to go, but if you standardize the layout of your data, vlookup or INDEX/MATCH may be a better option.

    Without seeing some sample data (and expected outcome), it is almost impossible to give any more specific advice.

    upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1