# Find First Match Of Two Criteria In Index Range

1. ## 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?

2. ## 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")

3. ## 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. ## 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. ## 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:

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

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

#### 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