+ Reply to Thread
Results 1 to 19 of 19

Return Multiple values from a column with index and match, and search criteria

  1. #1
    Registered User
    Join Date
    01-19-2015
    Location
    Warrington, PA
    MS-Off Ver
    2013
    Posts
    41

    Return Multiple values from a column with index and match, and search criteria

    Here is my delima
    I have a table Table 4 on Sheet Pull Data

    What I'm trying to get is a formula that returns the names for day for the individuals that work on that Day & Shift.

    So for sunday it would return only Name4, Name7, Name13. All of these cells within my table have data validation set on them.
    Attached Files Attached Files
    Last edited by marcusduton; 02-07-2015 at 02:26 AM.

  2. #2
    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,917

    Re: Return Multiple values from a column with index and match, and search criteria

    HI, welcome to the forum

    Often, a copy/paste to here doesnt quite work out - and this is kinda 1 of those times.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    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

  3. #3
    Registered User
    Join Date
    01-19-2015
    Location
    Warrington, PA
    MS-Off Ver
    2013
    Posts
    41

    Re: Return Multiple values from a column with index and match, and search criteria

    Data for Original Post, Located on Pull Data Tab
    Attached Files Attached Files

  4. #4
    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,917

    Re: Return Multiple values from a column with index and match, and search criteria

    THanks for the file

    Now, where exactly is the data you want extract, and where do you want it put?

  5. #5
    Registered User
    Join Date
    01-19-2015
    Location
    Warrington, PA
    MS-Off Ver
    2013
    Posts
    41

    Re: Return Multiple values from a column with index and match, and search criteria

    The Data is in "Table4", the extracted Data I want to put into cell Z9:Z21 on Tab <Jan>
    Last edited by marcusduton; 02-07-2015 at 02:44 AM.

  6. #6
    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,917

    Re: Return Multiple values from a column with index and match, and search criteria

    See if this ARRAY formula is kind of what you want? If have not set it up for different days yet, wanted to make sure it's what you wanted...
    =IFERROR(INDEX(Table4[Name],SMALL(IF((Table4[Shift]="Day")*(Table4[Sunday]<>""),ROW('Pull Data'!$A$3:$A$33)-2),ROWS('Pull Data'!$A$1:A1))),"")
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

  7. #7
    Registered User
    Join Date
    01-19-2015
    Location
    Warrington, PA
    MS-Off Ver
    2013
    Posts
    41

    Re: Return Multiple values from a column with index and match, and search criteria

    Thank you very much that did exactly what I was looking for.

  8. #8
    Registered User
    Join Date
    01-19-2015
    Location
    Warrington, PA
    MS-Off Ver
    2013
    Posts
    41

    Re: Return Multiple values from a column with index and match, and search criteria

    Hello I'm back don't know how I could do this. But need to have this formula fit into or check and not enter data based on date. I have uploaded a new file for an example. You see the date ranges on the Schedule Tab. and needs to fit into the formula that you have in the above post. See for the example of where it would go being 1/1/15 on the worksheet named Jan-Test I'm looking for the formula to only fill in between the date ranges and if no date range for end date to continue.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    01-19-2015
    Location
    Warrington, PA
    MS-Off Ver
    2013
    Posts
    41

    Re: Return Multiple values from a column with index and match, and search criteria

    Please see my latest post above. Hopefully I'm being clear.

  10. #10
    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,917

    Re: Return Multiple values from a column with index and match, and search criteria

    On ja-Test, where is the date you need to test against?

  11. #11
    Registered User
    Join Date
    01-19-2015
    Location
    Warrington, PA
    MS-Off Ver
    2013
    Posts
    41

    Re: Return Multiple values from a column with index and match, and search criteria

    Let's go with V7, the formula is actually going to test across all days eventually.

  12. #12
    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,917

    Re: Return Multiple values from a column with index and match, and search criteria

    OK so you want the date in V7 to test against the days/names in Schedule?
    So only info for Wed-Sun would show?

    Would it be a problem to include the dates in the blank spaces, and then to add a helper column on the Schedule sheet?

  13. #13
    Registered User
    Join Date
    01-19-2015
    Location
    Warrington, PA
    MS-Off Ver
    2013
    Posts
    41

    Re: Return Multiple values from a column with index and match, and search criteria

    What I'm looking to do is to test V7 {What ever date it is} to test against start and end date and only display the names for that day which is Thursday, but only if those names were within after the begin date and before the end date, and if no end date to continue like to display those names until an end date comes about and then drop that name.
    It's basically a schedule. but don't want to display the names of people that are on the schedule before their hire date and not to display them on the schedule after their end date.
    Hopefully I'm not confusing you.
    Example Joe Hired on 1/9/15(Start Date), I don't want Joe's name to display on the schedule until 1/9/15, and if Joe is Terminated on 2/15/15(End Date), I don't want him to show on the rest of the calendar after that End Date.

  14. #14
    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,917

    Re: Return Multiple values from a column with index and match, and search criteria

    I am out of time right now, I will take a look again as soon as I can

  15. #15
    Registered User
    Join Date
    01-19-2015
    Location
    Warrington, PA
    MS-Off Ver
    2013
    Posts
    41

    Re: Return Multiple values from a column with index and match, and search criteria

    That's fine, this is what I have come up with, but have 2 issues. =IF(V$7<>"",IFERROR(INDEX(Table4[Name],SMALL(IF((Table4[Shift]="Day")*(Table4[Thursday]<>"")*(V$7>Table4[Start Date])*(V$7<=Table4[Term Date]),ROW('Schedule Data'!$F$3:$F$33)-2),ROWS('Schedule Data'!$D$1:D1))),""),"").

    If no Entry in start Date I want the if statement to go false, but if end date is blank i don't want the if statement to false. To get this to work I have to Place "" in the area's in which I want the logic to work as I planned.

    For Example if Name one hasn't started I have to place "" in that cell, but if Name one has a start date, I have to put"" in the term date to keep it from ripping that person off the schedule prematurely.

    Correction to the formula.=IF(V$7<>"",IFERROR(INDEX(Table4[Name],SMALL(IF((Table4[Shift]="Day")*(Table4[Thursday]<>"")*(V$7>=Table4[Start Date])*(V$7<=Table4[Term Date]),ROW('Schedule Data'!$F$3:$F$33)-2),ROWS('Schedule Data'!$D$1:D1))),""),"").

    Correction marked in Red
    Last edited by marcusduton; 02-15-2015 at 01:09 AM.

  16. #16
    Registered User
    Join Date
    01-19-2015
    Location
    Warrington, PA
    MS-Off Ver
    2013
    Posts
    41

    Re: Return Multiple values from a column with index and match, and search criteria

    I think this will work, so far from what I've tested it is working correctly.

    =IF(V$7<>"",IFERROR(INDEX(Table4[Name],SMALL(IF((Table4[Shift]="Day")*(Table4[Thursday]<>"")*(IF(Table4[Start Date]="",FALSE,V$7>Table4[Start Date]))*(IF(Table4[Term Date]="",TRUE,V$7<=Table4[Term Date])),ROW('Schedule Data'!$F$3:$F$33)-2),ROWS('Schedule Data'!$D$1:D1))),""),"")

  17. #17
    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,917

    Re: Return Multiple values from a column with index and match, and search criteria

    Thats great

    (btw, I hate structured tables lol)

  18. #18
    Registered User
    Join Date
    01-19-2015
    Location
    Warrington, PA
    MS-Off Ver
    2013
    Posts
    41

    Re: Return Multiple values from a column with index and match, and search criteria

    I think this will work, unless you have a different formula to do the same thing. =IF(V$7<>"",IFERROR(INDEX(Table4[Name],SMALL(IF((Table4[Shift]="Day")*(Table4[Thursday]<>"")*(IF(Table4[Start Date]="",FALSE,V$7>=Table4[Start Date]))*(IF(Table4[Term Date]="",TRUE,V$7<=Table4[Term Date])),ROW('Schedule Data'!$F$3:$F$33)-2),ROWS('Schedule Data'!$D$1:D1))),""),"")

  19. #19
    Registered User
    Join Date
    01-19-2015
    Location
    Warrington, PA
    MS-Off Ver
    2013
    Posts
    41

    Re: Return Multiple values from a column with index and match, and search criteria

    Hey Fdibbins, do you think that this would eventually give me an erroneous answer. The formula in question is cell d87 on tab Jan(3)

    The reason I ask is I don't understand the logic for the ROW function.
    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. Replies: 0
    Last Post: 07-08-2014, 09:51 AM
  2. Replies: 7
    Last Post: 08-04-2013, 03:41 PM
  3. [SOLVED] How to return multiple values that match a single search criteria?
    By JSallen in forum Excel General
    Replies: 4
    Last Post: 11-28-2012, 11:49 AM
  4. SOS:Return multiple values against multi criteria match and index function
    By nitesh_inin in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-26-2012, 05:45 PM
  5. Replies: 3
    Last Post: 06-15-2012, 04:19 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