+ Reply to Thread
Results 1 to 7 of 7

Searching for multiples and returning a name

  1. #1
    Registered User
    Join Date
    04-08-2017
    Location
    Dixon, CA
    MS-Off Ver
    2007
    Posts
    5

    Post Searching for multiples and returning a name

    I have an array formula and trying to add a second item to look for in the INDEX or IF statement.

    I have tried OR and COUNTIFS and get nothing or strange numbers.

    The BRO_CNT is the column where the names to be retrieved from.
    The TIME* is the column where to look for (currently) the letter I. I also want to include a second item... an I with a space ("I ").
    Here is the formula:

    =IF(COUNTIF(TIME730,"I")>=1,INDEX(BRO_CNT,SMALL(IF(TIME730="I",ROW(BRO_CNT)),1)),"")

    I have two other array formulas the look for the second and third occurrences of "I". So the last 1 changes to 2 and 3.

    I have tried everything I know and then some for about 2 hours and I am stuck. I will be very grateful if someone can un-stick me. Trying to fix a church spreadsheet.

    Bill

  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
    53,051

    Re: Searching for multiples and returning a name

    The we I normally do this is
    =IF(COUNTIF(TIME730,"I")>=1,INDEX(BRO_CNT,SMALL(IF(TIME730="I",ROW(BRO_CNT)),1)),"")....

    =IFerror(INDEX(BRO_CNT,SMALL(IF(TIME730="I",ROW(BRO_CNT)),rows($A$1:A1))),"")
    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
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Searching for multiples and returning a name

    Hi Bill- Can you use the wildcard (*) character? "I*" would find "I" and "I ", but also "Iota" and "Ida". If that's not an issue, then this works:

    =IF(COUNTIF(TIME730,"I*")>=1,INDEX(BRO_CNT,SMALL(IF(TIME730="I*",ROW(BRO_CNT)),1)),"")

    If that IS an issue, how about:

    =IF(COUNTIF(TIME730,"I")+COUNTIF(TIME730,"I ")>=1,INDEX(BRO_CNT,SMALL(IF((TIME730="I")+(TIME730="I "),ROW(BRO_CNT)),1)),"")

    I haven't worked w array formulae in a while, but I remember how tricky they can be. Hope this helps... -Lee
    Last edited by leelnich; 04-09-2017 at 11:51 PM.

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: Searching for multiples and returning a name

    pl upload a sample file.

  5. #5
    Registered User
    Join Date
    04-08-2017
    Location
    Dixon, CA
    MS-Off Ver
    2007
    Posts
    5

    Re: Searching for multiples and returning a name

    Thank you. I should have mentioned the ways I have tried, I guess.

    I have tried the countif + countif and did not work an don't know why. I can't use the "I*" as there are other items that start with I. Came across TRIM since the post last night and it seems logical to me to try that. Lots of time people entering data into the cells start with an "I" and the auto suggest show IL so instead they hit a space bar to stop it but then end up with I+space which the formula doesn't see as an "I". I will study on TRIM and give it a go. If you know how to use TRIM I would like to get some pointers.

    Thanks for your suggestions.

    Bill

  6. #6
    Registered User
    Join Date
    04-08-2017
    Location
    Dixon, CA
    MS-Off Ver
    2007
    Posts
    5

    Re: Searching for multiples and returning a name

    Thank you for your help.

  7. #7
    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
    53,051

    Re: Searching for multiples and returning a name

    Happy to help and thanks for the feedback

+ 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. Returning Multiples Values with a Lookup using Index Array
    By NEEDEXCELHELP123456 in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 11-04-2014, 10:16 AM
  2. Searching & returning
    By acoger in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-08-2012, 05:30 AM
  3. VLookup returning same data when multiples exist
    By Laserfast in forum Excel General
    Replies: 8
    Last Post: 05-12-2011, 12:56 PM
  4. Searching a value in row and returning to header value
    By adrianorc in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-05-2009, 11:41 AM
  5. Searching and returning row number of a value
    By MikeDH in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 PM
  6. Searching and returning row number of a value
    By MikeDH in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 11:05 AM
  7. Searching and returning row number of a value
    By MikeDH in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1