+ 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
    845

    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
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    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

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

    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
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    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
    845

    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)

Similar Threads

  1. [SOLVED] Best Method... Range.Find or Index.Match?
    By Quint6778 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-28-2017, 10:37 AM
  2. Index Match to find match date to a range of dates
    By downcrusher in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-27-2017, 06:05 PM
  3. [SOLVED] Find multiple results with INDEX & MATCH on 2 criteria
    By SubwAy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-22-2017, 08:56 AM
  4. [SOLVED] Index Match with multiple criteria involving find text
    By 3345james in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-30-2015, 11:56 AM
  5. [SOLVED] Index Match with date range criteria
    By NS4Excel in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 10-24-2013, 04:58 PM
  6. Replies: 6
    Last Post: 09-30-2009, 12:05 PM
  7. Replies: 3
    Last Post: 07-11-2009, 02:58 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