Formula based on date reference

[dunmore83]

Hi,

I have a more complex series of numbers but this is small sample;

Excel 2012
	A	B	C
2	05-Jul-15	4,320	2,138
3	06-Jul-15	4,600	2,392
4	07-Jul-15	4,200	2,016
5	08-Jul-15	5,000	2,525
6	09-Jul-15	4,650	2,465
7
8	02-Jul-15	54.00%
9	03-Jul-15	55.50%
10	04-Jul-15	51.00%
11	05-Jul-15	49.50%
12	06-Jul-15	52.00%
13	07-Jul-15	48.00%
14	08-Jul-15	50.50%
15	09-Jul-15	53.00%
Sheet1

Cell Formulas
Range	Formula
A3	=A2+1
A4	=A3+1
A5	=A4+1
A6	=A5+1
A9	=A8+1
A10	=A9+1
A11	=A10+1
A12	=A11+1
A13	=A12+1
A14	=A13+1
A15	=A14+1
C2	=B2*B11
C3	=B3*B12
C4	=B4*B13
C5	=B5*B14
C6	=B6*B15

 

[dunmore83]

I need a formula in C2:C6 that will find the value in cells B8:B15 based on the corresponding date i.e. in cell C2 rather than B2*B11 it needs to be more like B2*... match the date in A2 with A11 then multiply B2 by B11

The reason being the dates in A2:A6 and A8:A15 will not always be sequential.

Thanks

 

[AhoyNC]

Try:
Copy C1 down as needed.

Excel Workbook
	A	B	C
1	5-Jul-15	4,320	2,138
2	6-Jul-15	4,600	2,392
3	7-Jul-15	4,200	2,016
4	8-Jul-15	5,000	2,525
5	9-Jul-15	4,650	2,465
6
7	2-Jul-15	54.00%
8	3-Jul-15	55.50%
9	4-Jul-15	51.00%
10	5-Jul-15	49.50%
11	6-Jul-15	52.00%
12	7-Jul-15	48.00%
13	8-Jul-15	50.50%
14	9-Jul-15	53.00%
Sheet

 

[dunmore83]

Thanks.

This is my formula from the full worksheet;

=IF(G13>TODAY(),"",IF($P13=0,0,((($S13*AH224)+($AE13*P431)+($AN13*AH431)+($AW13*AW431)+($BF13*BL431))*(1-BX13))/$CD13))

For the above example substitute the $S13 for B1 & the AH224 for B7.

Can your vlookup formula be embedded into the formula above ?

 

[AhoyNC]

You would want to replace AH224 with the VLOOKUP formula.