KuraiChikara
Board Regular
- Joined
- Nov 16, 2016
- Messages
- 111
- Office Version
- 2013
- Platform
- Windows
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:
<tbody>
</tbody>
Sheet 2:
<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")
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")