Index Match Returns Non-Blank - Causes Date Calculation To Falsely Return True

[gryce]

I'm working with a large set of data where I need to match a name to another name and get the associated date. Column C returns the looked up date, however, even though I've added a catch to blank out results not found, it's actually inserting a character (it's unclear what the character is) when a lookup value isn't found (but it doesn't when it is found, even if it doesn't have any associated data). Column B tells if the looked up date is N days past whatever is defined in another worksheet (bottom left of this example).

[TABLE="class: grid, width: 991, align: left"]
<tbody>[TR]
[TD]Patient Name[/TD]
[TD]Patient Data Exceeds Value[/TD]
[TD]Looked Up Patient Data[/TD]
[TD][/TD]
[TD]Blank/NotBlank[/TD]
[TD][/TD]
[TD]Patient Name[/TD]
[TD]Required Patient Data[/TD]
[/TR]
[TR]
[TD]Object01[/TD]
[TD]No[/TD]
[TD][/TD]
[TD][/TD]
[TD]Not Blank[/TD]
[TD][/TD]
[TD]Object02[/TD]
[TD="align: right"]6/21/2017[/TD]
[/TR]
[TR]
[TD]Object02[/TD]
[TD]No[/TD]
[TD="align: right"]06/21/17[/TD]
[TD][/TD]
[TD]Not Blank[/TD]
[TD][/TD]
[TD]Object01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Object03[/TD]
[TD]No[/TD]
[TD="align: right"]04/10/10[/TD]
[TD][/TD]
[TD]Not Blank[/TD]
[TD][/TD]
[TD]Object03[/TD]
[TD="align: right"]4/10/2010[/TD]
[/TR]
[TR]
[TD]Object04[/TD]
[TD]No[/TD]
[TD="align: right"]04/10/10[/TD]
[TD][/TD]
[TD]Not Blank[/TD]
[TD][/TD]
[TD]Object04[/TD]
[TD="align: right"]4/10/2010[/TD]
[/TR]
[TR]
[TD]Object05[/TD]
[TD]No[/TD]
[TD="align: right"]04/10/10[/TD]
[TD][/TD]
[TD]Not Blank[/TD]
[TD][/TD]
[TD]Object05[/TD]
[TD="align: right"]4/10/2010[/TD]
[/TR]
[TR]
[TD]Object06[/TD]
[TD]Yes[/TD]
[TD="align: right"]11/15/17[/TD]
[TD][/TD]
[TD]Not Blank[/TD]
[TD][/TD]
[TD]Object06[/TD]
[TD="align: right"]11/15/2017[/TD]
[/TR]
[TR]
[TD]Object07[/TD]
[TD]Yes[/TD]
[TD="align: right"]11/15/17[/TD]
[TD][/TD]
[TD]Not Blank[/TD]
[TD][/TD]
[TD]Object07[/TD]
[TD="align: right"]11/15/2017[/TD]
[/TR]
[TR]
[TD]Object08[/TD]
[TD]No[/TD]
[TD][/TD]
[TD][/TD]
[TD]Not Blank[/TD]
[TD][/TD]
[TD]Object08[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Object09[/TD]
[TD]No[/TD]
[TD][/TD]
[TD][/TD]
[TD]Not Blank[/TD]
[TD][/TD]
[TD]Object09[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Object10[/TD]
[TD]No[/TD]
[TD][/TD]
[TD][/TD]
[TD]Not Blank[/TD]
[TD][/TD]
[TD]Object10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Object11[/TD]
[TD]No[/TD]
[TD][/TD]
[TD][/TD]
[TD]Not Blank[/TD]
[TD][/TD]
[TD]Object11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Object12[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD]Blank[/TD]
[TD][/TD]
[TD]Object14[/TD]
[TD="align: right"]7/29/2013[/TD]
[/TR]
[TR]
[TD]Object13[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD]Blank[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Object14[/TD]
[TD]No[/TD]
[TD="align: right"]07/29/13[/TD]
[TD][/TD]
[TD]Not Blank[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Days Before Exceeds[/TD]
[TD="align: right"]60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


































The formula used in column B is:
=IF(C2<today()-$b$17,"no","yes")

The formula used in column C is:
=IF(ISNA(INDEX($H$2:$H$500,MATCH(A2,$G$2:$G$500,0))),"",INDEX($H$2:$H$500,MATCH(A2,$G$2:$G$500,0)))

Column E doesn't really exist in the worksheet I'm using, but I've added it temporarily to help identify what's going on. It's formula is:
=IF(C2<>"","Not Blank","Blank")

It's worth noting that I'm formatting the data in column C with the following custom format:
[=0]"";mm/dd/yy;@

Any help to identify and resolve what's going on is appreciated. I've banged my head on this for awhile but I'm at a loss.</today()-$b$17,"no","yes")

 

[Aladin Akyurek]

Is what the range in column H shows the desired output?

 

[gryce]

Sorry for my ugly post, it looks like my column B data didn't come through. Column B's formula as follows:

=IF(C2<TODAY()-$B$17,"No","Yes")


The range of values for column G/H is goes out to 1000 in my spreadsheet. Does that answer your question or are you after something else?

 

[gryce]

=IF( C2 < TODAY () - $B$17 , "No", "Yes" )

OK.. something about that formula the site doesn't like. Really here's the formula from Column B with additional spacing so I can post it.

 

[Aladin Akyurek]

Instead of discussing the (non-working) formulas, you might want to try to specify in words what you require. Let's start with what is input and what is output...

 

[gryce]

That's fair.

Sorry I'm not communicating this better. I'll try again.

I have Column A which contains a name, and Column B which tells me if the value in Column C is older than N many days, which is defined in a cell on the worksheet. Column C is actually getting its values from a separate worksheet by performing an index/match against we'll say Column A (name) and Column B (date) to pull back it's date value. The problem seems to center around on the original spreadsheet if the name isn't found in the target list the cell appears blank, but it's not actually blank, which causes the calculation in Column B to incorrectly report that the date is older than N days.

Thanks for looking at this.