Results 1 to 4 of 4

Thread: IF STATEMENT BASED ON EQUAL VALUES IN RANGE

  1. #1
    Member mrmmickle1's Avatar
    Join Date
    Sep 2012
    Posts
    51
    Rep Power
    13

    IF STATEMENT BASED ON EQUAL VALUES IN RANGE

    I am trying to compare cells within the range $T$7:$T$47. I look for the max value within this range. If two or 3 cells equal the same value within this range I would like it to compare another set of values. So for example if $t$7 and $t$9 both equaled say 15 and 15 was the Max of this range then I have another criteria set in cells $S$7:$S$47. If there is a "tie" for the max value in cells $t$7 and $t$9 (They would both = 15) then the cells adjacent to these ($S$7, $S$9) need to be compared against a value in cell $S$5. If the value in $S$7 = 35 and the value in cell $S$9 is 45 and the value in cell $S$5 is 39. I would like to take the absolute values of the differences between the cells so ABS($s$5-$S$7) = 4 and ABS($S$5-$S$9) = 6. Then take the lowest value so then in this instance Row 7 would win. I have values in the range $B$7:$B$47 that I would like to display if that cell wins so in this example I would want to display the value in cell $B$7.
    Using Excel 2010

  2. #2
    Senior Member alansidman's Avatar
    Join Date
    Apr 2012
    Posts
    125
    Rep Power
    13
    Suggest you post a sample spreadsheet with a before and after so that we can determine what you need based upon the information supplied.

  3. #3
    Member mrmmickle1's Avatar
    Join Date
    Sep 2012
    Posts
    51
    Rep Power
    13

    Example_Spreadsheet

    Here is a sample spreadsheet. I was almost able to get it to work by adding a few helper columns. I tried to write a function called LookupWinner but I couldn't get it to work correctly. This spreadsheet is nothing crazy. Just a few drop down list boxes and nested if statements. It should be pretty self explanatory... I think I may have forget to take protection off the work book. Just use 1234 as the PW.
    Attached Files Attached Files
    Last edited by mrmmickle1; 09-09-2013 at 07:57 AM.
    Using Excel 2010

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    Try this array formula.

    =IF(COUNTIF(T7:T47,MAX(T7:T47)),INDEX(B7:B47,MATCH (MIN(ABS(S5-S7:S47)),ABS(S5-S7:S47),0)),INDEX(B7:B47,MATCH(MAX(T7:T47),T7:T47, 0)))
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. ONE CELL AS RANGE IN VBA INSIDE AN IF STATEMENT
    By jffryjsphbyn in forum Excel Help
    Replies: 1
    Last Post: 07-16-2013, 09:49 PM
  2. Change Display Range Based On Change of Dropdown Values
    By rich_cirillo in forum Excel Help
    Replies: 2
    Last Post: 03-29-2013, 04:58 AM
  3. Replies: 2
    Last Post: 12-19-2012, 11:52 PM
  4. Replies: 0
    Last Post: 09-07-2012, 09:10 PM
  5. Replies: 2
    Last Post: 07-15-2012, 04:05 PM

Posting Permissions

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