Results 1 to 9 of 9

Excel Index and Match function with multiple criteria not searching the next record

Threaded View

  1. #1
    Registered User
    Join Date
    06-16-2017
    Location
    canada
    MS-Off Ver
    office 2010
    Posts
    3

    Excel Index and Match function with multiple criteria not searching the next record

    Safety Stats Image.jpg
    I have a spreadsheet wherein I need to find data based on multiple search criteria. I have used the Index with Match function to find the first record in the spreadsheet however I am unable to get to the next record in the data set.

    The file name is "Safety Stats" and the data is in the [Occupational Injuries] tab. I need to find all records where the month is pervious month, the "Employee/Contractor" column is "Employee" and the "Injury Type" column is not "First Aid" or "Reported Only". I want excel to pull data based on my criteria from this spreadsheet into a different excel report every month.

    My function is locating the first occurrence however I am unable to find the next record in the data set. Any help will be highly appreciated.

    Here is the function I have written.
    [CODE][CODE][CODE]{=IF(
    ISNA(
    INDEX(
    '[Safety Stats.xlsx]Occupational Injuries'!$C$5:$C$128,
    MATCH(1,
    ('[Safety Stats.xlsx]Occupational Injuries'!$A$5:$A$128=TEXT(EOMONTH(TODAY(),-1),"MMMM")) *
    ('[Safety Stats.xlsx]Occupational Injuries'!$F$5:$F$128="Employee") *
    ('[Safety Stats.xlsx]Occupational Injuries'!$I$5:$I$128<>"First Aid") *
    ('[Safety Stats.xlsx]Occupational Injuries'!$I$5:$I$128<>"Reported Only"),0))),"",
    INDEX('[Safety Stats.xlsx]Occupational Injuries'!$C$5:$C$128,
    MATCH(1,
    ('[Safety Stats.xlsx]Occupational Injuries'!$A$5:$A$128=$Y$9) *
    ('[Safety Stats.xlsx]Occupational Injuries'!$F$5:$F$128="Employee") *
    ('[Safety Stats.xlsx]Occupational Injuries'!$I$5:$I$128<>"First Aid") *
    ('[Safety Stats.xlsx]Occupational Injuries'!$I$5:$I$128<>"Reported Only"),0)))}

    Below is the DATA:

    Month Day Full Name Years of Experience Occupation Employee /Contractor Location Department Injury Type
    May 05 Bery Owen 5 Welder Employees 2. Maintenance Maint Shop Medical Treatment Injury
    May 09 Samyy Wood 3 Services Contractor 2. Maintenance Services First Aid
    May 11 Sunny Man 4 Operator Employees 1. Production Operation Medical Treatment Injury
    May 15 Rick Richard 1 Operator Employees 4. Others Plant Lost Time Injury
    May 19 Patty Westwood 6 Maint Employees 4. Others Machinery First Aid
    May 21 Tim Bear 3 Maint Contractor 4. Others Services Reported Only
    May 24 Angela Petterson 5 Mechanic Employees 1. Production Operation First Aid
    May 25 Martin Butin 4 Tradesman Employees 1. Production Operation Restricted Work Injury
    May 29 Roy Mathew 6 Rigger Employees 1. Production Operationy First Aid
    Attached Files Attached Files
    Last edited by mushtaqkadar; 06-16-2017 at 03:15 PM. Reason: Image file not opening

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need help in Index, Match usage to match multiple criteria in sum function
    By Summer0830 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-26-2017, 02:47 AM
  2. Searching for 2 criteria - trying Index/Match with no luck
    By notsamsnead in forum Excel General
    Replies: 6
    Last Post: 11-11-2015, 12:28 AM
  3. [SOLVED] Index-Match function with multiple criteria
    By MikeSta4ord in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-05-2015, 12:51 PM
  4. How do you drag down an index match function with multiple criteria?
    By larryg003 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-28-2014, 01:26 PM
  5. Using MATCH and INDEX function with multiple criteria
    By Babylon in forum Excel Programming / VBA / Macros
    Replies: 52
    Last Post: 02-06-2014, 11:28 AM
  6. VLookup or Index/match searching rows instead of columns or multiple criteria
    By Groovicles in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-16-2013, 05:00 PM
  7. Index and match function multiple criteria
    By mfortier3 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-16-2013, 05:27 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