Find Value in Row and Return Column Heading

**SMITEE** · 05-18-2010, 01:24 PM

Hi everyone,
I'm putting together a project plan in Excel. I have a grid of Xs entered under a row of dates. The Xs indicate when a task starts and ends.

I also have 2 columns where I enter the start and end date.

Right now I'm entering both the Xs and the start and end dates manually, which is a huge waste of time. I want a formula for the Start column and for the End column that looks at the first x in the row and the last x in the row (respectively), and returns the column header. Can this be done with Excel formulas? I have a feeling it can, but I have not been able to figure it out.

**veejar** · 05-18-2010, 01:29 PM

hi

can you upload a sample file of what you want?

regards,

veejar

**SMITEE** · 05-18-2010, 01:36 PM

Sample file uploaded. Thank you for your response Veejar.

**veejar** · 05-18-2010, 02:04 PM

Hi,

Please check the attached file whether it works for you.

Regards,

Veejar

**SMITEE** · 05-18-2010, 05:21 PM

Hi again, I'm afraid this doesn't provide me with what I'm looking for. Thanks for putting it together though.

What I want to do is enter a row of Xs under the date headings, then have Excel find the first X in the row and return the column heading as the "Start Date." For "End Date" I want Excel to return the column heading for the last X in the row.

In other words I enter:

6/6 | 6/13 | 6/20
_x_ | _x_ | _x_

And Excel gives me:

Start Date | End Date
___6/6___ | ___6/20___

I've tried to find formulas that find the first non-empty cell in a row and the last non-empty cell in a row and return the heading for that cell, but I'm not able to put it together correctly.

**veejar** · 05-19-2010, 12:31 AM

well an X would be a vague method. My advice is use some kind of numbers which are either in increasing or decreasing order. For Example it can be row() or column(). The reason for using numbers is that it would be easier then to find the smallest and largest number in that row or column which can used to lookup the corresponding date values

will send you an example soon

regards,

veejar

**DonkeyOte** · 05-19-2010, 03:50 AM

Originally Posted by SMITEE

What I want to do is enter a row of Xs under the date headings, then have Excel find the first X in the row and return the column heading as the "Start Date." For "End Date" I want Excel to return the column heading for the last X in the row.

Based on your first sample file:

B2: =INDEX($D$1:$Z$1,MATCH("x",$D2:$Z2,0))
copied down

C2: =LOOKUP(2,1/($D2:$Z2="x"),$D$1:$Z$1)
copied down

or alternatively, if non "x" values are true blanks then:

C2: =INDEX($D$1:$Z$1,MATCH(REPT("Z",255),$D2:$Z2))
copied down

modify ranges to suit.

**SMITEE** · 05-19-2010, 01:01 PM

This works wonderfully. Thanks so much DonkeyOte. You have no idea how much time you've saved me.

Thanks again.

**capsym** · 01-31-2012, 10:38 AM

HELLO - THANKYOU AGAIN DONKEY OTE - as this has been so useful to me also.

Could I please ask one more question related to this?

In the same way SMITEE requires the date returned, I have multiple "x" in columns containing names of colleagues in the headers.

How can I have a total return of the names against which the x's are please?

Hope you don't chuckle at how simple this is for you

Thank you

TOTAL |JOHN | FRED | PETER| JULIE| SUE |
John, Fred, Sue | X | X | | | X |

Is this possible?

**liamsnodden** · 12-10-2012, 01:25 PM

Can I expand the formula using a different range?

I've tried this but it says too many arguments?

=IF(F5="","",IF(AND(F5>=I2,F5<J2),I1,IF(AND(F5>=$J$2,F5<K2),J1,K1,IF(AND(F5>=K2,F5<L2),K1,L1))))

Thanks in advance.

Liam

Find Value in Row and Return Column Heading

LinkBack

Thread Tools

Rate This Thread

Display

Find Value in Row and Return Column Heading

Re: Find Value in Row and Return Column Heading

Re: Find Value in Row and Return Column Heading

Re: Find Value in Row and Return Column Heading

Re: Find Value in Row and Return Column Heading

Re: Find Value in Row and Return Column Heading

Re: Find Value in Row and Return Column Heading

Re: Find Value in Row and Return Column Heading

Re: Find Value in Row and Return Column Heading

Re: Find Value in Row and Return Column Heading

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions