+ Reply to Thread
Results 1 to 7 of 7

Index & Match with Multiple criteria

  1. 11-09-2014, 09:05 PM #1
    dfaidley
    dfaidley is offline
    Registered User
    Join Date
    09-27-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    2

    Index & Match with Multiple criteria

    i'm trying to use index and match to create an employee schedule. There are two main worksheets, the actual schedule which will employee name in rows and dates in columns and show a 4 week time period.

    The second worksheet is a manual input table of employee name, requested date and type of request. I thought I could use index and match to auto populate the employee schedule with the appropriate time off type 'Sick, X, PTO' when it's been approved on this second 'vacation request' table.

    I've attached a small sample, the biggest issue I'm struggling with is index and match. Any help or suggestions is appreciated!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 11-09-2014, 09:27 PM #2
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Index & Match with Multiple criteria

    Try this in B2, then copied down and across the table:

    =IFERROR(INDEX('Days off'!$C$1:$C$100,MATCH($A2&B$1,INDEX('Days off'!$A$1:$A$100&'Days off'!$B$1:$B$100,0), 0)), "")
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  3. 11-09-2014, 09:33 PM #3
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Index & Match with Multiple criteria

    Now, this expanded version is really just the same formula above run TWICE, once to check and make sure there is a data in the column D for the matched row, then a second time to pull back the column C "type" of day off.

    =IFERROR(IF(ISNUMBER(INDEX('Days off'!$D$1:$D$10,MATCH($A2&B$1,INDEX('Days off'!$A$1:$A$10&'Days off'!$B$1:$B$10,0), 0))),
    INDEX('Days off'!$C$1:$C$10,MATCH($A2&B$1,INDEX('Days off'!$A$1:$A$10&'Days off'!$B$1:$B$10,0), 0)), ""), "")
    Reply With QuoteRegister To Reply
  4. 11-09-2014, 09:48 PM #4
    dfaidley
    dfaidley is offline
    Registered User
    Join Date
    09-27-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Index & Match with Multiple criteria

    awesome, this is exactly what I needed! thank you
    Reply With QuoteRegister To Reply
  5. 11-09-2014, 09:54 PM #5
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,972

    Re: Index & Match with Multiple criteria

    Jerry, have you checked to see what the highest building was, that you leapt in a single bound?

    awesome formula
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  6. 11-09-2014, 10:55 PM #6
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2405
    Posts
    13,432

    Re: Index & Match with Multiple criteria

    Quote Originally Posted by FDibbins View Post
    Jerry, have you checked to see what the highest building was, that you leapt in a single bound?

    awesome formula
    My thoughts exactly; sent wordless rep 'cause I'm speechless.
    Reply With QuoteRegister To Reply
  7. 11-10-2014, 12:53 PM #7
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Index & Match with Multiple criteria

    Aww shucks.... (kicks a loose stone)
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Formula needed for partial string match as part of index match with multiple criteria
    By Marvelous in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-27-2014, 04:34 PM
  2. [SOLVED] Index Match using multiple criteria to match to
    By sacastiglia in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-25-2014, 03:46 PM
  3. Index/Match with multiple criteria
    By ccwynar in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-27-2014, 06:25 PM
  4. Index/Match Multiple Criteria
    By ertweety in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-07-2013, 11:42 AM
  5. Multiple Criteria INDEX MATCH MAX
    By sweep in forum Excel General
    Replies: 4
    Last Post: 04-25-2007, 08:08 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1