+ Reply to Thread
Results 1 to 4 of 4

INDEX, MATCH, MAX Formula

  1. #1
    Registered User
    Join Date
    03-15-2009
    Location
    DC, DC
    MS-Off Ver
    Excel 2010
    Posts
    15

    INDEX, MATCH, MAX Formula

    There are over 600+ customer ID and 400+ Unique IDs that is based on the customer. (Sheet 1)

    I am trying to analyze the expense incurred to the dues collected.
    1. Customer ID is the same but could change for some factors
    2. Unique ID is based on the customers themselves so like 2545 when the person went from individual to entity it stayed the same and when the customer number changed (Unique ID 21) when they cancelled membership and resigned and customer ID Changed.


    What I want to achive in SHEET 2

    Cloumn A 1. Reference the unique ID sheet 2 B2 to sheet one and return the customer ID with Max Annual Pay
    Column B- Will be filled in with the Unique ID (no formula needed)
    Column C- To return the name from sheet 1 associate with the highest annual pay
    Column F To return the most recent date of membership contract based on the Unique ID

    I have filled in what the valued should be in sheet 2. I was using the formula below also the formula in Clolumn G
    =INDEX(Sheet1!F:F,MATCH(Sheet2!B2,Sheet1!B:B),0)
    But it does not work unless the data is sorted the right way which is hard to do because there are a lot of records.

    I would appreciate any help!
    Attached Files Attached Files

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,127

    Re: INDEX, MATCH, MAX Formula

    hi macki60. try this array formula in A2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    ...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. Press F2 on that cell and try again. copy downwards & to column C too.

    a slight change for F2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: INDEX, MATCH, MAX Formula

    Hi macki60,

    Please see attached file
    Attached Files Attached Files
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  4. #4
    Registered User
    Join Date
    03-15-2009
    Location
    DC, DC
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: INDEX, MATCH, MAX Formula

    Thank you so much!

+ 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: 3
    Last Post: 05-02-2013, 01:31 AM
  2. Index Match Index Formula work slow
    By avk in forum Excel General
    Replies: 9
    Last Post: 03-07-2012, 02:19 PM
  3. Replies: 5
    Last Post: 02-29-2012, 08:51 PM
  4. Nested Match(index()) vs. Match() array formula.
    By GeneralDisarray in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-14-2011, 07:57 AM
  5. Adding a third match to an Index Match Formula
    By Weasel in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-07-2009, 01:51 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