+ Reply to Thread
Results 1 to 6 of 6

return next item in list using index match

  1. #1
    Forum Contributor
    Join Date
    04-04-2016
    Location
    dallas tx
    MS-Off Ver
    365 (15.0.5501.1000)
    Posts
    534

    return next item in list using index match

    I have a list of employees that I want to separate by supervisor name and whether they are active or terminated. The best way to do this is to use a two value index match formula witch I am doing but with some difficulty. It is just returning the first person on the list repeatedly instead of showing the next individual, then the next, then the next and so on. How do tell the formula to return the next item down on the list until complete?

    In my example I am populating the test tab with the information on the data tab beginning in cell A7 on the text tab.
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: return next item in list using index match

    Why don't you use AutoFilter instead of making separate lists?
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Contributor
    Join Date
    04-04-2016
    Location
    dallas tx
    MS-Off Ver
    365 (15.0.5501.1000)
    Posts
    534

    Re: return next item in list using index match

    Frankly, the powers that be don't want to use filter. They want to look at a Dashboard that has each supervisor and shift listed separately. Not only that, but they want that said dashboard placed in multiple reports so it would mean having to go and update each list on each report each time a new person is hired or terminated. By having a central list that separates the data accordingly and having all the reports saved in the same folder on the share drive the HR department just needs to update the master list when necessary and it will take less time.

    In other words, they want to add more cogs to the clock than what is needed because they are stupid. So I have to try and make it work.

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: return next item in list using index match

    How about in A7 dragged down
    =IFERROR(INDEX('Total Headcount & Start Date'!$B$2:$B$183,AGGREGATE(15,6,(ROW('Total Headcount & Start Date'!$B$2:$B$183)-ROW('Total Headcount & Start Date'!$B$2)+1)/('Total Headcount & Start Date'!$G$2:$G$183=$A$1)/('Total Headcount & Start Date'!$H$2:$H$183=$C$1),ROWS(A$7:A7))),"")

  5. #5
    Forum Contributor
    Join Date
    04-04-2016
    Location
    dallas tx
    MS-Off Ver
    365 (15.0.5501.1000)
    Posts
    534

    Re: return next item in list using index match

    Thanks you very Much!! I have to study the Aggregate function in more detail.

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: return next item in list using index match

    You're welcome & thanks for the feedback.

+ 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] Index Match with Criteria - return list
    By UHD in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-30-2019, 12:23 PM
  2. Need VBA to auto select first item in drop down list which uses Index Match
    By jimmy.crowe in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-31-2018, 08:57 PM
  3. [SOLVED] Last item in a list with index and match
    By comatose1978 in forum Excel General
    Replies: 18
    Last Post: 08-13-2018, 07:19 PM
  4. Using Index Match to consolidate a list and exclude an item
    By StuartAllenNZ in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-17-2018, 11:31 PM
  5. Return search item/s from array or list in a cell (exact match)
    By adm.patrikbpl in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-16-2017, 09:22 AM
  6. [SOLVED] Match index value return all the corresponding line item
    By silambarasan.J in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-20-2016, 09:27 AM
  7. Index/Match Return is an Error but Only at the Top of List
    By kate isgreat in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-20-2015, 06:12 PM

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