Fancy lookup required! :)

[ryansm05]

Hi,

I need a formula that can identify when the Employee in column A leaves the business. This can be seen from the -1 in the respective months.
For example Employee 1 leaves the business in P03. This is populated below the data and this is where I'd like the formula to work from if possible. I can adjust rows so this is not important but you can assume P01, P02, P03 etc are row1 (apologies for no attachment - company restrictions!)

Col A

Col B

Col C

Col D

Col E

Col F

Col G

Col H

Col I

Col J

Col K

Col L

Col M

Col N

P01

P02

P03

P04

P05

P06

P07

P08

P09

P10

P11

P12

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Jan

Feb

Mar

Employee 1

1

-1

Employee 2

1

-1

Employee 3

1

-1

Employee 4

1

-1

<colgroup><col width="64" style="width: 48pt;" span="5"><colgroup><col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2560;"><colgroup><col width="64" style="width: 48pt;" span="8"><tbody>
</tbody>

Employee	Left?
Employee 1	P03
Employee 2	P06
Employee 3	P09
Employee 4	P11

<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2673;"><colgroup><col width="64" style="width: 48pt;"><tbody>
</tbody>

Thanks in advance,
Ryan

 

[jasonb75]

2,1/INDEX is performing division on the row picked out by index. Numbers other than 0 are divisible, anything else will return an error.

MATCH(2 then finds the last column that didn't return an error.

Note that it doesn't work well if your cells contain very small decimal values.

 

[ryansm05]

2,1/INDEX is performing division on the row picked out by index. Numbers other than 0 are divisible, anything else will return an error.

MATCH(2 then finds the last column that didn't return an error.

Note that it doesn't work well if your cells contain very small decimal values.

That's genius!