Search and Matching issue

[trianglet]

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

[Fotis1991]

Try

=max(if(a1:a1000=5;b1:b1000))

=min(if(a1:a1000=5;b1:b1000))

[benishiryo]

hi trianglet, welcome to the forum. assuming your data is from A1:A15, you can copy this formula into the formula bar:
=MAX(IF(A1:A15=5,B1:B15))

and press CTRL + SHIFT + ENTER to confirm

you can also place the number 5 somewhere say in C1 to change the criteria more easily & see the change:
=MAX(IF(A1:A15=C1,B1:B15))

[trianglet]

Thank you so much for your speedy response, and thank you for your welcome. I will spend more time here and help people less knowledgable than myself to pay it forwards.

Can I ask a followup question? My question is a little more complicated in "real life", than it is in my simplified example. In reality, my columns are more like this:

col1 col2
3.054 223
4.893 890
5.145 -3222

And the value I'm searching for can be anywheres in the middle, say, 4.5. Can the function be modified to check for the closest available value, in this case 4.893? Thanks again, and if you don't have time to help again, I understand!

[Kevin UK]

Deleted post