+ Reply to Thread
Results 1 to 8 of 8

IF-ISTEXT-SEARCH, to return values from multiple cells

  1. #1
    Registered User
    Join Date
    08-20-2013
    Location
    Fort Lauderdale
    MS-Off Ver
    Excel 2010
    Posts
    7

    IF-ISTEXT-SEARCH, to return values from multiple cells

    Hi,

    I have a spreadsheet with two tabs. On the first tab is a list of event dates in column E and the groups invited to those events in column D. So it might say that an event on one date was attended by group1, group3, group7, while the row below shows an event on the following day attended by group3, group 4, group6. On the second tab is the full list of groups.

    What I need is a formula on the second tab that will return all the dates attended by each group, either in one cell separated by commas or in consecutive cells in that row.

    So far I've developed this formula for the second tab:
    =IF(ISNUMBER(SEARCH($C2,'Tab1'!$D4)),'Tab1'!$E4,"")

    The problem is that this just delivers the first date of attendance without also providing the later dates.

    Any suggestions?

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: IF-ISTEXT-SEARCH, to return values from multiple cells

    Hi,

    If you could attach a small sample worksheet that would be great.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    08-20-2013
    Location
    Fort Lauderdale
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: IF-ISTEXT-SEARCH, to return values from multiple cells

    Attached is a mini-version that I made, with some of the non-essential data removed. Sample1.xlsx

    The formula that I am working with is in cell B2 of the second tab. In my full length version of this spreadsheet the dates go through the entire year, and there are nearly 500 groups. I need a way to track that every group is being invited an equitable amount of times.

  4. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: IF-ISTEXT-SEARCH, to return values from multiple cells

    Hi,

    Enter this array formula (confirm with CTRL+SHIFT+ENTER, not just ENTER) in B2 and copy across and down as required:

    =IFERROR(INDEX('Calendar of Sessions'!$E$2:$E$9,SMALL(IF(ISNUMBER(SEARCH($A2,'Calendar of Sessions'!$D$2:$D$9)),ROW('Calendar of Sessions'!$D$2:$D$9)-MIN(ROW('Calendar of Sessions'!$D$2:$D$9))+1),COLUMNS($A:A))),"")

    Regards

  5. #5
    Registered User
    Join Date
    08-20-2013
    Location
    Fort Lauderdale
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: IF-ISTEXT-SEARCH, to return values from multiple cells

    That didn't quite work. Maybe it was my fault, I didn't understand the "confirm with control+shift+enter" part. The dates in column B are incorrect, and the additional dates are not showing up in the other columns.
    Sample2.xlsx

  6. #6
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: IF-ISTEXT-SEARCH, to return values from multiple cells

    "Maybe it was my fault, I didn't understand the "confirm with control+shift+enter" part."

    Once you've typed in an array formula, you don't simply press ENTER, as you would for a normal formula, but you need to hold down CTRL and SHIFT and then press ENTER. You'll see curly brackets around the formula if you've done it correctly.

    Regards

  7. #7
    Registered User
    Join Date
    08-20-2013
    Location
    Fort Lauderdale
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: IF-ISTEXT-SEARCH, to return values from multiple cells

    Got it! Now it works great! Thanks for all your help!

  8. #8
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: IF-ISTEXT-SEARCH, to return values from multiple cells

    You're welcome.

+ 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. Return Multiple Values For a Single search
    By Darkonius in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 06-19-2013, 05:21 PM
  2. Replies: 14
    Last Post: 06-17-2013, 09:43 AM
  3. Macro to return multiple values from search value
    By Mr Low in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-11-2012, 02:00 PM
  4. Search list and return multiple values
    By MarVil85 in forum Excel General
    Replies: 1
    Last Post: 06-23-2012, 03:46 PM
  5. [SOLVED] Search multiple values to return single values
    By JANA in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-27-2005, 12:05 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