EXCEL LOOKUP HELP

[Twenny02]

Hi,

Can anyone help with a suggestion for the following please? I apologise in advance for the long mail!

I have a 'Milestone Exception Data' table in Excel that lists milestones (one per row) and gives Start / Finish dates for each milestone. There is also a column that shows days delay for each milestone exception and another column that shows the unique milestone ID.

On another worksheet (in the same workbook) I have created a 'Calendar' view. The first column has the unique milestone ID, the second has the milestone name, then all the further columns represent a week, with the date for the first day of each week at the top of each respective column. In the rows I list the milestones that had exceptions (one milestone per row).

I am trying to do some type of lookup that refers to the Milestone Data table, and in the Calendar view returns a 'true' value in the relevant cell based on the date of the milestone exception. For example if Milestone 1 went into exception on 25th April, then in the Calendar view I would want a true value in the cell intersection between the row for Milestone 1 and the column for the week commencing 21st April.

I can get the Calendar working so that the true value appears in the correct week column for the exception and against the correct milestone ID. I can also highlight and put info in the highlighted Calendar cell such as 'days delay' using a lookup to the Milestone Exception Data table and conditional formatting.

The problem I have is that I am using Vlookup to do this based on the unique Milestone ID. It works fine until the same milestone goes into exception more than once (a scenario that is very likely to happen). This would give the same milestone in the Milestone Exception Data table (with the same unique ID), more than once just with different exception dates. The Vlookup fails due to it only returning the set of data from the first exception for the milestone (obviously Vlookup needs unique reference data to work properly).

Does anyone know a function that could look at data in the Milestone Exception Table, and return a true value in the relevant cells in the Calendar view for multiple instances of a unique milestone ID? Could it be done so all exceptions for a unique milestone ID appear on one row in the Calendar view in the respective week columns (as opposed to multiple instances of exceptions for the milestone appearing in the Calendar view, one row per exception)?

Thank you for any help you can provide!

 

[SkipVought]

hi,

So you do NOT have, "milestones ([highlight #729FCF]one per row[/highlight]) and gives Start / Finish dates for each milestone." Is that correct?

Please post a sample from your Milestone Exception Data that illustrates the issue at question.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Twenny02]

Hi Skip,

Thank you for your reply! Think I have sorted now using Index and Match combination.