Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Array Calendar
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Array Calendar
Use a combination of the following functions:
VLOOKUP MATCH IF ISNA IF(ISNA(VLOOKUP(Namerange,EntirelookupRange,MATCH( LookupDatevlaue,Lookuprangedaterowrange,FALSE),FAL SE)),"",V) Hope this helps. Bill Horton "Don Quixote" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Array Calendar
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? -- Thanks, --- Don Q. "William Horton" wrote: Use a combination of the following functions: VLOOKUP MATCH IF ISNA IF(ISNA(VLOOKUP(Namerange,EntirelookupRange,MATCH( LookupDatevlaue,Lookuprangedaterowrange,FALSE),FAL SE)),"",V) Hope this helps. Bill Horton "Don Quixote" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Array Calendar
Never mind... Your suggestion worked after playing with MATCH a little bit!
Thanks a bunch!! --- Don Q. "William Horton" wrote: Use a combination of the following functions: VLOOKUP MATCH IF ISNA IF(ISNA(VLOOKUP(Namerange,EntirelookupRange,MATCH( LookupDatevlaue,Lookuprangedaterowrange,FALSE),FAL SE)),"",V) Hope this helps. Bill Horton "Don Quixote" wrote: 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Array Calendar
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... -- Thanks, --- Don Q. "William Horton" wrote: Use a combination of the following functions: VLOOKUP MATCH IF ISNA IF(ISNA(VLOOKUP(Namerange,EntirelookupRange,MATCH( LookupDatevlaue,Lookuprangedaterowrange,FALSE),FAL SE)),"",V) Hope this helps. Bill Horton "Don Quixote" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
table, index, array, match, lookup? | Excel Worksheet Functions | |||
Lookup in Multiple Columns, Return Multiple Values | Excel Worksheet Functions | |||
VLOOKUP keeping array lookup reference | Excel Discussion (Misc queries) | |||
i need help with a lookup and/or array type formula | Excel Worksheet Functions | |||
Array Formula Calendar | Excel Worksheet Functions |