+ Reply to Thread
Results 1 to 9 of 9

Index / Match / Large / If help

  1. #1
    Registered User
    Join Date
    02-12-2018
    Location
    Melbourne, Australia
    MS-Off Ver
    2016
    Posts
    5

    Question Index / Match / Large / If help

    Hi all,

    I am trying to return the name of the top 5 salespeople for a given store. I have a working formula if all their sales came through on one line, however, they come through on multiple lines. So essentially, I want to sum the sales for each person, then rank them based on their total sales and group by location.

    This formula returns a name, but only based on 1 line of sales - not the sum of all their sales.

    {=INDEX($B2:$B21,MATCH(LARGE(IF($C$2:$C$21=$G$1,$D$2:$D$21),1),$D$2:$D$21,0))}
    Attached Files Attached Files

  2. #2
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,628

    Re: Index / Match / Large / If help

    You must use the Pivot Table to visualize the desired output.
    Convert your data into an Excel Table by selecting any cell in the data range and press Ctrl+T and hit OK to format the data range as an Excel Table.
    Now insert a Pivot Table as shown in the attached and it will show you the top 5 salesmen for each location.

    Once you add more data down the rows, all you need is, right click in any cell in the Pivot Table and choose Refresh and it will update the Pivot Table with latest info.
    Attached Files Attached Files
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  3. #3
    Registered User
    Join Date
    02-12-2018
    Location
    Melbourne, Australia
    MS-Off Ver
    2016
    Posts
    5

    Re: Index / Match / Large / If help

    Thanks for the response Sktneer. I'll get working on it that way. Shame there isn't a way to get a formula to work!

  4. #4
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,628

    Re: Index / Match / Large / If help

    Post deleted as better formula provided by FlameRetired.
    Last edited by sktneer; 02-16-2018 at 02:17 AM.

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,220

    Re: Index / Match / Large / If help

    If I understand correctly and you still would like to see a formula solution ... this is rather complicated and resource heavy.

    To return the ranking totals this in I3 and filled down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Copy that range and paste into I10 and I17. You will need to change 3 references to the cities in I1 to I8 and I15 for the other sections.

    To get the names this in H3 and fill down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Copy/paste and edit as before.


    A
    B
    C
    D
    E
    F
    G
    H
    I
    1
    Date
    Salesperson
    Location
    Sales
    Richmond
    2
    1-Feb
    Jacky
    Richmond
    $43.00
    Rank
    Name
    Total
    3
    1-Feb
    Bob
    South Melbourne
    $58.00
    1
    Harry
    $209.00
    4
    1-Feb
    Harry
    Richmond
    $66.00
    2
    Jacky
    $154.00
    5
    1-Feb
    Steve
    South Melbourne
    $22.00
    3
    Daniel
    $98.00
    6
    1-Feb
    Jenny
    Northcote
    $19.00
    4
    Ben
    $66.00
    7
    2-Feb
    Jacky
    Richmond
    $89.00
    5
    8
    2-Feb
    Bob
    South Melbourne
    $20.00
    South Melbourne
    9
    2-Feb
    Harry
    Richmond
    $44.00
    Rank
    Name
    Total
    10
    2-Feb
    Steve
    South Melbourne
    $80.00
    1
    Bob
    $171.00
    11
    2-Feb
    Jenny
    Northcote
    $66.00
    2
    Steve
    $135.00
    12
    3-Feb
    Jacky
    Richmond
    $22.00
    3
    Jacob
    $76.00
    13
    3-Feb
    Bob
    South Melbourne
    $93.00
    4
    Kim
    $45.00
    14
    3-Feb
    Harry
    Richmond
    $99.00
    5
    15
    3-Feb
    Steve
    South Melbourne
    $33.00
    Northcote
    16
    3-Feb
    Jenny
    Northcote
    $54.00
    Rank
    Name
    Total
    17
    3-Feb
    Daniel
    Richmond
    $98.00
    1
    Frida
    $160.00
    18
    3-Feb
    Jacob
    South Melbourne
    $76.00
    2
    Jenny
    $139.00
    19
    3-Feb
    Ben
    Richmond
    $66.00
    3
    20
    3-Feb
    Kim
    South Melbourne
    $45.00
    4
    21
    3-Feb
    Frida
    Northcote
    $160.00
    5
    Dave

  6. #6
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,628

    Re: Index / Match / Large / If help

    Good one Dave!

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,220

    Re: Index / Match / Large / If help

    Thank you sktneer, but I have to agree the pivot table is still the best solution.

  8. #8
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,628

    Re: Index / Match / Large / If help

    Yeah, that depends upon personal preference. That's why I offered a solution based on Pivot Table.

  9. #9
    Registered User
    Join Date
    02-12-2018
    Location
    Melbourne, Australia
    MS-Off Ver
    2016
    Posts
    5

    Re: Index / Match / Large / If help

    Thank you very much, both of you!

    I do have my reasons for wanting to stick to formulas. I have been asked to make one table that shows a huge range of information from 4 different brand stores, then 10 different locations. Things like sales/customers/costs/top 1/top 2 (73 columns all up) all at-a-glance. If I were to use a pivot table, it would be an additional dataset to manage, where my goal is to make what I already have all into one.

+ 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. [SOLVED] Large Data - Index Match Match with another function?
    By d7882 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 10-12-2017, 03:51 PM
  2. Index Large Match
    By normanbox in forum Excel General
    Replies: 3
    Last Post: 04-11-2017, 04:18 PM
  3. [SOLVED] Formula - Index / Match / Large
    By jsneak in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-10-2015, 07:46 AM
  4. [SOLVED] Large,Index, Match with duplicates
    By x65140 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-10-2014, 10:45 AM
  5. INDEX, MATCH and LARGE
    By ridebikes in forum Excel General
    Replies: 1
    Last Post: 10-24-2011, 04:03 PM
  6. Index, Match, and Large
    By edwardpestian in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-03-2006, 06:10 PM
  7. Large Index Match Lookup
    By Qaspec in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03: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