excel: If I search for a MIN value in a range, and 2 values ​​are MIN, it displays only the first

advertisements

I have a function where I look for the MIN value in a range, lets say 9, 18, 12, 9. Both 9's are the MIN, but MIN only looks at the 'first' min value. I want to be able to also give the 2nd '9' in my range as an option, to make it more clear: The columns in which these values are represent a location, and the value itself represents how easy it is to go there, so if 2 locations have value '9', I want a way to not only display the first '9' column as a result, but also the 2nd '9' column since this result is just as valid. How can I address the 2nd value when using this function, so that my result shows both the first AND second column with these MIN values. IS there a way to exclude the already found MIN value in another MIN search? Or how would I address this 2nd 'min' value?

Thank you for your time.


Say we have data in column A

In C1 enter the array formula:

=IFERROR(SMALL(IF(($A$1:$A$20)=MIN(A$1:A$20),ROW($A$1:$A$20),""),ROW()),"")

and copy down.

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.

This will display the row number(s) of the cells containing the minimum value: