Help please index match/vlook up

mina91709

New Member
Joined
Mar 25, 2017
Messages
25
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
CATEGORYRATES GOOD UP TO 6/30/2017RATES GOOD UP TO 6/30/2018RATES GOOD UP TO 6/30/2019
A202226
B303540
C404550
D505560
E606570
F707580
G8090100
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 DATEFormula under the value cells will do the following
A204/30/20175/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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,414
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.
 
Last edited:

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,414
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.
 
Last edited:

mina91709

New Member
Joined
Mar 25, 2017
Messages
25
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

New Member
Joined
Mar 25, 2017
Messages
25

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
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,414
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,343
Messages
5,601,073
Members
414,426
Latest member
fraru

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top