if, lookups, checking if a cell value exists within another col, if so return value NEXT to that cell

[ludo2016]

I have two columns of dates and one column with prices.
The first column is a day by day sequence across the year (365 cells). The second column are the days when a transaction occurred (eg. 100 cells) and the third column is the transaction price on those days (100 cells).

I want to plot the transaction prices (yaxis) against the first column (x axis). For this I think I can formulate a function to allocate zeroes on the days when there was no transaction and prices on the days there were prices.

I've tried a combo of approaches, none of which have worked. Maybe there's an easier way? Or I'm tripping up in executing one of these approaches?: =vlookup(), if(vlookups), =IF(ISERROR(MATCH), and =IF(ISERROR(MATCH(A2,$C$2:$C$111,0)),"no match",VLOOKUP(A2,$C$2:$D$111,2,FALSE))

Sample data:

[TABLE="width: 353"]
<tbody>[TR]
[TD]Cal Yr[/TD]
[TD][/TD]
[TD]Trans date[/TD]
[TD][/TD]
[TD]Trans price[/TD]
[/TR]
[TR]
[TD="align: right"]01/05/2015[/TD]
[TD][/TD]
[TD]10/05/2015[/TD]
[TD][/TD]
[TD]7.46[/TD]
[/TR]
[TR]
[TD="align: right"]02/05/2015[/TD]
[TD][/TD]
[TD]11/05/2015[/TD]
[TD][/TD]
[TD]7.31[/TD]
[/TR]
[TR]
[TD="align: right"]03/05/2015[/TD]
[TD][/TD]
[TD]19/05/2015[/TD]
[TD][/TD]
[TD]7.62[/TD]
[/TR]
[TR]
[TD="align: right"]04/05/2015[/TD]
[TD][/TD]
[TD]19/05/2015[/TD]
[TD][/TD]
[TD]7.08[/TD]
[/TR]
[TR]
[TD="align: right"]05/05/2015[/TD]
[TD][/TD]
[TD]20/05/2015[/TD]
[TD][/TD]
[TD]7.93[/TD]
[/TR]
[TR]
[TD="align: right"]06/05/2015[/TD]
[TD][/TD]
[TD]23/05/2015[/TD]
[TD][/TD]
[TD]7.115[/TD]
[/TR]
</tbody>[/TABLE]

 

[jorismoerings]

Hi,

Check if this is what you're after:

Excel 2016 (Windows) 64 bit
	A	B	C	D
1	Cal Yr	Trans date	Trans price
2	1-5-2015	10-5-2015	7,46	0
3	2-5-2015	11-5-2015	7,31	0
4	3-5-2015	19-5-2015	7,62	0
5	4-5-2015	19-5-2015	7,08	0
6	5-5-2015	20-5-2015	7,93	0
7	6-5-2015	23-5-2015	7,115	0
8	7-5-2015			0
9	8-5-2015			0
10	9-5-2015			0
11	10-5-2015			7,46
12	11-5-2015			7,31
13	12-5-2015			0
14	13-5-2015			0
15	14-5-2015			0
16	15-5-2015			0
17	16-5-2015			0
18	17-5-2015			0
19	18-5-2015			0
20				0
Sheet1

Cell Formulas
Range	Formula
D11	=IFERROR(INDEX($C$2:$C$7,MATCH(A11,$B$2:$B$7,0)),0)

HtH

 

[ludo2016]

Thanks Joris. Looks like a neat solution on here but it's not working in my spreadsheet. I get zeroes all the way down

[TABLE="width: 278"]
<colgroup><col span="2"><col span="2"></colgroup><tbody>[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[ludo2016]

I don't think I get zeroes becasue of this but I've noticed another potential problem. Sometimes I have two prices on the same date.