Find Difference Of Prices For Each Day

[30percent]

Hi,

I have a 4 columns: Date(itemA), Price(itemA), Date(itemB), Price(itemB).
I need to find the difference of Price(itemA) and Price(itemB) for each day.
However, there are more data/date on itemB than itemA.

So I can't use a simple formula of (=B2-D2). Since date for B2 may not be the same date for D2.

I wonder how could I structure my formula, perhaps using "if" and "vlookup"
to find the difference of Price of the 2 items for each day.

Thank you!

 

[sheetspread]

Excel Workbook
	A	B	C	D	E
1	Date (Item A)	Price (Item A)	Date (Item B)	Price (Item B)	Difference
2	1/1/2011	37	1/1/2011	2	35
3	1/3/2011	70	1/2/2011	49	-49
4	1/4/2011	37	1/3/2011	33	37
5	1/5/2011	78	1/4/2011	26	11
6	1/6/2011	58	1/5/2011	5	73
7	1/8/2011	93	1/6/2011	99	-41
8	1/9/2011	70	1/7/2011	78	-78
9	1/10/2011	10	1/8/2011	75	18
10	1/11/2011	48	1/9/2011	58	12
11	1/12/2011	26	1/10/2011	23	-13
12	1/19/2011	75	1/11/2011	29	19
13	1/20/2011	17	1/12/2011	97	-71
14	1/21/2011	89	1/13/2011	21	-21
15	1/22/2011	88	1/14/2011	35	-35
16	1/23/2011	95	1/15/2011	89	-89
17	1/28/2011	58	1/16/2011	90	-90
18	*	*	1/17/2011	64	-64
19	*	*	1/18/2011	32	-32
20	*	*	1/19/2011	70	5
21	*	*	1/20/2011	86	-69
22	*	*	1/21/2011	22	67
23	*	*	1/22/2011	74	14
24	*	*	1/23/2011	21	74
25	*	*	1/24/2011	54	-54
26	*	*	1/25/2011	9	-9
27	*	*	1/26/2011	58	-58
28	*	*	1/27/2011	8	-8
29	*	*	1/28/2011	87	-29
Sheet1

 

[30percent]

Hi,

Thank you for your help!!

I wonder if I could return null or just return nothing, if there is no matching date?

 

[30percent]

Sorry, think I know how to do this....
Let me try the formula....
Thank you!

 

[sheetspread]

IF(ISERROR(VLOOKUP(C2,$A$2:$B$17,2,FALSE))=TRUE,0,VLOOKUP(C2,$A$2:$B$17,2,FALSE)-D2)

will return zero,

IF(ISERROR(VLOOKUP(C2,$A$2:$B$17,2,FALSE))=TRUE,"Null",VLOOKUP(C2,$A$2:$B$17,2,FALSE)-D2)

will return the word "null"

You could also change the "Null" to just "" and it will be blank, but be careful, excel will treat it as a zero length string and not a true blank nor zero (this has caused problems for me in the past)

 

[T. Valko]

IF(ISERROR(VLOOKUP(C2,$A$2:$B$17,2,FALSE))=TRUE,0,VLOOKUP(C2,$A$2:$B$17,2,FALSE)-D2)

will return zero,

IF(ISERROR(VLOOKUP(C2,$A$2:$B$17,2,FALSE))=TRUE,"Null",VLOOKUP(C2,$A$2:$B$17,2,FALSE)-D2)

will return the word "null"

You could also change the "Null" to just "" and it will be blank, but be careful, excel will treat it as a zero length string and not a true blank nor zero (this has caused problems for me in the past)

You don't need to test that ISERROR() = TRUE. It returns either TRUE or FALSE so a test for one of those conditions would be redundant.

Also, you can save a couple of keystrokes by using 0 instead of FALSE for the range_lookup argument.

IF(ISERROR(VLOOKUP(C2,$A$2:$B$17,2,0)),"Null",VLOOKUP(C2,$A$2:$B$17,2,0)-D2)

If they happen to be using Excel 2007 or later:

=IFERROR(VLOOKUP(C2,$A$2:$B$17,2,0)-D2,"Null")