AND & OR in IF(ISNA(MATCH formula

[screach]

Little background.
What I am wanting to do and attempted to do up above is the following:
Sheet 1 contains data from activities that took place. Sheet 2 contains general event information. I am wanting to match up the specific time from sheet one with a start and end time of sheet two. In other words if if the activity took place at 3:49pm and we had an event (on the same day, same state) that was offered between 3:30pm and 4:30pm then it matches and says YES.

What I have currently is two spreadsheets with varying data. Sheet1 has State, Date, Day, City, Time. Sheet2 has State, Time Start, Time End, Date, Day (and some other irrelevant data).

I was hoping I could use a formula to do the job but the one I drafted below fails on the "And/Or" section where I was trying to match up the exact time in a range of start and end times.

=IF(ISNA(MATCH(c2,Sheet2!$e$2:$e$38,0)*AND(a2,Sheet2!$a$2:$a$38,0)*AND((e2,Sheet2!$b$2:$b$38,0)*OR(e2,<=Sheet2!$c$2:$c$38)0)),"N","Y")

c2 = Day (So Day from Sheet1 equals one of the days listed in column E of Sheet two)
a2 = State (So state from Sheet1 equals one of the states listed in column A of sheet two)

I'm a database guy not an excel guru, but I am not dealing with enough data to justify the work to create a database to track this. I was hoping to do it in excel with a formula...am I barking up the wrong tree?

Any and all help is greatly appreciated.

 

[screach]

In the attached Sample, the first row in sheet one would fail since State didn't match. Second row would match since all three criteria match. Last row would fail due to times not matching. I hope this helps.

 

[eibi]

Welcome to the forum.

I had to do a little data scrubbing on your sample file because the times you have in Sheet 2 are reading as text "12:30pm" rather than times 12:30 PM.

However, once that was resolved, I used an array formula to get the output you were seeking:

=SUM((A2=Sheet2!$A$2:$A$4)*(B2=Sheet2!$D$2:$D$4)*(E2>Sheet2!$B$2:$B$4)*(E2<Sheet2!$C$2:$C$4))>0

Because this is an array formula, after you paste it in cell F2, you'll need to put your cursor in the formula and press Ctrl+Shift+Enter to make it work.

This will return a TRUE/FALSE value. If you want a "YES"/"NO" value, just put it into an IF()

=IF(SUM((A2=Sheet2!$A$2:$A$4)*(B2=Sheet2!$D$2:$D$4)*(E2>Sheet2!$B$2:$B$4)*(E2<Sheet2!$C$2:$C$4))>0,"YES","NO")

Then, you can drag down to the other cells. See attached.

 

[screach]

Ok that's awesome! Thank you so very very much! My headache is gone.

 

[David Evans]

Here's a slightly different variation - agree with @eibi about the data scrubbing on the time formats ...

 

[screach]

eibi,

Thanks again, yeah your right on the data...just was getting around to squaring that away. Anyhow your sample worked great so long as the sheets had an equal amount of rows, but as soon as sheet2 stops (at say at row 37) the formula no longer works on sheet1. It throws a #Error! in the cell. In my working tables I have one which is only 37 rows in length. The other table has hundreds of rows.

 

[eibi]

Screach,

I'm glad to help, but as you'll see in the attached, I am unable to replicate the error that you are having on Sheet 1.

I'm going to need more information to diagnose your problem.

 

[screach]

eibi,

Here is another sample with more data that shows the error. I am certain it is something I am doing wrong on my end. One other question. The forumula as you wrote it, does it require all three things to be equal in order to be a True statement or is it only one of the three that must be equal to get a True statement?

 

[eibi]

The formula I provided is a special type of formula: an array formula.

In order for it to work, there's a special trick -- a 'magic' keystroke, if you will.

In order to 'fix' your sample file, all I have to do is click on cell E2, place my cursor in the formula at the top of the screen (as though I were going to edit the formula) and press CTRL+Shift+Enter.

I can check if it worked by clicking on E2 again -- and this time, the formula at the top of the screen is enclosed in {brackets} -- like magic!

Now, I click and drag E2 down to fill the rest of the column.

See attached.

There's lots of information on this site about array formulas. I'd suggest you start with this one:

http://chandoo.org/wp/2009/03/25/using-array-formulas-example1/

 

[eibi]

To answer your second question, this formula requires all 4 tests to be true in order to return a TRUE value. The 4 tests are:
- Team Name match
- Date match
- Time is after start time
- Time is before end time

 

[screach]

eibi,

Thank you! For some reason I thought I did that but apparently that was not the case. Thanks big time for all your help! Also, I really appreciate you taking the time to explain your formula as I continue to learn it is a big help.