How to get lowest last date of lowest price if you have 2 or more equal lowest prices?

[Mathman]

If you can help that would be great.


Here's the example




Dates in years 2018 2017 2016 2015 2014
Prices 5 5 5 7 10


First the lowest price is 5 but then based on that I want the most recent or closest date to current which in this case would be year 2018.


Any suggestions?


Thank you! ​

 

[Eric W]

Try:

	A	B	C	D	E	F
Dates
Prices
Most recent date for low value

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2018[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]2014[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet8​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]A6[/TH]
[TD="align: left"]{=MAX(IF(B3:F3=MIN(B3:F3),B2:F2))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]


You can also try:

=INDEX(B2:F2,MATCH(MIN(B3:F3),B3:F3,0))

if you don't want an array formula, and the dates are in descending order.

 

[Mathman]

Thanks!

 

[Eric W]

Glad to help!