+ Reply to Thread
Results 1 to 4 of 4

IF statement to return string based on highest value in range of columns

  1. #1
    Registered User
    Join Date
    07-15-2015
    Location
    Canada
    MS-Off Ver
    Mac 2011
    Posts
    2

    IF statement to return string based on highest value in range of columns

    I have a table with four columns of integer values. In the fifth column I'd like to have it print a specific string based on which column has the highest value in each row, or a different string if no column has the highest value (i.e. two columns have the same value). Is there a way to craft an IF statement to say:

    -If A1 is the highest value in the range of A1–D1 print "A"
    -If B1 is the highest in the range print "B"
    -If C1 is the highest in the range print "C"
    -If D1 is the highest in the range print "D"
    -If more than one column has the highest value print "None"

    I'm getting lost in nested if-else statements trying to accomplish this and feel like there must be a more straightforward way of doing it than I am attempting.

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: IF statement to return string based on highest value in range of columns

    Try this...

    =IF(COUNTIF(A1:D1,MAX(A1:D1))>1,"None",INDEX({"A","B","C","D"},MATCH(MAX(A1:D1),A1:D1,0)))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    07-15-2015
    Location
    Canada
    MS-Off Ver
    Mac 2011
    Posts
    2

    Re: IF statement to return string based on highest value in range of columns

    That seems to work, thank you! FAR simpler than what I was cobbling together before.

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: IF statement to return string based on highest value in range of columns

    You're welcome. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 8
    Last Post: 01-26-2012, 12:08 PM
  2. Return based on highest number in row
    By jillteresa in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-10-2009, 01:43 PM
  3. I want vlookup to find a range, and return highest value in that range
    By crazie in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-05-2008, 01:20 PM
  4. How do I return the highest value in a range of cells
    By pjs83 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  5. [SOLVED] How do I return the highest value in a range of cells
    By pjs83 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  6. How do I return the highest value in a range of cells
    By pjs83 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  7. [SOLVED] How do I return the highest value in a range of cells
    By pjs83 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  8. How do I return the highest value in a range of cells
    By pjs83 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-06-2005, 05:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1