Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Lookup Question
I am trying to build a table and formula to look up the phase of a project
based on the week that a piece of work was done. I need to do this for multiple projects with different start dates. I have built the table listed below. I can change the Phase start date to End date if needed. Column A Column B Column C Project Phase Start Date Phase Name Project A 5/21/2007 Concept Project A 9/30/2007 Plan Project A 10/30/2007 Develop Project B 2/28/2007 Concept Project B 5/31/2007 Plan Project A 12/1/2007 Rollout I have been trying to do this using the concatenate feature to combine the Project and Date columns, then use VLOOKUP to find the appropriate Phase, but I get mostly NAs or inconsistent data at best. An example of what I need to see is below: Assuming that project A had work performed for the week of 5/25/07, I would expect a result of Concept, because the next date for Project A in the table is 9/30/07. Therefore, any date for Project A between 5/25/07 and 9/29/07 should have a result of Concept. In addition, for a week end date of 11/23/07 I would expect a result of Develop, and any dates after 12/1/07 should have a result of Rollout. Is there any way to build this table and formula to return these results? Any help anyone can provide would be greatly appreciated. Thanks, Rob |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Lookup Question
You will need to use the range lookup feature of the vlookup function.
This requires that the table array is sorted. Be sure to use Project as the primary sort key then Start Date as the secondary sort key. Add a new col-C; so that the phase name will be pushed over to D Then make col-C a simple concatenation of A&B; The result will convert the date to it's universal date I.E the first one will look like "Project A39223" This is the formaula that will return "Concept" =VLOOKUP("Project A"&DATEVALUE("5/21/2007"),$C$2:$D$7,2,TRUE) The date's data is probably what was causing your issues. HTH -- Regards, John "Rob_Michael" wrote: I am trying to build a table and formula to look up the phase of a project based on the week that a piece of work was done. I need to do this for multiple projects with different start dates. I have built the table listed below. I can change the Phase start date to End date if needed. Column A Column B Column C Project Phase Start Date Phase Name Project A 5/21/2007 Concept Project A 9/30/2007 Plan Project A 10/30/2007 Develop Project B 2/28/2007 Concept Project B 5/31/2007 Plan Project A 12/1/2007 Rollout I have been trying to do this using the concatenate feature to combine the Project and Date columns, then use VLOOKUP to find the appropriate Phase, but I get mostly NAs or inconsistent data at best. An example of what I need to see is below: Assuming that project A had work performed for the week of 5/25/07, I would expect a result of Concept, because the next date for Project A in the table is 9/30/07. Therefore, any date for Project A between 5/25/07 and 9/29/07 should have a result of Concept. In addition, for a week end date of 11/23/07 I would expect a result of Develop, and any dates after 12/1/07 should have a result of Rollout. Is there any way to build this table and formula to return these results? Any help anyone can provide would be greatly appreciated. Thanks, Rob |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Lookup Question
John,
Thanks so much for your help. This has worked perfectly. Thanks again. Rob Michael John Keith wrote: You will need to use the range lookup feature of the vlookup function. This requires that the table array is sorted. Be sure to use Project as the primary sort key then Start Date as the secondary sort key. Add a new col-C; so that the phase name will be pushed over to D Then make col-C a simple concatenation of A&B; The result will convert the date to it's universal date I.E the first one will look like "Project A39223" This is the formaula that will return "Concept" =VLOOKUP("Project A"&DATEVALUE("5/21/2007"),$C$2:$D$7,2,TRUE) The date's data is probably what was causing your issues. HTH I am trying to build a table and formula to look up the phase of a project based on the week that a piece of work was done. I need to do this for [quoted text clipped - 27 lines] Thanks, Rob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Lookup Question | Excel Discussion (Misc queries) | |||
Excel 2003 - Lookup type of question | Excel Worksheet Functions | |||
I think it is a lookup question??? | Excel Worksheet Functions | |||
Excel LOOKUP Question | Links and Linking in Excel | |||
Question on Lookup | Excel Discussion (Misc queries) |