Subtracting data sets using Vlookup

[andyb16]

Hi All,

I have 2 sets of data as below.

The objective is to find the difference in value of hrs against a given emplyee id. I have tried using vlookup to subtract but no luck.


Not sure what I am missing. Please help! thx.

EMPLID</SPAN>	April hrs set 1</SPAN>		EMPLID</SPAN>	April hrs set 2</SPAN>
55518</SPAN>	186</SPAN>		55518</SPAN>	176</SPAN>
72542</SPAN>	202</SPAN>		72542</SPAN>	168</SPAN>
7551</SPAN>	24</SPAN>		7551</SPAN>	168</SPAN>
44496</SPAN>	187</SPAN>		44496</SPAN>	168</SPAN>
50265</SPAN>	193</SPAN>		50265</SPAN>	176</SPAN>
82825</SPAN>			82825</SPAN>	168</SPAN>
4665</SPAN>			4665</SPAN>	178.5</SPAN>
55155</SPAN>	190</SPAN>		55155</SPAN>	165</SPAN>
12585</SPAN>	71</SPAN>		12585</SPAN>	176</SPAN>
27556</SPAN>			27556</SPAN>	168</SPAN>
10040</SPAN>	144</SPAN>		10040</SPAN>	136</SPAN>
45588</SPAN>	167</SPAN>		9500</SPAN>	186</SPAN>
54906</SPAN>	161</SPAN>		45588</SPAN>	176</SPAN>
7514</SPAN>	206</SPAN>		54906</SPAN>	174</SPAN>
58949</SPAN>	209</SPAN>		7514</SPAN>	172</SPAN>
66775</SPAN>	110</SPAN>		49517</SPAN>	168</SPAN>
82552</SPAN>	172</SPAN>		58949</SPAN>	171</SPAN>
70556</SPAN>	194</SPAN>		66775</SPAN>	120</SPAN>
54272</SPAN>	177</SPAN>		82552</SPAN>	160</SPAN>
9049</SPAN>	178</SPAN>		70556</SPAN>	168</SPAN>
			54272</SPAN>	160</SPAN>
			9049</SPAN>	160</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL span=2><COL></COLGROUP>

 

[Marcelo Branco]

Maybe something like this...(Excel 2007 or higher)

=IFERROR(VLOOKUP(G2,D:E,2,0),0)-IFERROR(VLOOKUP(G2,A:B,2,0),0)

where G2 houses an EMPLID (for example, 9500)

M.

 

[drsarao]

Andy,
How about something like this?

Excel Workbook
	A	B	C	D	E	F
1	EMPLID	April hrs set 1		EMPLID	April hrs set 2	Difference
2	55518	186		55518	176	10
3	72542	202		72542	168	34
4	7551	24		7551	168	-144
5	44496	187		44496	168	19
6	50265	193		50265	176	17
7	82825			82825	168	-168
8	4665			4665	178.5	-178.5
9	55155	190		55155	165	25
10	12585	71		12585	176	-105
11	27556			27556	168	-168
12	10040	144		10040	136	8
13	45588	167		9500	186	Not Available in 1st list
14	54906	161		45588	176	-9
15	7514	206		54906	174	-13
16	58949	209		7514	172	34
17	66775	110		49517	168	Not Available in 1st list
18	82552	172		58949	171	38
19	70556	194		66775	120	-10
20	54272	177		82552	160	12
21	9049	178		70556	168	26
22				54272	160	17
23				9049	160	18
Sheet2

Excel 2003

Cell Formulas
Range	Formula
F2	=IF(ISNA(MATCH(D2,A:A,0)),"Not Available in 1st list", VLOOKUP(D2,A:B,2,0)-E2)

Modify the formula as per your needs.

 

[andyb16]

Thanks Guys!!