Find? Index? Match? Help!

[SamAnnElizabeth]

I'm not sure how to describe what I'm looking to do concisely. I have two sheets, "Dates" and "Programs." On "Dates," I have a series of dates in a table like below:

	Start	Stage 1	Stage 2	End
Computer	3/22	3/30	4/25	5/29
Cell Phone	4/1	4/6	4/25	5/1
TV	3/23		4/1	4/3
Desk	4/8	4/9	4/17	5/4

<tbody>
</tbody>

So in this table, there is a list of things that have to be set up in column A. There are four stages in the set up processes (except TV, which does not have a stage 1). In the sheet "Programs" I have the following:

	This Week	Next Week
Computer
Cell Phone
TV
Desk

<tbody>
</tbody>

What I would like is for "Start" to appear in B2, meaning Computer will go through the 'Start' stage this week. Accordingly, 'Stage 1' would appear in C2, and 'Start' would appear in B4.

I already have calculated formulas to display the start and end dates of the current week as well as those for the next week (dependent on the =TODAY() function). I'm not against creating a mid-way table or anything that would simplify this process, but I'd love to get any assistance! Thanks, MrExcel team!

 

[James006]

Hi,

For a start, in cell B2 :

=IF(AND(INDEX(dates!B:B,MATCH(A2,dates!A:A,0))>(TODAY()-7),INDEX(dates!B:B,MATCH(A2,dates!A:A,0))<(TODAY()+7)),INDEX(dates!B:B,MATCH(A2,dates!A:A,0)),"")

HTH

 

[SamAnnElizabeth]

That would work if each program went by week, but if I were to run this today, the Stage 1 for Computer would be in this week and that would involve a search in column C. This is the problem I'm running into, where not all the dates for this week are in one column. Is there a way to search for this?

 

[James006]

Not sure to fully understand your constraints ...

Can you sort your data in horizontally to get an ascending order ?

 

[SamAnnElizabeth]

Horizontally, my data is in ascending order. A program will pass through Start before Stage 1, then Stage 2, then End. Because it's now a new week, this is how I would want the table in 'Programs' to look:

	This Week	Next Week
Computer	Stage 1
Cell Phone		Start
TV		Stage 2
Desk

<tbody>
</tbody>

I need a formula that will search the entire row for a date that falls within the dates of this week or next week. I know it's a little complex, I've tried a lot of different things.

 

[James006]

It seems to me the easiest way out would be a pivot table ... and grouping by week ...

Excel Pivot Table Tutorial -- Grouping Data


HTH

 

[SamAnnElizabeth]

I can't get that to work. Because my dates are the data in the chart, it's not allowing me to group the dates or have them display in the middle of the chart in any way. Could you walk me through how to do this?

 

[James006]

From what I understand, you do need to keep your data for your chart in a dedicated format ...

So, to get your pivot table working, the best is to have also a dedicated format for the pivot table ...

HTH