Help with Index Match/Sumproduct/Vlookup

[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 rate values for certain periods (dates) (Given)
-Then I have another(second table) table that has three columns , 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 periods it will need to populate the average values of the two.
here is an example below, I hope my explanation made sense.

GIVEN DATA/ GIVEN TABLE
[TABLE="width: 500"]
<tbody>[TR]
[TD]6/30/2017[/TD]
[TD]6/30/2018[/TD]
[TD]6/30/2019[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]25[/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]35[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]45[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD]55[/TD]
[TD]60[/TD]
[/TR]
</tbody>[/TABLE]

FORMULA & INPUT TABLE
[TABLE="width: 500"]
<tbody>[TR]
[TD]VALUE[/TD]
[TD]START DATE[/TD]
[TD] END DATE[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]4/30/2017[/TD]
[TD]5/30/2017[/TD]
[/TR]
[TR]
[TD]32.5[/TD]
[TD]3/30/2017[/TD]
[TD]5/30/2018[/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD]7/30/2018[/TD]
[TD]4/30/2019[/TD]
[/TR]
[TR]
[TD]57.5[/TD]
[TD]5/30/2018[/TD]
[TD]5/30/2019[/TD]
[/TR]
</tbody>[/TABLE]


Note that the 2nd and 4th inputs fall in two periods and there for the value populated is the average.

Thank you very much for your help

 

[L. Howard]

Isn't MVP Marcelo Branco offering a solution in your other post of the same problem/query?

Howard

 

[mina91709]

Isn't MVP Marcelo Branco offering a solution in your other post of the same problem/query?

Howard

I couldn't get his solution to work and I got rid of the category column. If you could please help me I would appreciate it.

thank you very much

 

[L. Howard]

Protocol is to stick to the original post, as double posting is frowned on in this forum.

But I must say... You have one of the MVP experts responding to your post, (his last reply as below). If Marcelo Branco cannot resolve your issue it is almost a given that NOBODY can.

Howard

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.

 

[mina91709]

Protocol is to stick to the original post, as double posting is frowned on in this forum.

But I must say... You have one of the MVP experts responding to your post, (his last reply as below). If Marcelo Branco cannot resolve your issue it is almost a given that NOBODY can.

Howard

Thank you

 

[Marcelo Branco]

You cannot get rid of the column Category - it's necessary to get the corresponding/ proper row. Without the category as a search value to locate the values in the proper row of your data, how to obtain the desired values? Why should be 20 the answer of the first formula you showed in post #1 of this thread? Why 32.5 in the second formula?

Please try again the formula i suggested in
https://www.mrexcel.com/forum/excel-questions/1001589-help-please-index-match-vlook-up.html

I spent some time to create and test it and it worked perfectly for me.

Regards

M.

 

[Marcelo Branco]

Protocol is to stick to the original post, as double posting is frowned on in this forum.

But I must say... You have one of the MVP experts responding to your post, (his last reply as below). If Marcelo Branco cannot resolve your issue it is almost a given that NOBODY can.

Howard

@Howard

In spite this is not really true - there are many members very proficient in formulas here, MVPs and non MVPs - i really appreciated it.

Thank you very much.

M.

 

[mina91709]

You cannot get rid of the column Category - it's necessary to get the corresponding/ proper row. Without the category as a search value to locate the values in the proper row of your data, how to obtain the desired values? Why should be 20 the answer of the first formula you showed in post #1 of this thread? Why 32.5 in the second formula?

Please try again the formula i suggested in
https://www.mrexcel.com/forum/excel-questions/1001589-help-please-index-match-vlook-up.html

I spent some time to create and test it and it worked perfectly for me.

Regards

M.

Thann you for all your help I appreciate it. I will try again and will let you know how it goes. Thank you ??