Choice Year Nearest 2020 And Not Over 2020

[muhammad susanto]

hi all..

how to choice or get year based on criteria :
- year nearest 2020 and year not over 2020
my target in cell F3 down
this table

Book4
	C	D	E	F
2	year base	year1 (given)	year2 (given)	my expected result
3	2020	1989	2019	2019
4	2020	2005	2023	2005
5	2020	2030	2010	2010
6	2020	2009	2035	2009
7	2020	2025	2020	2020
Sheet1

thank any help.
.sst

 

[FormR]

Hi, here's one way.

Book5
	A	B	C	D
1	year base	year1 (given)	year2 (given)	my expected result
2	2020	1989	2019	2019
3	2020	2005	2023	2005
4	2020	2030	2010	2010
5	2020	2009	2035	2009
6	2020	2025	2020	2020
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=MAXIFS(B2:C2,B2:C2,"<="&A2)

 

[muhammad susanto]

hi ...not work show #NAME?
maybe you use Office 365? I'm use office 2010 and 2016

 

[FormR]

Here's an alternative you can try.

Book6
	A	B	C	D
1	year base	year1 (given)	year2 (given)	my expected result
2	2020	1989	2019	2019
3	2020	2005	2023	2005
4	2020	2030	2010	2010
5	2020	2009	2035	2009
6	2020	2025	2020	2020
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=AGGREGATE(14,6,B2:C2/(B2:C2<=A2),1)

 

[muhammad susanto]

great... this work perfect..
thank you, FormR

 

[FormR]

for year1 2009 and year2 2035, the result is 2035 should be 2009

It returns 2009 for me?

Book6
	A	B	C	D
1	year base	year1 (given)	year2 (given)	my expected result
2	2020	2009	2035	2009
Sheet1

Cell Formulas
Range	Formula
D2	D2	=AGGREGATE(14,6,B2:C2/(B2:C2<=A2),1)