# Match and display question

#### trianglet

##### New member
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
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
This method uses array formulas:

#### Attachments

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

#### trianglet

##### New member
It is exactly what I'm looking for, thank you so much! I hope this helps somebody else too!

#### trianglet

##### New member
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!