Match and display question

trianglet

New member
Joined
Sep 25, 2012
Messages
3
Reaction score
0
Points
0
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!!!
 

tommyt61

New member
Joined
Mar 26, 2011
Messages
153
Reaction score
0
Points
0
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,""))
 

Attachments

  • MAX­_MIN VALUES.xlsx
    10.3 KB · Views: 12

CheshireCat

New member
Joined
Dec 30, 2011
Messages
121
Reaction score
0
Points
0
Location
Victoria, Canada
Excel Version(s)
Microsoft Excel 2013
This method uses array formulas:
 

Attachments

  • trianglet 2012-09-25.xls
    21 KB · Views: 13

trianglet

New member
Joined
Sep 25, 2012
Messages
3
Reaction score
0
Points
0
It is exactly what I'm looking for, thank you so much! I hope this helps somebody else too!
 

trianglet

New member
Joined
Sep 25, 2012
Messages
3
Reaction score
0
Points
0
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!
 

tommyt61

New member
Joined
Mar 26, 2011
Messages
153
Reaction score
0
Points
0
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,""))
 
Top