IF AND Function where the Logical Criteria is Text Help

[KuraiChikara]

Hello, I am trying to perform an IF AND function where the logical function in the IF portion is text on a different sheet or tab.
In the example below I was using different tabs on the same sheet:

Sheet 1:

Name	Day	Formula
John	Sunday
Jim	Monday
Bob	Tuesday
Bill	Wednesday
Steve	Thursday
Owen	Friday
Luke	Saturday

<tbody>
</tbody>

Sheet 2:

Name	Day	Fruit
John	Sunday	Watermelon
Jim	Monday	Apple
Bob	Tuesday	Kiwi
Bill	Wednesday	Strawberry
Steve	Thursday	Grapes
Owen	Friday	Honeydew
Luke	Saturday	Blueberry

<tbody>
</tbody>

With my example I can use this formula and have it WORK:
=IF(AND(A2=Sheet2!A2,B2),Sheet2!C2,"NO MATCH")

Verbally the formula should read: If A2 equals John AND Sunday in sheet 2, put C2 or Watermelon, if it doesnt match, put "no match."
Here is my issue.

For that formula to work, in sheet 2, I have to pin point where "John" is, but what if "John" is on A1063 on sheet 2? What I would like to do, is search for if John is a match without having to designate where John is.

I've tried this and it doesnt work:
=IF(AND(A2=Sheet2!"John",B2),Sheet2!C2,"NO MATCH")

 

[Nine Zero]

use INDEX and MATCH for this im using 10000 as a safe amount of values in your list

=INDEX(Sheet2!$C$2:$C$10000,MATCH($A2&$B2,Sheet2!$A$2:$A$10000&Sheet2!$B$2:$B$10000,0))

That should give you your result

**** This is an array formula, make sure instead of just hitting ENTER, you do CTRL + SHIFT + ENTER ****

 

[KuraiChikara]

That works, you are a genius thank you very much. I'm ok with Excel, but almost all of the Array type formulas I tend to never use. This has helped me a lot.

 

[Nine Zero]

No problem, glad i could help

Everything ive learned for Excel i give credit 100% to MrExcel!