Comments on: MAX function in Excel: formula examples to find and highlight highest value

MAX is one of the most straightforward and easy-to-use Excel functions. However, it does have a couple of tricks knowing which will give you a big advantage. Continue reading →

Comments page 3. Total comments: 135

  1. im trying to find the best way to write a function that will take the max of a range of cells and display the row header associated with that cell... for example take the Max of cells A1:a17 and display the row header of whichever value is produced from the max function. any input?

    1. Hi!
      Your question is not entirely clear. What row title do you want to show? What cells is the row header in?

  2. Is it possible to do an index match lookup between 2 tables and also find the highest value? The index match + max formulas I am finding all seem to be for within a single table.

    For example, if you have a list of cities and hotel prices at different times of the year in one table.
    Then in another table, you have a list of cities, with columns for different values and you want to pull in the highest hotel price from the other table. Is that possible with any kind of lookup?

  3. Hello,
    I have tried my best on my own forever. I am trying to get the largest sum of 5 numbers, however, based on a second column, the corresponding total cannot exceed a number. Example, for the below, I would like to have the left column total the largest of the 5 added up, but cannot exceed $135 based on the column to it's right. I have gotten close, but it will give me a number but don't actually know which 5 it has chosen! Ha. So best case scenario, it will highlight or say A, D, H or whatever are the five that total the most without going over $135 summed on the right column. Thanks for any help and I hope that makes sense.

    A 24.91 $37
    B 19.47 $23
    C 18.26 $38
    So On 17.27 $34
    13.48 $22
    12.37 $18
    11.45 $20
    10.68 $18
    9.49 $17
    9.34 $18
    8.99 $16
    6.25 $10
    6.15 $21
    5.38 $10
    4.28 $10

  4. I have a customer list with three columns representing stage1, stage2 and stage3. some customers have balance only in one stage while others have balance in two stages. I want to find out the stage with maximum balance for each customer. how to find the same.
    Customer STAGE1 STAGE2 STAGE3 Result I want
    ABC 100 400 STAGE3
    DEF 3000 2000 STAGE1
    MNO 5000 50 STAGE2
    KPL 250 STAGE1
    GHI -250 STAGE2

    How can I find the result with a formula for larger amount of data

    1. Hello!
      The formula below will do the trick for you:

      =INDEX($B$1:$D$1,MATCH(MAX(B2:D2),B2:D2,0))

      You can learn more about searching with INDEX + MATCH in Excel in this article on our blog.

  5. I have 2 columns of values, i have conditionally formatted each to show top 20%. I want ones that are in the top 20% of each category to be highlighted in another colour. e.g top 20% in column A Green, top 20% in column B Green. In top 20% in A and B Blue.

    Any assistance is appreciated

    Thanks,
    Luke

  6. After I find the MAX value, do I use a CONCATENATE formula to show which is the highest value? Any suggestions are welcome. Thank you.

  7. i have in
    column A: 34,34,34,33,33,33,35,35.
    column B: 3,2,1,5,4,3,2,2,1

    what function i can use to gate max value of B compare to A: 34,33,35 for each
    like in column C: 3,nil,nil,5,nil,nil,2,nil.

  8. I have been trying the formula to get headers to find maximum value in rows which goes as =INDEX($P1:$R1,0,MATCH(MAX($P2:$R2),$P2:$R2,0))
    I drag the formula through the column. It takes the 2nd row values as headers. The formula seems as =INDEX($P2:$R2,0,MATCH(MAX($P3:$R3),$P3:$R3,0)). It returns the 2nd row maximum values in the consecutive cells but not headers for the maximum value.

    How can I fix this issue that the header reference formulas do not change in the formula?

  9. Hello im trying to find the best formula for this data. I feel it should be FILTER but not sure which one to use.
    I need to filter the result in column D with the condition as
    amount = highest value in Column C
    data limited to column B of Company XYZ

    expected result should be: A9100 amount$3,428.52

    please help.. I'm not expert in excel's formula. thanks in advance..!!

    A B C D
    Company XYZ AC1000000454 $3.59 A1019
    Company XYZ AC1000000454 $67.91 AR400
    Company XYZ AC1000000454 $3,428.52 A9100
    Company XYZ AC1000000454 ($353.20) AF400
    Company XYZ AC1000000454 ($33.00) AHF00
    Company XYZ AC1000000454 ($4,256.00) A1044

  10. Hi,

    Could you please help me out on how to calculate max time from an array of 3 time stamps of 3 systems placed horizontal in range? How can I get the system names too after I try the formula or it can directly show system names that has highest time? How can I use maxifs with criteria range?

    System 1, 00:30:23
    System 2, 00:57:20
    System 3, 00:11:05

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =INDEX(A1:A10,MATCH(MAX(B1:B10),B1:B10,0))

      1. Hi,

        The 3 time stamps are placed horizontal on a row.

        A. B. C
        00:30:23. 00:57:20 00:11:05

        How can I get max value from the columns A,B,C with names system 1, system2 and systems 3 respectively in formula? Please help me out on this.

        1. Hi!
          Have you tried the ways described in this blog post? If they don’t work for you, write what formula you used and what were the problems.

          1. How can I get any of the three systems names whichever with maximum value on a single column with respect to 50 rows for 3 systems?

          2. Hi,

            I have tried with index, match, max functions.

            If I have headers : System1, System2, System3 in Column O, P, Q with 00:01:57, 00:04:00, 00:00:35 timestamps respectively.

            And there is a list of 50 timestamps under each.
            I want to find out which system has maximum timestamp value.

            I tried the formula, on 2nd row:
            INDEX($O1:$Q1,0,MATCH(MAX($O2:$Q2),$O2:$Q2,0))

            Problem1 : I don’t get the true system header name with true maximum value.

            Problem 2: the formula doesn’t work on 3rd row when I try to drag the formula for next 50 rows.
            Please suggest.

            1. Hello!
              If I got you right, the formula below will help you with your task:

              =INDIRECT(ADDRESS(1,MIN(IF(O2:Q51=MAX(O2:Q51),COLUMN(O1:Q1)))))

              This is an array formula and it needs to be entered via Ctrl + Shift + Enter, not just Enter.

              I hope it’ll be helpful.

  11. Hi

    Am working with data where I have to get the date of Highest Quantity with matching Product No.
    Maxif formula is not working with.

    Kindly suggest the way out.

    Product Qty Date
    GHI 102 30-Aug
    DEF 77 30-Aug
    ABC 21 30-Aug
    ABC 10 30-Aug
    GHI 119 20-May
    GHI 106 20-May
    GHI 94 20-May
    DEF 81 20-May
    DEF 69 20-May
    DEF 56 20-May
    ABC 44 20-May
    ABC 36 20-May
    ABC 10 20-May
    GHI 114 10-May
    DEF 90 10-May
    DEF 65 10-May
    ABC 40 10-May
    ABC 20 10-May
    GHI 110 01-May
    DEF 85 01-May
    DEF 61 01-May
    ABC 35 01-May
    GHI 123 30-Apr
    GHI 98 10-Apr
    DEF 73 10-Apr
    ABC 48 10-Apr
    ABC 30 10-Apr

    Result should be - ABC - 10-Apr (ABC highest Qty is 48 so 10-Apr)

      1. Hi

        Date format is correct.
        Have tried {=MAX(IF($B$3:$B$29=$G6,$D$3:$D$29))}
        This gives me latest date against ABC which is 30-Aug.
        I would like to have result 10-Apr because ABC has 48 (highest Value)

        1. Hello!
          I believe the following formula will help you solve your task:

          =FILTER($D$3:$D$29,($B$3:$B$29=$G$6)*($C$3:$C$29=MAXIFS($C$3:$C$29,$B$3:$B$29,$G$6)))

          You can learn more about FILTER function in Excel in this article on our blog.

  12. Hello,

    Does anyone know how to apply this function with data that is not in a consecutive range?

    Instead of a range like B5:G5, my data is B5,D5,F5,

    Can anyone help?

    Thanks!

  13. Hi,
    I have a data sheet that I need to extract information from, in one column I have the NAMES of persons, in another I have either "yes" or "no" answers for each person.
    I'd like to extract everyone's name who answered "yes" into either one cell, separated by commas, or each into a vertical list.

  14. Hi, I am involved in stock trading and i have my trading records in Excel; ie bunch of profits and losses.

    My question is, how do i find the lowest and highest profit and loss that i make in my records?

    =MAX(IFERROR(P23:P79, "")) + CTRL + Shift + Enter only return highest profit, while
    =MIN(IFERROR(P23:P79, "")) + CTRL + Shift + Enter returns highest loss.

    How do i find lowest profit and lowest loss? Where can i fit the criteria "0" ?

    Do note that i have multiple blanks (intentionally left) and #DIV/0! (formula i keyed in in advance for future trading but i havent filled in respective fields)

    Thank you in advance. God bless and have a nice day.

  15. Hello, I'm trying to get the maximum error to show up but some times the maximum error is a negative (so -5 is a greater error than +3 etc) Any ideas?

    Also the 2 cells I'm comparing arent next to each other (just to add to the fun).

  16. hello
    Can I get help to write a formula that will highlight the lowest price for an article that repeats in several rows. If that article only shows once then that price would be highlighted too. for example only the first line would be highlighted.

    article 1 300
    article 1 548
    article 1 2500

    Thank you for your help

    1. Hello!
      I recommend using the FILTER function to find the article you are looking for. Find the minimum price using the MAXIFS function.

      =FILTER(A1:B8,(A1:A8="article 1")*(B1:B8=MINIFS(B1:B8,A1:A8,A1:A8)))

      I hope my advice will help you solve your task.

  17. I'm struggling with this a bit.
    My challenge is that I've got a list of results in a column, and want to identify the largest value in that column and then add a bonus prize in the next column for the largest row value.

    I would describe it BADLY as:
    in the column next to the data: +IF("the data in this cell is greater than the cells in rh column, give me A point" otherwise 0)
    Does that make sense?

    thanks

    1. Hello!
      In the next column, you can write the formula

      =IF(MAX($A$1:$A$30)=A1,100,"")

      After that you can copy this formula down along the column.
      Hope this is what you need.

  18. I am trying to find a formula that returns for example to find which State had the highest sales for a specific month. Column A is the State and Column B is the sales for the specific month. Does anyone know a formula for this? Thanks.

      1. Well if my range is - A2:D10 and I need to find the highest value with the name the person

      2. Thanks it worked.

  19. Hi, is it possible to create a formula to find the max value if some of the cells contain ranges please?
    i.e. working out that the max value is 505 from the following cells:
    Cell 1. 490
    Cell 2. 500,
    Cell 3. 490-505

  20. Dear Sir

    can you help me to do a formula to find highest value of 1st numbers letters

  21. This is excellent, thanks for saving me a ton of time!

  22. which one of the following functions is designed to display the maximum value in a range of cells with question answer

  23. THANK YOU!!! Do you know how complicated I have been making this for years?! And it was as simple as =Max!!!
    You have made my day!

  24. How to find max, 2nd, 3rd & 4th latest dates by name in another sheet?
    Results e;
    Names in column A
    Dates in column B
    Distance in column C
    & so on

    Regards
    Tony

  25. Well nd easy

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)