+ Reply to Thread
Results 1 to 7 of 7

Using INDEX and MATCH but omitting some results

  1. #1
    Registered User
    Join Date
    09-28-2020
    Location
    Canada
    MS-Off Ver
    Office for Mac 16.14
    Posts
    4

    Using INDEX and MATCH but omitting some results

    I have a workbook with two sheets (work_load and Assign) that I use to track assigned task for a particular house address. Each address that the workers work at has 3 tasks (Sign, Path & Drop), the tasks can all be assigned to 1 person or 3 different people. Shown below
    Screen Shot 2020-09-28 at 3.54.05 PM.png

    On my sheet named "Assign", I assign the tasks to the various workers and it's updated on the workload sheet. As shown below
    Screen Shot 2020-09-28 at 3.57.56 PM.png

    Each worker has an excel sheet which I update with all assigned tasks manually everyday. I want to have a dynamic formula that will fetch all assigned work to each worker only for tasks they are responsible for. As below in the case of John
    Screen Shot 2020-09-28 at 4.02.14 PM.png

    I am able to generate the report for each worker however, it also displays the other workers responsible for the other tasks associated with a particular address.
    this is the formula I used to achieve this:

    =IFERROR(INDEX(Work_load!$A$2:$J$20, SMALL(IF(1=((--(Assign!$A$2:$A$20 = Work_load!$F$2:$F$20))) * (--("John"=Work_load!$H$2:$J$20)), ROW(Work_load!$A$2:$K$20)-1,""), ROW()-1),{1,2,3,4,5,6,8,9,10}),"")

    Screen Shot 2020-09-28 at 5.57.25 PM.png

    Where I am finding a challenge is returning results for workers indicating only tasks they are responsible for. Any help and suggestions are will be very much appreciated. Attached is my file I'm using
    Attached Files Attached Files
    Last edited by DrPopo; 09-28-2020 at 08:09 PM.

  2. #2
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Using INDEX and MATCH but omitting some results

    For my own convenience, I'd name the range Work_load!A1:J20 Base.

    I'd insert 2 rows at the top. Put For: in cell A1, and a drop-down list for all available workers in B1.

    C1: =COUNTIF(INDEX(Base,0,8):INDEX(Base,0,10),$B$1)

    Give C1 the number format ;;; . This would put column labels in row 3.

    K4: =IF($C$1>COUNTIF(H$3:J3,$B$1),SMALL((MMULT(--(INDEX(Base,0,8):INDEX(Base,0,10)=$B$1),{1;1;1})=0)*1E+300+(ROW(Base)-CELL("Row",Base)+1),ROWS(K$4:K4)),-1)

    Enter K4 as an array formula.

    A4: =IF($K4>0,INDEX(Base,$K4,COLUMNS($A4:A4)),"")

    Fill A4 right into B4:G4.

    H4: =IF($K4>0,IFERROR(VLOOKUP(INDEX(Base,$K4,COLUMNS($A4:H4)),$B$1,1,0),""),"")

    Fill H4 right into I4:J4. Select A4:J4, and add conditional formatting, use a formula condition, namely, =$K4>0, and change the formatting to use a border completely around the cell. Give K4 the number format ;;; in order to hide it. Select A4:K4 and fill down, say into A5:K40.

    As you change the name in cell B1, the table in A3:H40 will change.

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Using INDEX and MATCH but omitting some results

    Try in A2:

    Please Login or Register  to view this content.
    Drag down andd accross
    Attached Files Attached Files
    Quang PT

  4. #4
    Registered User
    Join Date
    09-28-2020
    Location
    Canada
    MS-Off Ver
    Office for Mac 16.14
    Posts
    4

    Re: Using INDEX and MATCH but omitting some results

    Excellent! This worked perfectly. Thank you #hringrv

    I have a followup question though, I would like get this report to look in multiple work_load sheets down the road as I get assigned these work_load sheets (eg work_lod1, work_load2 etc..). Going back to edit all the formulas with new work_load sheets might be a pain so I might have to create a list of sheets and add on to that as I create more sheets. I will maintain the same Assign sheet for all my assignments and I will have to have to get the formula tweaked to fetch data from multiple work_load sheets for the report.

    Seems like a little challenge for this evening but I would like to try it out first and if I can't make headway I will came back for some help. Maybe a point in the right direction will also help.

    Thanks again, everyday I learn something new.

  5. #5
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Using INDEX and MATCH but omitting some results

    Spreadsheets usually handle multiple source ranges awkwardly at best. Better to consolidate all separate Work_Load# tables into a single consolidated table.

  6. #6
    Registered User
    Join Date
    09-28-2020
    Location
    Canada
    MS-Off Ver
    Office for Mac 16.14
    Posts
    4

    Re: Using INDEX and MATCH but omitting some results

    oh I see, thanks for the heads up. I will try to get all the data onto one sheet then.

  7. #7
    Registered User
    Join Date
    09-28-2020
    Location
    Canada
    MS-Off Ver
    Office for Mac 16.14
    Posts
    4

    Re: Using INDEX and MATCH but omitting some results

    #hringrv
    So I tried to get all the data on one sheet however, it starts to become cumbersome as I receive these files from our main contractors via smart sheets. I normally export them from smart sheet and import them into excel onto different sheets. For now I will have to manage with multiple work_load sheets until I can figure out a work around.

    Your formula is proving a bit challenging to modify so I have attached my new file with my modifications. Is it possible to take a look and see if I can achieve the report spooled from different sheets. Thanks in advance.
    Attached Files Attached Files

+ 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 giving correct results, wrong results & #N/A results...sometimes
    By mrteater in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-27-2019, 10:41 AM
  2. Need Help with Index, Match, Match with multiple results and copying data x times
    By jrboyd in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 05-27-2019, 06:57 AM
  3. Trying to Index match from the results of a previous index match
    By vitt4300 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-09-2017, 09:12 AM
  4. [SOLVED] Index / Match - match 3 input values and return the results from the index
    By t83357 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-08-2016, 07:34 PM
  5. [SOLVED] Help with Index, Match, Match formula results
    By kershaw in forum Excel General
    Replies: 9
    Last Post: 06-26-2015, 09:15 AM
  6. [SOLVED] Not getting expected results from index match match
    By number1mm in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-25-2013, 03:56 PM
  7. [SOLVED] Using Index & Match always results in #N/A
    By ioswoody in forum Excel General
    Replies: 3
    Last Post: 07-05-2012, 01:52 AM

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