index / Match Query

DubRed

Board Regular
(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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
How about a nested IF statement?

In B5:

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

Hi Colyback,

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?

Hi,

You can use offset formula:

Eli
Book1
ABCDEFG
1---DesignSDesignFDevpSDevpF
2-DesignF3/1/051/1/053/1/05--
3-DevpS6/1/055/1/056/1/056/1/05-
4-------
5-------
Sheet1

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?

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

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??

barry,

that worked!!

Thanks a mill for your help

Patrick

Sorry,

but you could simplify that to

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

Replies
5
Views
130
Replies
8
Views
234
Replies
7
Views
381
Replies
1
Views
202
Replies
4
Views
134

1,214,771
Messages
6,121,453
Members
449,033
Latest member
Kcolwell16

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.

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

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