Formula to match multiple things so I can get a result to tell me what I am off on.

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
2,125
Office Version
  1. 365
Platform
  1. Windows
Hi,

Example Sheet 1 am trying to Match column D id number with the total hours in Column H and matching the Job name in column K to Sheet2 for results.
Need to match sheet 1 to sheet 2. Sheet1 - Id in Column D then match the totals numbers and job names in E H, I L, M P, Q T, U X for a result to come back on Sheet1 in Column L.

On sheet1 in Column L I put 8 next to 20 Pacific Street as a result. This is because Sheet 1 had 16 hours in Column H with matching job Verse Sheet2 where it only has 8 hours and matching job in columns U and X. Not 16 hours. This is what I am trying to do so I know where I’m off on down my sheet.

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]
A
[/th][th]
B
[/th][th]
C
[/th][th]
D
[/th][th]
E
[/th][th]
F
[/th][th]
G
[/th][th]
H
[/th][th]
I
[/th][th]
J
[/th][th]
K
[/th][th]
L
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
1
[/td][td]
A
[/td][td]
B
[/td][td]
C
[/td][td]
D
[/td][td]
E
[/td][td]
F
[/td][td]
G
[/td][td]
H
[/td][td]
I
[/td][td]
J
[/td][td]
K
[/td][td]
L
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
2
[/td][td]ID[/td][td]LAST NAME[/td][td]FIRST NAME[/td][td]ID[/td][td]PERIOD END DATE[/td][td]GROSS PAY[/td][td]HOURLY AMOUNT[/td][td]HOURLY HOURS[/td][td]HOURLY RATE[/td][td]OVERTIME HOURS[/td][td]ORG UNIT[/td][td]Results Return - Pull Formula down[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
3
[/td][td]
5​
[/td][td]gat[/td][td]Jim[/td][td]
5​
[/td][td]
1/8/2023​
[/td][td]
200.00​
[/td][td]
200.00​
[/td][td]
16​
[/td][td]
125​
[/td][td][/td][td]15 Broome St[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
4
[/td][td]
5​
[/td][td]gat[/td][td]Jim[/td][td]
5​
[/td][td]
1/8/2023​
[/td][td]
200.00​
[/td][td]
200.00​
[/td][td]
16​
[/td][td]
125​
[/td][td][/td][td]20 Pacific Street[/td][td=bgcolor:#FFFF00]
8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
5
[/td][td]
5​
[/td][td]gat[/td][td]Jim[/td][td]
5​
[/td][td]
1/8/2023​
[/td][td]
100.00​
[/td][td]
100.00​
[/td][td]
8​
[/td][td]
125​
[/td][td][/td][td]162 Levit Ave[/td][td][/td][/tr]
[/table]
[Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]
A
[/th][th]
B
[/th][th]
C
[/th][th]
D
[/th][th]
E
[/th][th]
F
[/th][th]
G
[/th][th]
H
[/th][th]
I
[/th][th]
J
[/th][th]
K
[/th][th]
L
[/th][th]
M
[/th][th]
N
[/th][th]
O
[/th][th]
P
[/th][th]
Q
[/th][th]
R
[/th][th]
S
[/th][th]
T
[/th][th]
U
[/th][th]
V
[/th][th]
W
[/th][th]
X
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
1
[/td][td]
A
[/td][td]
B
[/td][td]
C
[/td][td]
D
[/td][td]
E
[/td][td]
F
[/td][td]
G
[/td][td]
H
[/td][td]
I
[/td][td]
J
[/td][td]
K
[/td][td]
L
[/td][td]
M
[/td][td]
N
[/td][td]
O
[/td][td]
P
[/td][td]
Q
[/td][td]
R
[/td][td]
S
[/td][td]
T
[/td][td]
U
[/td][td]
V
[/td][td]
W
[/td][td]
X
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
2
[/td][td=bgcolor:#DBDBDB]
SNO
[/td][td=bgcolor:#DBDBDB]
Employee Name
[/td][td=bgcolor:#DBDBDB]
Trade
[/td][td=bgcolor:#DBDBDB]
ID
[/td][td=bgcolor:#DBDBDB]
MONDAY
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
TUESDAY
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
WEDNESDAY
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
THURSDAY
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
FRIDAY
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
3
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
4
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
[/td][td=bgcolor:#DBDBDB]
ST
[/td][td=bgcolor:#DBDBDB]
OT
[/td][td=bgcolor:#DBDBDB]
DT
[/td][td=bgcolor:#DBDBDB]
JOB
[/td][td=bgcolor:#DBDBDB]
ST
[/td][td=bgcolor:#DBDBDB]
OT
[/td][td=bgcolor:#DBDBDB]
DT
[/td][td=bgcolor:#DBDBDB]
JOB
[/td][td=bgcolor:#DBDBDB]
ST
[/td][td=bgcolor:#DBDBDB]
OT
[/td][td=bgcolor:#DBDBDB]
DT
[/td][td=bgcolor:#DBDBDB]
JOB
[/td][td=bgcolor:#DBDBDB]
ST
[/td][td=bgcolor:#DBDBDB]
OT
[/td][td=bgcolor:#DBDBDB]
DT
[/td][td=bgcolor:#DBDBDB]
JOB
[/td][td=bgcolor:#DBDBDB]
ST
[/td][td=bgcolor:#DBDBDB]
OT
[/td][td=bgcolor:#DBDBDB]
DT
[/td][td=bgcolor:#DBDBDB]
JOB
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
5
[/td][td]
3
[/td][td=bgcolor:#FFFFFF]Jim Gat[/td][td=bgcolor:#FFFFFF]Super[/td][td=bgcolor:#FFFFFF]
5
[/td][td=bgcolor:#FFFFFF]
8.00
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
162 Levit Ave
[/td][td=bgcolor:#FFFFFF]
8.00
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
[/td][td]
15 Broome St
[/td][td=bgcolor:#FFFFFF]
8.00
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
[/td][td]
15 Broome St
[/td][td=bgcolor:#FFFFFF]
8.00
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
160 75th Street
[/td][td=bgcolor:#FFFFFF]
8.00
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
20 Pacific Street
[/td][/tr]
[/table]
[Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sheet2 i Meant - Id in Column D then match the totals numbers and job names in E H, I L, M P, Q T, U X for a result to come back on Sheet1 in Column L.
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top