Closed Thread
Results 1 to 4 of 4

=IFERROR(INDEX... to match action items with due date

  1. #1
    Registered User
    Join Date
    01-05-2016
    Location
    D.C.
    MS-Off Ver
    Office 2016
    Posts
    29

    Question =IFERROR(INDEX... to match action items with due date

    Hello all - I'm hoping one of your brilliant mind can help me out...I am trying to use a formula: =IFERROR(INDEX(P:P,MATCH(H$3&”_”&ROWS($1:1),S:S,0)),””) to search a row of due dates, run them against a due date in another cell and, if there is a match, return the associated action line that is in another column. I can't seem to get the function to work. Essentially, I'd like to auto-populate a calendar with action items (in a table/list form) by pasting action items into their "date" box.

    Beyond this, if there is any possible way for the formula to capture both start and end dates (for those actions that span multiple days - ex. "develop first draft" vs one-off action items "strategy meeting") I'd love to populate both on the calendar and conditionally format to color the cells between the start/end dates.

    I've attached a simplified excel file to show what I'm trying to accomplish. Thank you SO MUCH, in advance!!

    Autopopulation of Action Items onto Calendar.xlsx

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

    Re: =IFERROR(INDEX... to match action items with due date

    First of all, I tried to shorten your date formula
    In B3 to return last Sunday of today:
    =INDEX(TODAY()-{6,5,4,3,2,1},MATCH(1,WEEKDAY(TODAY()-{6,5,4,3,2,1}),0))
    Next day:
    D3=B3+1
    Copy to F3,H3,...

    In A4 with formula for First Draft:
    =IF(A3<>"","",IF(B3=$Q$2,"First Draft to start",IF(B3=$R$2,"First Draft to end",IF(AND(B3>$Q$2,B3<$R$2),"First Draft",""))))

    In A5 with formula for Second Draft:
    =IF(A3<>"","",IF(B3=$Q$3,"Second Draft to start",IF(B3=$R$3,"Second Draft to end",IF(AND(B3>$Q$3,B3<$R$3),"Second Draft",""))))

    In A6 with formula for meeting:
    =IF(B3=$R$4,"Meeting to end","")

    In A7 with formula for Final Draft:
    =IF(A3<>"","",IF(B3=$Q$5,"Final Draft to start",IF(B3=$R$5,"Final Draft to end",IF(AND(B3>$Q$5,B3<$R$5),"Final Draft",""))))

    Copy A4:A7 to right
    Copy A4:N7 to A11
    Attached Files Attached Files
    Quang PT

  3. #3
    Registered User
    Join Date
    01-05-2016
    Location
    D.C.
    MS-Off Ver
    Office 2016
    Posts
    29

    Re: =IFERROR(INDEX... to match action items with due date

    This works great on the short-list in the excel file I attached, however how would you recommend building the formula so that it could search through a long list (~100 items) to find any that match that day (ex. B3) and also be smart enough to extend the action across multiple columns if the start date differs from the end date? Thank you!!

  4. #4
    Registered User
    Join Date
    01-05-2016
    Location
    D.C.
    MS-Off Ver
    Office 2016
    Posts
    29

    Red face Re: =IFERROR(INDEX... to match action items with due date

    Here's an expanded list in case you have any thoughts? I'd like the actions to auto-populate into their appropriate calendar dates and, ideally, if they cover a date range to have them repeat from start date to end date. THANK YOU!!
    Last edited by shellataylor; 01-22-2016 at 05:22 PM.

Closed Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] IFERROR and IFBLANK within INDEX and MATCH
    By russwongg in forum Excel General
    Replies: 2
    Last Post: 08-21-2015, 11:52 AM
  2. IFERROR INDEX/MATCH for multiple sheets
    By mehare in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-22-2015, 12:47 PM
  3. [SOLVED] Sum used with index match and iferror
    By tlstevens in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-08-2015, 02:43 PM
  4. [SOLVED] Iferror, index & match
    By meh999 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 07-26-2014, 08:43 AM
  5. Index, Match, Iferror and lots of problems
    By Sherp in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-10-2014, 12:00 AM
  6. Adding IFERROR to INDEX MATCH
    By brad999 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-06-2013, 04:18 AM
  7. Looking for better formula of iferror, index and match
    By DavidRoger in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-23-2012, 07:50 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