Formula ? on match

Z

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
2,079
Office Version
  1. 365
Platform
  1. Windows
Hi I am trying to tell if there is a different sum amount from 2 different sheets

Sheet2 and Sheet 1

I am trying to use a formula to match the ID numbers in sheet2 and Sheet1 in both column D and then look at columns (I) on sheet 2 and (Y) on sheet1 and return the difference if any or if the same then 0 or blank.

as I posted below you can see ID number 12 is off by 1 so it can return 1 or -1 doesn't matter. Just doing a check

Sheet2 in I is 41, Sheet1 is 40 in Y

Excel 2016 (Windows) 32 bit
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
IDHOURLY HOURS
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
15​
40​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
12​
41​

<tbody>
</tbody>
Sheet: Sheet2

<tbody>
</tbody>


Excel 2016 (Windows) 32 bit
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]J[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]K[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]L[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]M[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]N[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]O[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]P[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Q[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]R[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]S[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]T[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]U[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]V[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]W[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]X[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Y[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
ID#​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
1​
12
8.0​
2.0​
8.0​
2.0​
8.0​
8.0​
8.0​
8.0​
40.0 ​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
2​
15
8.0 ​
3.5 ​
8.0​
8.0 ​
8.0 ​
8.0 ​
3.0 ​
8.0 ​
3.0 ​
40.0 ​

<tbody>
</tbody>
Sheet: Sheet1

<tbody>
</tbody>
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
Excel 2016 (Windows) 32 bit
D​
E​
F​
G​
H​
I​
1​
IDHOURLY HOURS
2​
15​
40​
3​
12​
41​
Sheet: Sheet2



Excel 2016 (Windows) 32 bit
A​
B​
C​
D​
E​
F​
G​
H​
I​
J​
K​
L​
M​
N​
O​
P​
Q​
R​
S​
T​
U​
V​
W​
X​
Y​
1​
2​
3​
ID#​
4​
1​
​
12
​
8.0​
2.0​
​
8.0​
2.0​
​
8.0​
​
​
8.0​
​
​
8.0​
​
​
​
8.0​
​
​
40.0 ​
5​
2​
​
15
​
8.0 ​
3.5 ​
​
8.0​
8.0 ​
​
8.0 ​
​
​
8.0 ​
3.0 ​
​
8.0 ​
3.0 ​
​
​
​
​
​
40.0 ​
Sheet: Sheet1
<strike></strike>

<strike></strike>
 
Upvote 0
I think I may of forgot to say I am getting the results on sheet3 with the ID also in column C on sheet3. I came up with this and its probably to long, but it works. The only thing now I need to stop the #value error. If nothing returns then blank.

=IFERROR(VLOOKUP($C$3:$C$100, Sheet1!$D$2:$Z$100,22,0),"")-IFERROR(VLOOKUP($C$3:$C$100, Sheet2!$D$2:$Z$100,6,0),"")



Excel 2016 (Windows) 32 bit
C​
D​
2​
id​
3​
1​
#value !​
4​
2​
#value !​
5​
3​
#value !​
6​
4​
#value !​
7​
5​
0​
Sheet: Sheet3
 
Upvote 0
You will get value if either of your IFERROR formulas return "". You could of course wrap the whole formula in another IFERROR or maybe use 0 instead of "".
 
Upvote 0
Maybe this:

=IFERROR(ABS(VLOOKUP(C3,Sheet1!$D$2:$Z$100,22,0)-VLOOKUP(C3,Sheet2!$D$2:$Z$100,6,0)),"")
 
Last edited:
Upvote 0
this works great thanks so much for the help. Sorry for the late resposnse
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Solved
Formula help. Match name return numbers that not in Column H
2 3
Replies
28
Views
944
Fluff
Fluff
Z
  • Question
Formula to match multiple things so I can get a result to tell me what I am off on.
Replies
1
Views
293
zone709
Z
Z
  • Solved
Need help on a formula Maybe SumIfs
Replies
2
Views
352
zone709
Z
Z
  • Question
Sumifs Formula Help 2 Criteria i think
Replies
1
Views
297
zone709
Z
Z
  • Solved
SUMSIF formula help in a different way
Replies
6
Views
295
Fluff
Fluff

Forum statistics

Threads
1,215,465
Messages
6,124,977
Members
449,200
Latest member
Jamil ahmed

Share this page

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