Help please index match/vlook up

[mina91709]

Hello everyone,
First of all, I would like to thank you for taking the time to help me out. Here is what I am looking to do:
-I have a table with categories that have a rate value for certain periods (dates) (Given)
-Then I have another(second table) table that have the same categories, start date input , end date input , value cell (formula cell)
-The formula will need to look at the input of the start date and end date (in the second table) and populate the correct value from the first table
-If the duration falls between two period it will need to populate the average of the two.
here is an example below, I hope my explanation made sense.

GIVEN VALUES/LOOK UP TALBE
CATEGORY	RATES GOOD UP TO 6/30/2017	RATES GOOD UP TO 6/30/2018	RATES GOOD UP TO 6/30/2019
A	20	22	26
B	30	35	40
C	40	45	50
D	50	55	60
E	60	65	70
F	70	75	80
G	80	90	100
Formula under the value cells will look at the start date and end date then will populate the correct value based on the period it falls in. If the duration falls in two periods it will take the average of the two period
CATEGORY	VALUE	START DATE	END DATE	Formula under the value cells will do the following
A	20	4/30/2017	5/30/2017	since the period falls in 6/30/2017 rates it will populate the values for that period
B	32.5	3/30/2017	5/30/2018	since period falls in two periods 6/30/2017&5/30/2018 formula will average two values
C	50	7/30/2018	4/30/2019	since the period falls in 6/30/2019 it will populate the values for that period

<colgroup><col><col span="3"><col></colgroup><tbody>
</tbody>

Thank you very much

 

[Marcelo Branco]

Maybe this...

To make the formula a little bit shorter (still long..) and easier to understand let's create a named constant array
Formulas > Names Manager > New
Name: ArrCol
Refers to: ={1,2,3}

	A​	B​	C​	D​
1​		RATES GOOD UP TO​	RATES GOOD UP TO​	RATES GOOD UP TO​
2​	CATEGORY​	06/30/2017​	06/30/2018​	06/30/2019​
3​	A​	20​	22​	26​
4​	B​	30​	35​	40​
5​	C​	40​	45​	50​
6​	D​	50​	55​	60​
7​	E​	60​	65​	70​
8​	F​	70​	75​	80​
9​	G​	80​	90​	100​
10​
11​
12​	CATEGORY​	VALUE​	START DATE​	END DATE​
13​	A​	20​	04/30/2017​	05/30/2017​
14​	B​	32,5​	03/30/2017​	05/30/2018​
15​	C​	50​	07/30/2018​	04/30/2019​
16​	D​	57,5​	05/02/2018​	04/10/2019​
17​	E​	65​	02/27/2017​	03/15/2019​

<tbody>
</tbody>

Observe that i create a new row to store real dates in B2:D2

Formula in B13 copied down
=IF(A13="","",SUMPRODUCT(INDEX($B$3:$D$9,MATCH(A13,$A$3:$A$9,0),0),--((ArrCol>=IFERROR(MATCH(C13,$B$2:$D$2)+1,1))*(ArrCol<=IFERROR(MATCH(D13,$B$2:$D$2)+1,1))))/SUMPRODUCT(--((ArrCol>=IFERROR(MATCH(C13,$B$2:$D$2)+1,1))*(ArrCol<=IFERROR(MATCH(D13,$B$2:$D$2)+1,1)))))

Hope this helps

M.

 

[Marcelo Branco]

The formula above has a flaw - it doesn't work properly if start or end date coincides with the end of a period (06/30/xxxx). The correction would make the formula still longer and very hard to understand and maintain.
So let's take advantage that Excel has many columns and use two helper columns (gray area).

Something like this

	A​	B​	C​	D​	E​	F​
1​		RATES GOOD UP TO​	RATES GOOD UP TO​	RATES GOOD UP TO​
2​	CATEGORY​	06/30/2017​	06/30/2018​	06/30/2019​
3​	A​	20​	22​	26​
4​	B​	30​	35​	40​
5​	C​	40​	45​	50​
6​	D​	50​	55​	60​
7​	E​	60​	65​	70​
8​	F​	70​	75​	80​
9​	G​	80​	90​	100​
10​
11​
12​	CATEGORY​	VALUE​	START DATE​	END DATE​	START DATE Position​	END DATE Position​
13​	A​	20​	04/30/2017​	06/30/2017​	1​	1​
14​	B​	32,5​	03/30/2017​	05/30/2018​	1​	2​
15​	C​	50​	07/30/2018​	04/30/2019​	3​	3​
16​	D​	57,5​	05/02/2018​	04/10/2019​	2​	3​
17​	E​	65​	02/27/2017​	03/15/2019​	1​	3​

<tbody>
</tbody>

1. Create the named constant array ArrCol as showed above (post #2)

2. Helper columns
Put this formula in E13
=IFERROR(MATCH(C13,$B$2:$D$2)+1*ISNA(MATCH(C13,$B$2:$D$2,0)),1)
copy/drag to F13 and down

Formula in B13 copied down
=IF(A13="","",SUMPRODUCT(INDEX($B$3:$D$9,MATCH(A13,$A$3:$A$9,0),0),--((ArrCol>=E13)*(ArrCol<=F13)))/SUMPRODUCT(--((ArrCol>=E13)*(ArrCol<=F13))))

M.

 

[mina91709]

Maybe this...

To make the formula a little bit shorter (still long..) and easier to understand let's create a named constant array
Formulas > Names Manager > New
Name: ArrCol
Refers to: ={1,2,3}

	A​	B​	C​	D​
1​		RATES GOOD UP TO​	RATES GOOD UP TO​	RATES GOOD UP TO​
2​	CATEGORY​	06/30/2017​	06/30/2018​	06/30/2019​
3​	A​	20​	22​	26​
4​	B​	30​	35​	40​
5​	C​	40​	45​	50​
6​	D​	50​	55​	60​
7​	E​	60​	65​	70​
8​	F​	70​	75​	80​
9​	G​	80​	90​	100​
10​
11​
12​	CATEGORY​	VALUE​	START DATE​	END DATE​
13​	A​	20​	04/30/2017​	05/30/2017​
14​	B​	32,5​	03/30/2017​	05/30/2018​
15​	C​	50​	07/30/2018​	04/30/2019​
16​	D​	57,5​	05/02/2018​	04/10/2019​
17​	E​	65​	02/27/2017​	03/15/2019​

<tbody>
</tbody>

Observe that i create a new row to store real dates in B2:D2

Formula in B13 copied down
=IF(A13="","",SUMPRODUCT(INDEX($B$3:$D$9,MATCH(A13,$A$3:$A$9,0),0),--((ArrCol>=IFERROR(MATCH(C13,$B$2:$D$2)+1,1))*(ArrCol<=IFERROR(MATCH(D13,$B$2:$D$2)+1,1))))/SUMPRODUCT(--((ArrCol>=IFERROR(MATCH(C13,$B$2:$D$2)+1,1))*(ArrCol<=IFERROR(MATCH(D13,$B$2:$D$2)+1,1)))))

Hope this helps

M.

Thank you for the response. I plugged it in and i got #NAME? error. I would appreciate it if you can help me resolve it.
Thank you

 

[mina91709]

ADVERTISEMENT

Thank you for your response, I tried this method and i got #value! error. Also i am not worried about the category match all i want is to look at the start and end date and get the correct value of the rate. If you could help me resolve, i would truly appreciate it.

Thank you

 

[mina91709]

I am trying to fix my formula which cells should be named ArrCol

 

[Marcelo Branco]

I am trying to fix my formula which cells should be named ArrCol

The formula worked perfectly for me.

No cells should be named ArrCol. This is a name for a constant array {1,2,3} - if you are having problems with ArrCol you can try to substitute, in the formula, every instance of ArrCol by {1,2,3}

I have to leave now - maybe later i can try to help - if you are still having problems..

M.