index / Match Query

DubRed

Board Regular
Joined
Mar 25, 2005
Messages
97
(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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

colbymack

Active Member
Joined
Jul 14, 2005
Messages
333
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

Board Regular
Joined
Mar 25, 2005
Messages
97
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?
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Board Regular
Joined
Mar 25, 2005
Messages
97

ADVERTISEMENT

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

Board Regular
Joined
Mar 25, 2005
Messages
97
barry,

just saw your reply there

that worked!!

Thanks a mill for your help

Patrick
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Sorry,

but you could simplify that to

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

Watch MrExcel Video

Forum statistics

Threads
1,118,447
Messages
5,572,152
Members
412,446
Latest member
jorgefelipe
Top