Results 1 to 6 of 6

Thread: Match and display question

  1. #1

    Match and display question



    Register for a FREE account, and/
    or Log in to avoid these ads!

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

  2. #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,""))
    Attached Files Attached Files

  3. #3
    Conjurer CheshireCat's Avatar
    Join Date
    Dec 2011
    Location
    Victoria, Canada
    Posts
    120
    Articles
    0
    Excel Version
    Microsoft Excel 2013
    This method uses array formulas:
    Attached Files Attached Files

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

  5. #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!

  6. #6
    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,""))

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •