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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

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)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,997
Messages
5,834,799
Members
430,322
Latest member
excelnoobnoob

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top