Formula ? on match

Z

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
2,125
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
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]
[TH][/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​
[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ID[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]HOURLY HOURS[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #FFFF00"]
15​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #FFFF00"]
40​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #FFFF00"]
12​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #FFFF00"]
41​
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet2[/TD]
[/TR]
</tbody>[/TABLE]


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

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][th]
G​
[/th][th]
H​
[/th][th]
I​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
1​
[/td][td]ID[/td][td][/td][td][/td][td][/td][td][/td][td]HOURLY HOURS[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
2​
[/td][td=bgcolor:#FFFF00]
15​
[/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#FFFF00]
40​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
3​
[/td][td=bgcolor:#FFFF00]
12​
[/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#FFFF00]
41​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet2[/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][th]
Y​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
1​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
2​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
3​
[/td][td][/td][td][/td][td][/td][td]
ID#​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
4​
[/td][td=bgcolor:#F1F7ED]
1​
[/td][td=bgcolor:#F1F7ED][/td][td=bgcolor:#F1F7ED]
​
[/td][td=bgcolor:#FFFF00]12[/td][td=bgcolor:#F1F7ED]
​
[/td][td=bgcolor:#F1F7ED]
8.0​
[/td][td=bgcolor:#F1F7ED]
2.0​
[/td][td=bgcolor:#F1F7ED]
​
[/td][td=bgcolor:#F1F7ED]
8.0​
[/td][td=bgcolor:#F1F7ED]
2.0​
[/td][td=bgcolor:#F1F7ED]
​
[/td][td=bgcolor:#F1F7ED]
8.0​
[/td][td=bgcolor:#F1F7ED]
​
[/td][td=bgcolor:#F1F7ED]
​
[/td][td=bgcolor:#F1F7ED]
8.0​
[/td][td=bgcolor:#F1F7ED]
​
[/td][td=bgcolor:#F1F7ED]
​
[/td][td=bgcolor:#F1F7ED]
8.0​
[/td][td=bgcolor:#F1F7ED]
​
[/td][td=bgcolor:#F1F7ED]
​
[/td][td=bgcolor:#F1F7ED]
​
[/td][td=bgcolor:#F1F7ED]
8.0​
[/td][td=bgcolor:#F1F7ED]
​
[/td][td=bgcolor:#F1F7ED]
​
[/td][td=bgcolor:#FFFF00]
40.0 ​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
5​
[/td][td=bgcolor:#F1F7ED]
2​
[/td][td=bgcolor:#F1F7ED][/td][td=bgcolor:#F1F7ED]
​
[/td][td=bgcolor:#FFFF00]15[/td][td=bgcolor:#F1F7ED]
​
[/td][td=bgcolor:#F1F7ED]
8.0 ​
[/td][td=bgcolor:#F1F7ED]
3.5 ​
[/td][td=bgcolor:#F1F7ED]
​
[/td][td=bgcolor:#F1F7ED]
8.0​
[/td][td=bgcolor:#F1F7ED]
8.0 ​
[/td][td=bgcolor:#F1F7ED]
​
[/td][td=bgcolor:#F1F7ED]
8.0 ​
[/td][td=bgcolor:#F1F7ED]
​
[/td][td=bgcolor:#F1F7ED]
​
[/td][td=bgcolor:#F1F7ED]
8.0 ​
[/td][td=bgcolor:#F1F7ED]
3.0 ​
[/td][td=bgcolor:#F1F7ED]
​
[/td][td=bgcolor:#F1F7ED]
8.0 ​
[/td][td=bgcolor:#F1F7ED]
3.0 ​
[/td][td=bgcolor:#F1F7ED]
​
[/td][td=bgcolor:#F1F7ED]
​
[/td][td=bgcolor:#F1F7ED]
​
[/td][td=bgcolor:#F1F7ED]
​
[/td][td=bgcolor:#F1F7ED]
​
[/td][td=bgcolor:#FFFF00]
40.0 ​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]
<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
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]
C​
[/th][th]
D​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
2​
[/td][td]
id​
[/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
3​
[/td][td]
1​
[/td][td]
#value !​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
4​
[/td][td]
2​
[/td][td]
#value !​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
5​
[/td][td]
3​
[/td][td]
#value !​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
6​
[/td][td]
4​
[/td][td]
#value !​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
7​
[/td][td]
5​
[/td][td]
0​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet3[/td][/tr][/table]
 
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
1K
Fluff
Fluff
Z
  • Question Question
Formula to match multiple things so I can get a result to tell me what I am off on.
Replies
1
Views
376
zone709
Z
Z
  • Solved
Need help on a formula Maybe SumIfs
Replies
3
Views
477
zone709
Z
Z
  • Question Question
Sumifs Formula Help 2 Criteria i think
Replies
1
Views
381
zone709
Z
R
  • Solved
Approach to sorting and index/match
2
Replies
16
Views
405
Alex Blakenburg
A

Forum statistics

Threads
1,223,896
Messages
6,175,265
Members
452,627
Latest member
KitkatToby

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