Hello everybody. I am trying to figure out what I consider to be a very difficult problem in Excel, and I am having no luck on the internet searching an answer. My job depends on me figuring this out!

Let's say I have two columns, Column 1, which contains a range of values, many of them are repeating, and column 2, which contains useful information corresponding to the values in column 1. Like this:

Col1: Col2:
1 74
2 3
3 -78
3 4
3 9
4 0
5 74
5 6
5 4
5 4
5 78
6 3
7 74
8 4
9 1

I would like to be able to select a value which I know occurs in col1, then search among the corresponding values in col2 to find the min/max. For example, Lets say I will search for 5, and return the maximum corrsponding value in col2. I want the cell to return 78.

I know I need to use arrays, index, match, and possibly frequency, but I can't put it all together. Thank you so much in advance!!!