Hi all,
I have two tables in a workbook. The first table has names and dates of birth (among other things) and the second table has names, DOBs, a few other things, and effective dates for an insurance policy. I'm trying to use index/match to check for a person by name/DOB and return "Y" if their coverage effective date was 1/1/2015. The formula I'm using is:
This is based off of several posts/articles/etc I've read that explained using an array formula to check multiple criteria in an index/match formula. When I use this formula, if it doesn't find the person who has both name and DOB matching the main sheet (meaning they aren't on the second sheet), it returns "#N/A" - which I'm fine with, I can add error handling later if I want to. The problem is that if it does find the person with the right name/DOB combo, regardless of what their policy effective date actually is, it returns blank, as if it isn't finding anyone whose effective date is 1/1/15; I've manually looked and found several "test people" who do have the 1/1/15 date but are still returning a blank in the formula column.
I've tried checking/rechecking the formatting on the date columns in both sheets to make sure they're dates and not text strings. I've tried with and without double-quotes around the date in my formula. I've made sure I'm doing C-S-E for the array formula. I've tried using DATE/DATEVALUE in place of just having the date itself in the formula. I have no idea why either the INDEX/MATCH formula seems entirely incapable of returning 1/1/15 as a value, or why the IF part of the formula seems entirely incapable of parsing that 1/1/15=1/1/15 and returning the "true" result I've set it up to return. Any idea what I'm doing wrong?
I have two tables in a workbook. The first table has names and dates of birth (among other things) and the second table has names, DOBs, a few other things, and effective dates for an insurance policy. I'm trying to use index/match to check for a person by name/DOB and return "Y" if their coverage effective date was 1/1/2015. The formula I'm using is:
Code:
{=IF(INDEX(Table2[EFFECTIVE_DATE],MATCH(1,([@[Dependent Name]]=Table2[Name])*([@DOB]=Table2[DATE_OF_BIRTH]),0))=1/1/2015,"Y","")}This is based off of several posts/articles/etc I've read that explained using an array formula to check multiple criteria in an index/match formula. When I use this formula, if it doesn't find the person who has both name and DOB matching the main sheet (meaning they aren't on the second sheet), it returns "#N/A" - which I'm fine with, I can add error handling later if I want to. The problem is that if it does find the person with the right name/DOB combo, regardless of what their policy effective date actually is, it returns blank, as if it isn't finding anyone whose effective date is 1/1/15; I've manually looked and found several "test people" who do have the 1/1/15 date but are still returning a blank in the formula column.
I've tried checking/rechecking the formatting on the date columns in both sheets to make sure they're dates and not text strings. I've tried with and without double-quotes around the date in my formula. I've made sure I'm doing C-S-E for the array formula. I've tried using DATE/DATEVALUE in place of just having the date itself in the formula. I have no idea why either the INDEX/MATCH formula seems entirely incapable of returning 1/1/15 as a value, or why the IF part of the formula seems entirely incapable of parsing that 1/1/15=1/1/15 and returning the "true" result I've set it up to return. Any idea what I'm doing wrong?
