Resource model - Lookup formula

[cs1810]

Hi,

I'm working on a resource model which has names in column A, dates of employment in (mm/yy) format across row 1. I have the names of employees, and how many days they have worked per month over the last 2 years.

As an example you could see:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]End Date[/TD]
[TD]Feb-15[/TD]
[TD]Mar-15[/TD]
[TD]Apr-15[/TD]
[TD]May-15[/TD]
[/TR]
[TR]
[TD]Employee 1[/TD]
[TD]Formula[/TD]
[TD]15[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee 2[/TD]
[TD]Formula[/TD]
[TD][/TD]
[TD]17[/TD]
[TD]10[/TD]
[TD]16[/TD]
[/TR]
</tbody>[/TABLE]


I'm trying to figure out a way for a formula to look at each row, and where the number chain finishes, give me the corresponding date in row 1 - ie. employees end date. I'm not sure how to write the syntax which would tell excel "where the number chain finishes".

In a similar fashion, do the same for a start date.

Some of the employees may have started mid way though the year as temporary staff, and others may be full-time employees who have decided to leave the organisation.

Appreciate the help!

 

[Special-K99]

Re: Help with a resource model - Lookup formula help

Will there only ever be one number chain on each row?

 

[BarryL]

Re: Help with a resource model - Lookup formula help

so employee 1 would give mar-15 and employee 2 wouldn't yield a date? you could try

=LOOKUP(2,1/(C3:I3<>""),$C$1:$I$1)

 

[cs1810]

Re: Help with a resource model - Lookup formula help

Hi BarryL,

That worked perfectly, thanks. Is there a similar method of getting the date of first entry too?

 

[cs1810]

Re: Help with a resource model - Lookup formula help

Will there only ever be one number chain on each row?

Hi, good question. Honest answer is i have only seen instances with one chain per row so far, but i guess it's possible if someone goes on maternity leave etc, they may leave the role and return. Can this be factored into BarryL's response?

 

[BarryL]

Re: Help with a resource model - Lookup formula help

Hi BarryL,

That worked perfectly, thanks. Is there a similar method of getting the date of first entry too?

the below is an array, enter with ctrl+shift+enter

=INDEX($C$1:$I$1,MATCH(TRUE,C2:I2<>"",0))