# Resource model - Lookup formula

#### cs1810

##### New Member
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:

 End Date Feb-15 Mar-15 Apr-15 May-15 Employee 1 Formula 15 20 Employee 2 Formula 17 10 16

<tbody>
</tbody>

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!

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### Special-K99

##### Well-known Member
Re: Help with a resource model - Lookup formula help

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

#### BarryL

##### Well-known Member
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

##### New Member
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

##### New Member
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

##### Well-known Member
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))

Replies
4
Views
364
Replies
1
Views
171
Replies
3
Views
189
Replies
6
Views
127
Replies
5
Views
320

1,190,872
Messages
5,983,330
Members
439,839
Latest member
iblackie

### 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.

### Which adblocker are you using?

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