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:
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: