+ Reply to Thread
Results 1 to 6 of 6

finding a data point in an array, and the labels

  1. #1
    Registered User
    Join Date
    08-09-2010
    Location
    Boston, MA
    MS-Off Ver
    Excel 2003
    Posts
    6

    finding a data point in an array, and the labels

    Hello,

    I have an array of data that shows scores for a game. Colum A has the names of the players and row one has the weeks. I am already using a function that shows the Min and Max values in the array using a simple =MIN(B2:H16). What I am trying to do is get the X and the Y values for the returned score. So rather then just getting the Min. value I will get the Min value, the week it occurred and player got the score.

    Thanks.

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,064

    Re: finding a data point in an array, and the labels

    Use INDEX...MATCH to locate the MIN value, assuming the MIN value is unique of course!
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    08-09-2010
    Location
    Boston, MA
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: finding a data point in an array, and the labels

    Thank you.

    What happens if the value is not unique?

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: finding a data point in an array, and the labels

    Simplest solution in these situations is invariably to use a Pivot Table - the Pivot will happily handle duplicity without need for formulae
    (you can configure the PT to filter based on Bottom n values etc and order appropriately)

    If you want further assistance I would ask you post a sample file which reflects both set up and desired output for the data provided - this will help members when it comes to providing a tailored solution for you.

  5. #5
    Registered User
    Join Date
    08-09-2010
    Location
    Boston, MA
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: finding a data point in an array, and the labels

    Now that I have had a chance to try using the Index/Match function I don't think I explained what I am going for very well. Essentially I want the opposite of what the Index/Match gives me. Of course I could be doing it wrong.

    Using the Index/Match combo I can write something that answers "what score did Doug get in week 6?", but what I want is the reverse - "Who and on what week scored 13 points?"

    I threw together a basic example of what I am trying to do on the attached spreadsheet.

    Thank you.
    Attached Files Attached Files

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: finding a data point in an array, and the labels

    One approach

    Please Login or Register  to view this content.

+ 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