Reply With Quote
See if this is what you are trying to accomplish. those are array formulas in G3 and H3.
=MAX(IF($A$3:$A$18=$C$1,$B$3:$B$18,""))
=MIN(IF($A$3:$A$18=$C$1,$B$3:$B$18,""))
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!!!
See if this is what you are trying to accomplish. those are array formulas in G3 and H3.
=MAX(IF($A$3:$A$18=$C$1,$B$3:$B$18,""))
=MIN(IF($A$3:$A$18=$C$1,$B$3:$B$18,""))
This method uses array formulas:
It is exactly what I'm looking for, thank you so much! I hope this helps somebody else too!
Can I expand my question just a touch? What if the value I'm searching for is not exactly what I will find in column 1. For example, lets say I will search for 3.8, then the spreadsheet falls apart. In reality, I want the closest value to 3.8, that is, 4. Can the function be modified? Thank you again!
adjust to match your cells
=MAX(IF($A$3:$A$18=(ROUND(C1,0)),$B$3:$B$18,""))
=MIN(IF($A$3:$A$18=(ROUND(C1,0)),$B$3:$B$18,""))
Bookmarks