Excel Lookup Question

[Rob_Michael]

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

 

[Guest]

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

 

[Rob_Michael]

John,
Thanks so much for your help. This has worked perfectly.

Thanks again.
Rob Michael

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