# Thread: Match and display question

1. ## Match and display question

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!!!  Reply With Quote

2. 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,""))  Reply With Quote

3. This method uses array formulas:  Reply With Quote

4. It is exactly what I'm looking for, thank you so much! I hope this helps somebody else too!  Reply With Quote

5. 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!  Reply With Quote

=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,""))  Reply With Quote

arrays, frequency, index, match 