Forum Discussion
IF AND formula to check name and date then return YES, NO
MAVOTRP To use the MATCH function with multiple criteria, try the following:
=IF(ISNUMBER(MATCH(1, (Table1[Name adjusted]=A109)*(Table1[Leave Date]=$D$105), 0)), "YES", "NO")Note: with older versions of Excel, you may need to press Ctrl+Shift+Enter when entering this formula.
For more information on how this works, please see: https://exceljet.net/formulas/index-and-match-with-multiple-criteria
Alternatively, you could also use the COUNTIFS function as follows:
=IF(COUNTIFS(Table1[Name adjusted], A109, Table1[Leave Date], $D$105), "YES", "NO")I hope that helps. Cheers!
The formula you provided uses the IF and AND functions along with INDEX and MATCH to achieve the desired outcome, but there's a small adjustment needed to ensure it only returns "YES" when both the name and date match corresponding rows in Table1.
Here's the breakdown of your formula:
- IFERROR(INDEX(Table1[Name adjusted], MATCH(A109, Table1[Name adjusted], 0)), ""): This part searches for the name in cell A109 within the "Name adjusted" column of Table1 and returns the corresponding value if found, or an empty string ("") if not found. The IFERROR function handles potential errors if no match is found.
- = A109: This compares the retrieved value (or empty string) with the name in cell A109.
- Similar logic applies to the IFERROR statement for the date comparison.
The issue lies in the way the AND function works. It evaluates each condition independently. Even if the name matches in a different row (e.g., B65 in your example), the AND function might still evaluate to TRUE as long as the date matches somewhere else in the "Leave Date" column (e.g., G65).
Here's the corrected formula to address this:
=IF(AND( INDEX(Table1[Name adjusted], MATCH(A109, Table1[Name adjusted], 0)) = A109, INDEX(Table1[Leave Date], MATCH(D105, Table1[Leave Date], 0)) = D105 ), "YES", "NO")
In this modified version, we removed the extra IFERROR statements and directly compared the results of the INDEX and MATCH functions with the corresponding values in Sheet 1 (A109 and D105). This ensures that both the name and date must match in the same row of Table1 for the formula to return "YES".
