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".
- Hello. Thank you for your response. Unfortunately this still returns YES even if the name and date don't match in the same row. It seems this modified version is still picking up on D105 matching other cells in Column G and not necessarily the corresponding row that the name has matched in Column B. The response below however is correct and works so I have used that instead. Thanks again.
smylbugti222gmailcom Please be advised:
By using A.I. tools to auto-generate answers without testing them for accuracy, you are potentially driving people away from this forum. A.I. very rarely produces Excel formulas that are 100% correct. More often than not, they contain false or misleading information, and in many cases, they are 100% wrong. Posting A.I. generated responses without having any personal knowledge of Excel formulas of your own is highly irresponsible. Please have the decency to at least test your findings before sharing them in the future.
dear friend,
seeking help from others to solve your problem is a commendable act, if anyone who volunteers their expertise or time to assist you, regardless of the outcome, deserves appreciation and respect, disrespecting someone genuinely trying to help is unprofessional and counterproductive.
Remember, mutual respect fosters a collaborative environment. This message is not intended to hurt your feelings or escalate any jealousy. If you suspect this was generated by AI, feel free to use your own judgment or provide accurate data to collaborate effectively with other community members.
smylbugti222gmailcom My comment was intended to be as respectful as possible. I wouldn't have said anything if I truly believed that you composed the answer on your own. However, there are various factors that lead me to believe it was an A.I. generated response. These factors include your recent response history on the forum (ie: posting multiple highly detailed and lengthy answers to different questions in less than 10 minutes), as well as the suspicious wording and obvious inconsistencies in many of your answers. It's totally fine for someone to offer assistance and make an honest mistake... nobody's perfect. However, I have a hard time believing that a human being wrote this particular response.
It starts out by providing a breakdown and analysis of the original formula (which is pretty much accurate), but then offers a solution that completely ignores the analysis and makes the exact same mistake as the original formula. I do not believe that a person who understands what's wrong with the formula would then offer virtually the same formula as a solution. The only difference between your formula and the original was that you removed the IFERROR functions, completely ignoring your own conclusion that the source of the problem was caused by the AND function evaluating each INDEX/MATCH result independently.
If you truly wrote this answer without the assistance of A.I. tools, then I sincerely apologize for the misunderstanding. On the other hand, if you are in fact using A.I. tools to generate responses, I would like to reiterate my suggestion that you at least test the results first before posting them. If you are unable to do so for any reason, I would highly recommend adding a disclaimer to the bottom of your solutions warning users that "this response was generated with the assistance of A.I. tools and has not been tested fully to verify its accuracy", or something to that effect. This would go a long way in helping users (especially beginners) to understand why a suggested solution would contradict itself and not work properly.
Peace.
