Index/Match Duplicate Values With No Helper Cells

[wsr429]

Hi, I am using Index, Match and Large in order to the locate the highest date (Col A) and then return its corresponding number (Col B) – see attached. It works well using this formula - unless there are duplicate dates (Col A):

IFERROR(INDEX(B1:B8,MATCH(LARGE(A1:A8,1),A1:A8,0),1),"")

I found a similar post (Index Match Duplicate Values With No Helper Cells [SOLVED]) which dealt with the data running across from A1:H2.

But I am not sure what the corresponding formula would be for data which goes down from A1:B8. Is it possible to convert their formula (see below) for my spreadsheet (without using a helper cell)?

INDEX($A1:$H1,MATCH(LARGE($A2:$H2-COLUMN($A2:$H2)/10^10,COLUMNS($A5:A5)),$A2:$H2-COLUMN($A2:$H2)/10^10,0))

I am using Excel for Mac (version 16).

Thank you,

William

 

[Domenic]

Try the following...

A11, confirmed with CTRL+SHIFT+ENTER, and copied across:

=INDEX($B$1:$B$8,MATCH(LARGE($A$1:$A$8+ROW($A$1:$A$8)/10^10,COLUMNS($A11:A11)),$A$1:$A$8+ROW($A$1:$A$8)/10^10,0))

wsr.xlsm
	A	B	C	D	E	F	G	H	I
1	3/15/2024	1
2	4/10/2024	2
3	4/10/2024	3
4	5/12/2024	4
5	5/12/2024	5
6	6/18/2024	6
7	7/22/2024	7
8	7/22/2024	8
9
10
11	8	7	6	5	4	3	2	1
12
Sheet2

Cell Formulas
Range	Formula
A11:H11	A11	=INDEX($B$1:$B$8,MATCH(LARGE($A$1:$A$8+ROW($A$1:$A$8)/10^10,COLUMNS($A11:A11)),$A$1:$A$8+ROW($A$1:$A$8)/10^10,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

However, if your data will always be sorted by date in ascending order, and you always want to return from last to first when duplicates exist, maybe the following would suffice...

wsr.xlsm
	A	B	C	D	E	F	G	H	I
1	3/15/2024	1
2	4/10/2024	2
3	4/10/2024	3
4	5/12/2024	4
5	5/12/2024	5
6	6/18/2024	6
7	7/22/2024	7
8	7/22/2024	8
9
10
11	8	7	6	5	4	3	2	1
12
Sheet2

Cell Formulas
Range	Formula
A11:H11	A11	=INDEX($B$1:$B$8,ROWS($B$1:$B$8)-COLUMNS($A11:A11)+1)

Hope this helps!

 

[wsr429]

Thanks so much for your prompt reply, Domenic. Your formula works perfectly in my spreadsheet. I can't guarantee the dates will always be in ascending order so I will use your first formula. Once again, I really appreciate your help!

 

[Domenic]

That's great, I'm glad I could help.

Cheers!