Bands and Dates - Lookup/Match?

[balusm]

Hi Everyone

I was wondering what the most efficent way of solving this might be.

I want cells D9 and D10 return the percentage rate from H4:H7, base on the dates in B9 and B10, as well as the amounts in C9 and C10.

The banding's lower treshhold is constant but the upper treshold changes on every 1 April.

Many thanks for your help!

 

[jasonb75]

The banding's lower treshhold is constant but the upper treshold changes on every 1 April.

That is incorrect, the lower limits will always move in line with the upper limits. If they do not then you will have overlapping results, for example an amount of 12 with a date after 1/4/2020 would fall into both the 5% and 7.5% bands. If the upper limits were reduced then you would end up with gaps and amounts that do not fit into any band.

To make it work properly, you will need something like this.

Book1
	B	C	D	E	F	G	H
2				01/04/2018	01/04/2019	01/04/2020
3				0	0	0	5%
4				11	12	13	7.50%
5				16	17	18	10%
6				21	22	23	12.50%
7				26	27	28	0%
8
9	31/05/2018	7	5%
10	30/06/2019	18	10%
Sheet2

Cell Formulas
Range	Formula
D9:D10	D9	=LOOKUP(C9,XLOOKUP(B9,$E$2:$G$2,$E$3:$G$7,,-1),$H$3:$H$7)