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.
[TABLE="width: 1256"]
<colgroup><col><col span="3"><col></colgroup><tbody>[TR]
[TD="colspan: 4"]GIVEN VALUES/LOOK UP TALBE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CATEGORY[/TD]
[TD]RATES GOOD UP TO 6/30/2017[/TD]
[TD]RATES GOOD UP TO 6/30/2018[/TD]
[TD]RATES GOOD UP TO 6/30/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]20[/TD]
[TD]22[/TD]
[TD]26[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]30[/TD]
[TD]35[/TD]
[TD]40[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]40[/TD]
[TD]45[/TD]
[TD]50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]50[/TD]
[TD]55[/TD]
[TD]60[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]60[/TD]
[TD]65[/TD]
[TD]70[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]70[/TD]
[TD]75[/TD]
[TD]80[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]80[/TD]
[TD]90[/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]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 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CATEGORY [/TD]
[TD]VALUE [/TD]
[TD] START DATE[/TD]
[TD] END DATE[/TD]
[TD]Formula under the value cells will do the following[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]20[/TD]
[TD="align: right"]4/30/2017[/TD]
[TD="align: right"]5/30/2017 [/TD]
[TD]since the period falls in 6/30/2017 rates it will populate the values for that period[/TD]
[/TR]
[TR]
[TD]
B[/TD]
[TD]
32.5[/TD]
[TD="align: right"]
3/30/2017[/TD]
[TD="align: right"]
5/30/2018 [/TD]
[TD]
since period falls in two periods 6/30/2017&5/30/2018 formula will average two values [/TD]
[/TR]
[TR]
[TD]
C[/TD]
[TD]
50[/TD]
[TD="align: right"]
7/30/2018[/TD]
[TD="align: right"]
4/30/2019[/TD]
[TD]
since the period falls in 6/30/2019 it will populate the values for that period[/TD]
[/TR]
</tbody>[/TABLE]


Thank you very much
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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}


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


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:
Upvote 0
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

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A
[/TD]
[TD="bgcolor: #DCE6F1"]
B
[/TD]
[TD="bgcolor: #DCE6F1"]
C
[/TD]
[TD="bgcolor: #DCE6F1"]
D
[/TD]
[TD="bgcolor: #DCE6F1"]
E
[/TD]
[TD="bgcolor: #DCE6F1"]
F
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1
[/TD]
[TD][/TD]
[TD]
RATES GOOD UP TO​
[/TD]
[TD]
RATES GOOD UP TO​
[/TD]
[TD]
RATES GOOD UP TO​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2
[/TD]
[TD]
CATEGORY​
[/TD]
[TD]
06/30/2017​
[/TD]
[TD]
06/30/2018​
[/TD]
[TD]
06/30/2019​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3
[/TD]
[TD]
A​
[/TD]
[TD]
20​
[/TD]
[TD]
22​
[/TD]
[TD]
26​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4
[/TD]
[TD]
B​
[/TD]
[TD]
30​
[/TD]
[TD]
35​
[/TD]
[TD]
40​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5
[/TD]
[TD]
C​
[/TD]
[TD]
40​
[/TD]
[TD]
45​
[/TD]
[TD]
50​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6
[/TD]
[TD]
D​
[/TD]
[TD]
50​
[/TD]
[TD]
55​
[/TD]
[TD]
60​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7
[/TD]
[TD]
E​
[/TD]
[TD]
60​
[/TD]
[TD]
65​
[/TD]
[TD]
70​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8
[/TD]
[TD]
F​
[/TD]
[TD]
70​
[/TD]
[TD]
75​
[/TD]
[TD]
80​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9
[/TD]
[TD]
G​
[/TD]
[TD]
80​
[/TD]
[TD]
90​
[/TD]
[TD]
100​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
12
[/TD]
[TD]
CATEGORY​
[/TD]
[TD]
VALUE​
[/TD]
[TD]
START DATE​
[/TD]
[TD]
END DATE​
[/TD]
[TD]
START DATE Position​
[/TD]
[TD]
END DATE Position​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
13
[/TD]
[TD]
A​
[/TD]
[TD]
20​
[/TD]
[TD]
04/30/2017​
[/TD]
[TD]
06/30/2017​
[/TD]
[TD="bgcolor: #D9D9D9"]
1​
[/TD]
[TD="bgcolor: #D9D9D9"]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
14
[/TD]
[TD]
B​
[/TD]
[TD]
32,5​
[/TD]
[TD]
03/30/2017​
[/TD]
[TD]
05/30/2018​
[/TD]
[TD="bgcolor: #D9D9D9"]
1​
[/TD]
[TD="bgcolor: #D9D9D9"]
2​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
15
[/TD]
[TD]
C​
[/TD]
[TD]
50​
[/TD]
[TD]
07/30/2018​
[/TD]
[TD]
04/30/2019​
[/TD]
[TD="bgcolor: #D9D9D9"]
3​
[/TD]
[TD="bgcolor: #D9D9D9"]
3​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
16
[/TD]
[TD]
D​
[/TD]
[TD]
57,5​
[/TD]
[TD]
05/02/2018​
[/TD]
[TD]
04/10/2019​
[/TD]
[TD="bgcolor: #D9D9D9"]
2​
[/TD]
[TD="bgcolor: #D9D9D9"]
3​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
17
[/TD]
[TD]
E​
[/TD]
[TD]
65​
[/TD]
[TD]
02/27/2017​
[/TD]
[TD]
03/15/2019​
[/TD]
[TD="bgcolor: #D9D9D9"]
1​
[/TD]
[TD="bgcolor: #D9D9D9"]
3​
[/TD]
[/TR]
</tbody>[/TABLE]


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:
Upvote 0
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}


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


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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

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
Back
Top