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

1. ## 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.

2. ## 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.``

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

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

#### 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