Lookup Array Calendar

[Guest]

Given the following, how can I perform the lookup?

I have two tables. The first table has employee names in Column A, and
Columns B through K contain requested days off for each employee.

Name 1 2 3 4
John Smith 09-15-06 09-16-06 09-17-06 etc.
Judy Jones 10-11-06 10-12-06 11-01-06 etc.


The second table is a calendar that lists employee names in Column A and the
dates of the month in Columns B through AF. In the calendar table, I want to
be able to type in the employee name, and if there is a match between a
calendar date and a time off request for an employee, I want to display a “Vâ€
in the cell. The formula needs to reference the cell in which the employee
name is entered.

09-14-06 09-15-06 09-16-06 09-17-06 09-18-06
John Smith V V V

The employee name in the second table is variable, depending on the result
of other formulas, or it could be typed into the cell.

How can I do this?


Thanks,

--- Don Q.

 

[Guest]

Use a combination of the following functions:
VLOOKUP
MATCH
IF
ISNA

IF(ISNA(VLOOKUP(Namerange,EntirelookupRange,MATCH(LookupDatevlaue,Lookuprangedaterowrange,FALSE),FALSE)),"",V)

Hope this helps.

Bill Horton

 

[Guest]

Thanks, William:

I had tried various combinations of VLOOKUP and HLOOKUP and named ranges,
but to no avail. It looks like your suggestion should work, but I am still
getting #N/A. In your example, the last argument of the MATCH function is
FALSE. Could that be causing the problem? I changed it to 0, since I wanted
an exact match, but still got #N/A. Any ideas?

 

[Guest]

Never mind... Your suggestion worked after playing with MATCH a little bit!

Thanks a bunch!!

--- Don Q.

 

[Guest]

Well, it worked, sort of. When associated with other employee names, it
reports a vacation day if the date appears anywhere in the first table. I
need to associate specific dates uniquely for each employee. Any ideas? I
am at a loss...