Index Function

[ripdaman]

In my worksheet, what is desired is minimum value in Columns I, II and III from years 2017 to 2027 excluding zero. However, from years 2017 to current year i.e. 2023, zeroes should be counted as minimum values and should not be excluded. I am getting the values right using IFS and MIN function but the INDEX function shows the wrong year for Columns II and III but shows the right value for Column I. Please guide me.

Book1
	B	C	D	E
3	Year	I	II	III
4	2027	0	0	0
5	2026	0	0	0
6	2025	0	0	0
7	2024	0	0	0
8	2023	7	0	1
9	2022	9	4	0
10	2021	4	2	3
11	2020	7	3	11
12	2019	4	10	6
13	2018	1	4	3
14	2017	6	1	5
15
16		1	0	0
17		2018	2027	2027
Sheet1

Cell Formulas
Range	Formula
C16:E16	C16	=IFS(YEAR(TODAY())<2024,MIN(C8:C14),YEAR(TODAY())>2024<2025,MIN(C7:C14),YEAR(TODAY())>2025<2026,MIN(C6:C14),YEAR(TODAY())>2026<2027,MIN(C5:C14),YEAR(TODAY())=2027,MIN(C4:C14))
C17:E17	C17	=INDEX((Sheet1!$B$4:$B$14),MATCH(IFS(YEAR(TODAY())<2024,MIN(C8:C14),YEAR(TODAY())>2024<2025,MIN(C7:C14),YEAR(TODAY())>2025<2026,MIN(C6:C14),YEAR(TODAY())>2026<2027,MIN(C5:C14),YEAR(TODAY())=2027,MIN(C4:C14)),C4:C14,0))

 

[fjns]

Hi, try this formula for C17
=INDEX((Sheet1!$B$4:$B$14),XMATCH(IFS(YEAR(TODAY())<2024,MIN(C8:C14),YEAR(TODAY())>2024<2025,MIN(C7:C14),YEAR(TODAY())>2025<2026,MIN(C6:C14),YEAR(TODAY())>2026<2027,MIN(C5:C14),YEAR(TODAY())=2027,MIN(C4:C14)),C4:C14,0,-1))

 

[Fluff]

Hi & welcome to MrExcel.
It's not entirely clear what you are trying to do as this YEAR(TODAY())>2024<2025 is not a valid calculation & the year cannot be greater than 2024 & less than 2025 at the same time.

 

[Fluff]

Is this what you are trying to do

Fluff.xlsm
	A	B	C	D	E
1
2
3		Year	I	II	III
4		2027	0	0	0
5		2026	0	0	0
6		2025	0	0	0
7		2024	0	0	0
8		2023	7	0	1
9		2022	9	4	0
10		2021	4	2	3
11		2020	7	3	11
12		2019	4	10	6
13		2018	1	4	3
14		2017	6	1	5
15
16			1	0	0
17			2018	2023	2022
Data

Cell Formulas
Range	Formula
C16:E17	C16	=TOCOL(TAKE(SORT(FILTER(HSTACK(C4:C14,$B$4:$B$14),$B$4:$B$14<=YEAR(TODAY()))),1))
Dynamic array formulas.

 

[fjns]

Hi, see the linked file for an another solution with simpler functions (INDEX, MAX, MIN, XMATCH)...

The formulas used in the table:
=MIN(($B4:$B14>YEAR(TODAY()))*(C4:C14=0)*MAX(C4:C14)+(C4:C14))
=INDEX($B4:$B14,XMATCH(MIN(($B4:$B14>YEAR(TODAY()))*(C4:C14=0)*MAX(C4:C14)+(C4:C14)),C4:C14,0,-1))

Index.xlsx

 

[ripdaman]

Hi, see the linked file for an another solution with simpler functions (INDEX, MAX, MIN, XMATCH)...

The formulas used in the table:
=MIN(($B4:$B14>YEAR(TODAY()))*(C4:C14=0)*MAX(C4:C14)+(C4:C14))
=INDEX($B4:$B14,XMATCH(MIN(($B4:$B14>YEAR(TODAY()))*(C4:C14=0)*MAX(C4:C14)+(C4:C14)),C4:C14,0,-1))

Index.xlsx

View attachment 87321

Thanks for the quick resolution!

 

[fjns]

Hi, I am very glad that the formulas work.
If I have enough knowledge, I am happy to help at any time.