+ Reply to Thread
Results 1 to 12 of 12

How to find out the largest value in a range with index match and large funtion

  1. #1
    Registered User
    Join Date
    12-29-2019
    Location
    Bahrain
    MS-Off Ver
    Office 365
    Posts
    10

    How to find out the largest value in a range with index match and large funtion

    Hi Experts!

    Looking for a help from you guys.
    I am working on Office 365.

    I have a table with a value for different months for different projects, and the projects has 3 types.
    My selection criteria is "Month" and "type of Project" which are dynamic.
    Once the selection is done, the result need is the highest values of the project for the selected month.
    Since my end result will be in other sheet, I need to show the corresponding project which is match to the resulted value with.

    I have tried many ways with Index-match-large function, but could not completed.
    I am not particular with Index-match-large formula. I am ok with any formula which will make the end result looking for.

    I have attached a sample file, and shown the required result needed (Done manually).
    In the attached file, yellow cells are my selecting criteria and blue cells are the ones I would get the result in by formula.


    Thanks in Advance
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription Insider (Win 10 - Home)
    Posts
    34,123

    Re: How to find out the largest value in a range with index match and large funtion

    In J6:

    =IFERROR(LARGE(IF($C$2:$C$23=$J$2,IF($D$1:$F$1=$J$1,$D$2:$F$23)),$I6),"")

    ... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Once entered, drag copy down.

    The rest can be done with simple INDEX MATCH (unless you could have duplicate numbers, in which case please advise).
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    12-29-2019
    Location
    Bahrain
    MS-Off Ver
    Office 365
    Posts
    10

    Re: How to find out the largest value in a range with index match and large funtion

    Hi Ali,


    Thank you very Much.
    It is awesome.

    To get the project name and location, as you mentioned my values will have duplicates sometimes. As well as my Project names also repeating/duplicating as certain project will have in multiple locations.
    What could be the best solution in this case.
    It would be highly appreciated your help on this.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription Insider (Win 10 - Home)
    Posts
    34,123

    Re: How to find out the largest value in a range with index match and large funtion

    This complicates matters. Please provide some sample data where this happens and show the results as you wish them to appear. I'm not going to guess!

  5. #5
    Registered User
    Join Date
    12-29-2019
    Location
    Bahrain
    MS-Off Ver
    Office 365
    Posts
    10

    Re: How to find out the largest value in a range with index match and large funtion

    Hi Ali,


    I am attaching the sample data where the values and projects are repeating certain cases.
    I have put the Project name and location manually in which the way I would like them to appear.

    Thanks
    Attached Files Attached Files

  6. #6
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,151

    Re: How to find out the largest value in a range with index match and large funtion

    I'd take a different approach.

    Normalise your source data using Power Query (Get & Transform Data): format your data as a table (SourceData) and Load To Data Model:

    Please Login or Register  to view this content.

    Add the following measure to the data model:

    Please Login or Register  to view this content.

    Now you can use a pivot table to report, with Month and Type as filters, Value, Projects and Location in Rows, Project Rank as Values. Sort / Filter Values by Project Rank, to suit. See attached for worked example.
    Attached Files Attached Files
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  7. #7
    Registered User
    Join Date
    12-29-2019
    Location
    Bahrain
    MS-Off Ver
    Office 365
    Posts
    10

    Re: How to find out the largest value in a range with index match and large funtion

    Hi Olly,


    Thanks for the support. But when I given the same value of the 1st position to the 2nd position also ( changed the value in the source data) it is showing some error. I refreshed it too.
    I did this to check out how this will work in case any values has a repetition.

  8. #8
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,151

    Re: How to find out the largest value in a range with index match and large funtion

    What sort of error?

  9. #9
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,151

    Re: How to find out the largest value in a range with index match and large funtion

    Here's an alternative way, using two measures, which removes the need to filter the pivot table:

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Now you can simply sort the pivot table by Value descending.

    Both options handle duplicate Values with no problem.

    Capture.PNG
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    12-29-2019
    Location
    Bahrain
    MS-Off Ver
    Office 365
    Posts
    10

    Re: How to find out the largest value in a range with index match and large funtion

    Thanks Olly,


    We are almost reached. But few points to fine tune.
    When more rows has the same value, the end result also increasing by that many line items. (Please find the attached screen shot)
    I need only the 3 values in the final result.
    Like values
    A
    B
    C
    In case the value A is repeating then it would be like

    A
    A
    A.

    So is there any way it can be restricted with 3 values.
    Thanks in advance

    Attachment 656085

  11. #11
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,151

    Re: How to find out the largest value in a range with index match and large funtion

    That attachment doesn't work, for me.

    When multiple rows have the same value, what is your expected output? I understand you want the top three distinct values - but which Project / Location values do you want to display?

  12. #12
    Registered User
    Join Date
    12-29-2019
    Location
    Bahrain
    MS-Off Ver
    Office 365
    Posts
    10

    Re: How to find out the largest value in a range with index match and large funtion

    Hi Olly,

    A you told, I need to get it displayed the top three distinct values alphabetically.
    If one project has same value for different location, then Let is display like;
    Project-1-Location-1
    Project-1-Location-2

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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