index / Match Query

[DubRed]

(This is a simplified version of a sheet i am working on)

In Cell D1 to G1 to i have the following text as column headers:
Design started
Design Finished
Development Started
Developement Finished

each project being worked on is contained on a new row. As each stage is started or finshed i enter in the date this occurred under the correct column.
In column C i have a formula that pulls the latest date from columns d to g for that row. What i would like is that the column B for that row will match the stage heading to that date.

E.g. If in Row 4, D4 = 28/10/05 and E4 = 16/11/05, then the latest date in c4 will be 16/11/05. I would like the text "design finished" (as per cell E1) to then be updated in cell b4.

Id really appreciate help with this

Thanks a mill

 

[colbymack]

How about a nested IF statement?

In B5:
If(C5=D5,D2,IF(C5=E5,E2,IF(C5=F5,F2,IF(C5=G5,G2,"NOT FOUND"))))

This assumes your titles you want to display are in row 2, and that you are currently working on the 5th row of data. You could also put in an evaluation to ensure that C5 actually has a value to begin with.

HTH

 

[DubRed]

Hi Colyback,

thanks for the reply.

The one problem with the nested if statements is that they example i gave is a simplified version of sheet i am working on. the full sheet has 10 different columns, which i think causes a problem, as dont if statements only work up to 6 different events?

 

[eliW]

Hi,

You can use offset formula:

Eli

Book1
	A	B	C	D	E	F	G
1	-	-	-	DesignS	DesignF	DevpS	DevpF
2	-	DesignF	3/1/05	1/1/05	3/1/05	-	-
3	-	DevpS	6/1/05	5/1/05	6/1/05	6/1/05	-
4	-	-	-	-	-	-	-
5	-	-	-	-	-	-	-
Sheet1

 

[barry houdini]

As you deduced, INDEX/MATCH would probably be the best approach in my opinion

in B4

=INDEX(D$1:G$1,MATCH(C4,D4:G4,0))

the only problem I can see with that is that if you have the same date for different stages then it will show the first stage, which is probably not what you want. Could you have the same date for different stages?

 

[barry houdini]

Assuming your dates always get later as you go across the row (i.e. the rightmost date is always the latest) and that where you have no date you have blanks you could use

=INDEX(D$1:G$1,MATCH(9.99999999999999E+307,D4:G4))

this will stop the problem I mentioned in my last post

 

[DubRed]

cheers for your help

i have tried the index/match formula barry and it works, except for as you say the fact that it will take the first column if two have the same date. I would prefer it took the column value to the right if this occurred

anyone any ideas??

 

[DubRed]

barry,

just saw your reply there

that worked!!

Thanks a mill for your help

Patrick

 

[barry houdini]

Sorry,

but you could simplify that to

=LOOKUP(9.99999999999999E+307,D4:G4,D$1:G$1)