+ Reply to Thread
Results 1 to 5 of 5

Formula to pull data from one sheet to another where multiple values match

  1. #1
    Registered User
    Join Date
    06-24-2020
    Location
    Wednesbury, England
    MS-Off Ver
    MS Office Professional Plus 2013
    Posts
    2

    Formula to pull data from one sheet to another where multiple values match

    Good afternoon everyone

    I'm looking for some assistance on looking up a percentage value from a sheet only where the dates match for the same identifier.

    e.g.

    number (in both sheets) 10000

    date (has to match from both sheets) 01/06/2020

    if it matches (show the percentage from sheet 2 in sheet 1) 5%

    I've tried combining an if and lookup, but keep getting stuck. Any help would be appreciated.
    Attached Files Attached Files

  2. #2
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,699

    Re: Formula to pull data from one sheet to another where multiple values match

    Since you are using Excel 2013, Power Query is available for you to download from the Excel Site. Using PQ, I loaded each table to it and then Close and Loaded to a Connection. Then, I merged the two tables with a Left Inner Join on the Identifier and Date. The results were:

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    1
    Idenitifier Forename Surname Date Basic Rate Holiday Pay Basic earnings total paid pension at %
    2
    603624
    Pete Smith
    6/1/2020
    700
    800
    1500
    5.8
    3
    603625
    Pete Smith
    4/1/2020
    600
    200
    800
    5.6
    4
    603626
    Pete Smith
    2/1/2020
    450
    200
    650
    5.6
    5
    603627
    Pete Smith
    12/1/2019
    800
    200
    1000
    5.3
    Sheet: Sheet2

    Review PQ
    In the attached file
    Click on any cell in the new table
    On the Data Tab, click on Queries & Connections
    In the right window, double click to open Query
    Review PQ steps

    M-code basics:
    - "let" is the start of a query
    - "in" is the closing of a query
    - each transformation step sits in between those 2 lines
    - each step line is ended by a comma, except the last one
    - "Source" is always the first step (Source Data)
    - After "in" you have the last step referenced

    File attached

    The Mcode that resulted from my steps is as follows.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by alansidman; 06-24-2020 at 12:33 PM.
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    2,707

    Re: Formula to pull data from one sheet to another where multiple values match

    Another way:

    In H2 and down -
    =AGGREGATE(15,6,($G$10:$G$16)*1/(D2=$B$10:$B$16),1)

  4. #4
    Registered User
    Join Date
    06-24-2020
    Location
    Wednesbury, England
    MS-Off Ver
    MS Office Professional Plus 2013
    Posts
    2

    Re: Formula to pull data from one sheet to another where multiple values match

    Thank you for your input. I couldn't download anything as it is my work computer and I couldn't get the formula to work, so have come up with another way around it now.

  5. #5
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,699

    Re: Formula to pull data from one sheet to another where multiple values match

    Do you have MS Access installed on your computer? Have you considered asking your IT department to add Power Query to your machine. It is part of Excel and not some "foreign" software.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Complex Index/match formula to pull data based on 1 criteria. (Bid/Cost Sheet)
    By Blackmagic42 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-18-2019, 01:45 AM
  2. [SOLVED] Pull whole rows of data to another sheet that match any of multiple criteria
    By christechgeek in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-12-2015, 03:51 AM
  3. Replies: 1
    Last Post: 04-20-2015, 03:18 PM
  4. [SOLVED] VBA formula with multiple variables to pull data from spread sheet.
    By topnotchthrillr in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-14-2015, 12:30 AM
  5. Replies: 4
    Last Post: 08-29-2012, 10:32 AM
  6. Replies: 5
    Last Post: 07-26-2012, 09:51 AM
  7. Replies: 0
    Last Post: 01-23-2012, 02:49 PM

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