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

 

[awoohaw]

Thanks for using the xl2bb add in! But, we also need to know your excel version. Can you update your profile show it shows up on your thread button?

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[awoohaw]

this will solve it for the scenario posted, i think. But, am unsure if you have a larger scope:

Mr excel questions 35.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		2021-04-01	100	6%	200	13%	300	20%	400	30%	500	50%
3	Date	2023-04-01		2022-04-01	100	7%	200	15%	300	25%	400	35%	500	55%
4	Rate	20%		2023-04-01	100	8%	200	20%	300	30%	400	40%	500	60%
joand

Cell Formulas
Range	Formula
B4	B4	=INDEX($D$2:$N$4,MATCH(YEAR(B3),YEAR($D$2:$D$4),0),MATCH(ROUNDDOWN(B2,-2),$D$4:$N$4,0)+1)

 

[awoohaw]

Here is another way to do the column number locating. Again, if your scenario is different for each row then it won't work:

=INDEX($D$2:$N$4,MATCH(YEAR(B3),YEAR($D$2:$D$4),0),2*MATCH(ROUNDDOWN(B2,-2),{100,200,300,400,500},0)+1)

 

[joand]

Yup, you're right, once I changed the amounts, it is giving me #N/A error. For example i changed G4 from 200 to 250, the result should have been 20%, instead it is #N/A

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	#N/A		04/01/2023	100	8%	250	20%	300	30%	350	40%	600	60%
Sheet1

Cell Formulas
Range	Formula
B4	B4	=INDEX($D$2:$N$4,MATCH(YEAR(B3),YEAR($D$2:$D$4),0),MATCH(ROUNDDOWN(B2,-2),$D$4:$N$4,0)+1)

 

[Fluff]

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

 

[DanteAmor]

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.

 

[Fluff]

If you have 365 or 2021 try

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

 

[awoohaw]

Try this:

@DanteAmor , neat formula. What does the mod with divisor 2 do? Could you use a 1 there as well?