+ Reply to Thread
Results 1 to 5 of 5

Find First Match Of Two Criteria In Index Range

  1. #1
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    834

    Question Find First Match Of Two Criteria In Index Range

    Hey there,

    I'm trying to search a range of cells from left to right to see if two criteria are met, and if so, display the related date of the first instance found with a preceding asterisk. The two criteria are as follow:

    1. Does the value in the cell contain an asterisk?

    2. Is its associated date greater than or equal to today's date?

    I've come up with this formula, but it only gives me results for the first instance found and ignores any others:

    Please Login or Register  to view this content.
    The example Google Sheet can be found here. If I remove the asterisk from cell B4, the formula returns the date from cell E4 properly, but when I enter an asterisk to cell B4, cell E4 is completely ignored. Does anyone know what am I doing wrong?
    Last edited by swordswinger710; 03-11-2019 at 10:13 AM.
    There is so much good in the worst of us,
    And so much bad in the best of us,
    That it hardly behooves any of us
    To talk about the rest of us.

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2016 primarily
    Posts
    4,000

    Re: Find First Match Of Two Criteria In Index Range

    Try:


    =iferror(CONCATENATE("*"&TEXT(INDEX({A4,C4,E4,G4,I4,K4,M4},MATCH(1, ISNUMBER(FIND("*",{B4,D4,F4,H4,J4,L4,N4}))*({A4,C4,E4,G4,I4,K4,M4}>=TODAY()),0)),"mmm d")),"NO MATCH FOUND")
    Rory
    Days when we raged, we flew off the page
    Such damage was done

  3. #3
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    834

    Re: Find First Match Of Two Criteria In Index Range

    Wow. That did the trick, thank you!

    Do you know if FIND runs slower than MATCH, or should there be no real difference?

    Also, I'm wondering what the best way would be to add another condition to this formula, such as making sure the date is greater than or equal to today AND does not say "DONE", do you have any suggestions? Thank you again!

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2016 primarily
    Posts
    4,000

    Re: Find First Match Of Two Criteria In Index Range

    You mean the date cell doesn't say DONE? To add more criteria just add them into the multiplication part.

    I wouldn't expect much difference in performance (other than that one doesn't work. )

  5. #5
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    834

    Re: Find First Match Of Two Criteria In Index Range

    Yes, that was what I meant, and thanks to your multiplication tip, I was able to come up with this glorious formula with yours in the middle of it, which is doing the trick as far as I can tell:

    =IF(CO5="","",IF(AVERAGE(CR5,CT5:CW5,CY5:CZ5)=1,"DONE",IFERROR(CONCATENATE("*"&TEXT(INDEX({U5,AD5,AM5,AV5,BE5,BN5,BW5,CF5},MATCH(1,ISNUMBER(FIND("*",{W5,AF5,AO5,AX5,BG5,BP5,BY5,CH5}))*({U5,AD5,AM5,AV5,BE5,BN5,BW5,CF5}>=TODAY())*({U5,AD5,AM5,AV5,BE5,BN5,BW5,CF5}<>"DONE"),0)),"MMM D")),IF(TODAY()<=CO5,WORKDAY(CO5,SUM(IF(CR5=1,0,CQ5-(CQ5*CR5)),IF(AVERAGE(CT5:CW5)=1,0,CS5-(CS5*AVERAGE(CT5:CW5))),IF(AVERAGE(CY5:CZ5)=1,0,CX5-(CX5*AVERAGE(CY5:CZ5))))),WORKDAY(TODAY(),SUM(IF(CR5=1,0,CQ5-(CQ5*CR5)),IF(AVERAGE(CT5:CW5)=1,0,CS5-(CS5*AVERAGE(CT5:CW5))),IF(AVERAGE(CY5:CZ5)=1,0,CX5-(CX5*AVERAGE(CY5:CZ5)))))))))

    Haha, and yes, good point on the performance. :P

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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