Thursday, August 11, 2011

yogi_Pull The Highest / Lowest Values From An Array Based On Specified Condition(s)

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  

chrissugden said:
How can I pull the highest or lowest value from am array returned as result of some conditions?
I have searched for this answer, but cannot find it. I want to pull the highest or lowest value after using a condition to return all matching rows:
3001 Open
3017 WIP
3017 WIP
3017 Open
3017 Complete
For the above set of data, I want to match the value 3017 and return either a single "WIP" or "Complete".
I have tried using the FILTER function, and it worked great except that it fills my spreadsheet with CONTINUEs. Those CONTINUEs then overwrite the lookup for the next row, and so on.
Any suggestions appreciated.
here we go ...