Lookup on every other column

[joand]

I want to get the specific rate for an amount that falls within the range. See example below. Any ideas?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1				Date	Amount	Rate	Amount	Rate	Amount	Rate	Amount	Rate	Amount	Rate
2	Amount	250		04/01/2021	100	6%	200	13%	300	20%	400	30%	500	50%
3	Date	04/01/2023		04/01/2022	100	7%	200	15%	300	25%	400	35%	500	55%
4	Rate	20%		04/01/2023	100	8%	200	20%	300	30%	400	40%	500	60%
5
6
Sheet1

 

[joand]

As previously requested. Can you please update your profile to show which version of Excel you are using.

I'm using 2021

 

[joand]

Try this:

varios 12may2023.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1				Date	Amount	Rate	Amount	Rate	Amount	Rate	Amount	Rate	Amount	Rate
2	Amount	250		01/04/2021	100	6%	200	13%	300	20%	400	30%	500	50%
3	Date	01/04/2023		01/04/2022	100	7%	200	15%	300	25%	400	35%	500	55%
4	Rate	20%		01/04/2023	100	8%	200	20%	300	30%	400	40%	500	60%
Hoja7

Cell Formulas
Range	Formula
B4	B4	=IF(B2<100,0,INDEX(E2:N4,MATCH(B3,D2:D4,0),MAX(IF(MOD(E2:N2,2)=0,IF(E2:N2<B2+0.001,COLUMN(E2:N2))))-3))
Press CTRL+SHIFT+ENTER to enter array formulas.

i changed G4 to 300 and I4 to 350, and the result was 20% instead of 8%

Sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1				Date	Amount	Rate	Amount	Rate	Amount	Rate	Amount	Rate	Amount	Rate
2	Amount	250		04/01/2021	100	6%	200	13%	300	20%	400	30%	500	50%
3	Date	04/01/2023		04/01/2022	100	7%	200	15%	300	25%	400	35%	500	55%
4	Rate	20%		04/01/2023	100	8%	300	20%	350	30%	400	40%	600	60%
Sheet1

Cell Formulas
Range	Formula
B4	B4	=IF(B2<100,0,INDEX(E2:N4,MATCH(B3,D2:D4,0),MAX(IF(MOD(E2:N2,2)=0,IF(E2:N2<B2+0.001,COLUMN(E2:N2))))-3))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[joand]

If you have 365 or 2021 try

=LET(f,FILTER(E2:N4,YEAR(D2:D4)=YEAR(B3)),INDEX(f,XMATCH(B2,f,-1)+1))

wow, i like this code. giving me the correct result. could you explain how this works?

 

[Fluff]

It filter the data to only return the row of interest & then finds the exact match, or next lower value, compared to B2 & then returns the col to the right of that match

 

[joand]

What would be the tweak if i start adding a date range (start and end dates)

Sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1				Date Start	Date End	Amount	Rate	Amount	Rate	Amount	Rate	Amount	Rate	Amount	Rate
2	Amount	250		04/01/2022	03/31/2023	100	6%	200	13%	300	20%	400	30%	500	50%
3	Date	04/01/2023		04/01/2023	04/15/2023	100	7%	200	15%	300	25%	400	35%	500	55%
4	Rate	#VALUE!		04/16/2023	04/30/2023	100	8%	300	20%	350	30%	400	40%	600	60%
Sheet1

Cell Formulas
Range	Formula
B4	B4	=LET(f,FILTER(F2:O4,YEAR(D2:D4)=YEAR(B3)),INDEX(f,XMATCH(B2,f,-1)+1))

 

[Fluff]

How about

=LET(f,FILTER(F2:O4,(D2:D4<=B3)*(E2:E4>=B3)),INDEX(f,XMATCH(B2,f,-1)+1))

 

[awoohaw]

My suggestion would be to make your look table more flat.
with only these columns: Start Date (preferably only the 1st or Last of each month), End Date (which may not be necessary in this structure), Amount, Rate.

ignore please.

 

[joand]

How about

=LET(f,FILTER(F2:O4,(D2:D4<=B3)*(E2:E4>=B3)),INDEX(f,XMATCH(B2,f,-1)+1))

wow genius! thanks a lot. marking it as a solution

 

[Fluff]

Glad we could help & thanks for the feedback.