Lookup/Date Question

[Danny640]

My range of data is as follows:
A B C D E F G
1 5/1/2009 11/1/2009 11/1/2010 11/1/2011 11/1/2012 11/1/2013 11/1/2014
2 YES YES YES YES YES YES YES


The dates are in Row 1 and the Corresponding "YES" is in row 2 below each
date.

I then have a list of dates, some of which appear in the above range and
some that don't, i.e. 5/1/2009, 6/1/2009, 7/1/2009, 8/1/2009, 9/1/2009,
10/1/2009, 11/1/2009.

What I am trying to do is return the value below the date (YES in this case)
if the date I am looking up is in the range, and if it is not in the range,
to return something else, like "NO". I have been trying to use a combination
of the IF and HLOOKUP functions, but in the cases where the date is not in
the range (like 6/1/2009) it always returns #N/A.

The formula I have tried is something like =IF(HLOOKUP(date to look up i.e.
6/1/2009,$A$1:$G$2,2,FALSE)="YES",HLOOKUP(date to look up i.e.
6/1/2009,$A$1:$G$2,2,FALSE),"NO")

Any help would be greatly appreciated. Thanks.

 

[Pete_UK]

You can trap the #N/A using ISNA. Assume the dates you want to look up
are in A10 downwards. Enter this formula in B10:

=IF(ISNA(hlookup(A10,$A$1:$G$1,1,0)),"NO",HLOOKUP(A10,$A$1:$G$2,2,0))
or even:
=IF(ISNA(hlookup(A10,$A$1:$G$1,1,0)),"NO","YES")

if all the dates in your table have "YES" below them, as in your
example.

Copy the formula down column B for as many dates that you have to
check.

Hope this helps.

Pete

 

[Trevor Shuttleworth]

=IF(ISNA(HLOOKUP(A8,$A$1:$G$2,2,FALSE)),"NO",(HLOOKUP(A8,$A$1:$G$2,2,FALSE)))

Or, why bother with the second row (if it is always "YES") ?

=IF(ISNA(HLOOKUP(A8,$A$1:$G$2,1,FALSE)),"NO","YES")

Regards

Trevor

 

[Danny640]

Thank you both for writing back...I didn't think of the ISNA function.

 

[Pete_UK]

You're welcome, Danny - thanks for feeding back.

Pete

 

[Trevor Shuttleworth]

Ditto

Thank you both for writing back...I didn't think of the ISNA function.