Nested MATCH functions within IF statement not working

Question

So I am having a serious headache from this problem and I hope someone out there can help me. I am trying to get a value with 2 different sets of matching criteria. This doc contains 2 separate sheets, and is laid out as follow:

The data is laid out like this:

Sheet 1

_____A_____    ____B___
Last Name 1    11:00 AM
Last Name 2    11:30 AM
Last Name 3    12:00 PM

Sheet 2

_____A_____    ____B___
Last Name 1    11:00 AM
Last Name 2    11:30 AM
Last Name 3    12:00 PM

The formula I am using is:

=IF(AND(MATCH(Sheet1!A1 ,Sheet2!A:A,0),MATCH(Sheet1!B1,Sheet2!$B:$B,0)),1,0)

I want this to basically return a 1 if the name in both columns match, and the time in both columns match. Currently what it is doing, is simply returning a 1 for each time value that matches, ignoring names.

Hopefully I am making enough sense here. Please any help would be very appreciated.

Answer 1

Try using COUNTIFS like this

=IF(COUNTIFS(Sheet2!A:A,Sheet1!A1,Sheet2!$B:$B,Sheet1!B1),1,0)

That will return a 1 if there is at least one row with matching time and name

Answer 2

I'm not totally clear on what you're trying to accomplish. It would help if you included some differences in data (as opposed to all matches) and then an output example so we can see what it is you want. But, if I understood correctly, you want to check only the same row in the two corresponding sheets? So data like this:

Sheet 1

_____A_____    ____B___
Last Name 1    11:00 AM
Last Name 2    11:30 AM
Last Name 3    12:00 PM

Sheet 2

_____A_____    ____B___
Last Name 1    11:00 AM
Bad Name       11:30 AM
Last Name 3    Bad Time

Will yield this:

Sheet 3

_____A_____  
1    
0
1

That would just be putting this formula in A1 and copy and pasting down the column:

=IF(AND(Sheet1!A1=Sheet2!A1, Sheet1!B1 = Sheet2!B1),1,0)

If, instead, you are trying to see if there is a match for one entire row anywhere in the other sheet, that's different and will depend on which sheet is your search criteria rows and which sheet is your search area.