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!)




[TABLE="width: 678"]
<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>[TR]
[TD="width: 64, bgcolor: transparent"]Col A[/TD]
[TD="width: 64, bgcolor: transparent"]Col B[/TD]
[TD="width: 64, bgcolor: transparent"]Col C[/TD]
[TD="width: 64, bgcolor: transparent"]Col D[/TD]
[TD="width: 64, bgcolor: transparent"]Col E[/TD]
[TD="width: 72, bgcolor: transparent"]Col F[/TD]
[TD="width: 64, bgcolor: transparent"]Col G[/TD]
[TD="width: 64, bgcolor: transparent"]Col H[/TD]
[TD="width: 64, bgcolor: transparent"]Col I[/TD]
[TD="width: 64, bgcolor: transparent"]Col J[/TD]
[TD="width: 64, bgcolor: transparent"]Col K[/TD]
[TD="width: 64, bgcolor: transparent"]Col L[/TD]
[TD="width: 64, bgcolor: transparent"]Col M[/TD]
[TD="width: 64, bgcolor: transparent"]Col N[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: #CCCCFF"]P01[/TD]
[TD="bgcolor: #CCCCFF"]P02[/TD]
[TD="bgcolor: #CCCCFF"]P03[/TD]
[TD="bgcolor: #CCCCFF"]P04[/TD]
[TD="bgcolor: #CCCCFF"]P05[/TD]
[TD="bgcolor: #CCCCFF"]P06[/TD]
[TD="bgcolor: #CCCCFF"]P07[/TD]
[TD="bgcolor: #CCCCFF"]P08[/TD]
[TD="bgcolor: #CCCCFF"]P09[/TD]
[TD="bgcolor: #CCCCFF"]P10[/TD]
[TD="bgcolor: #CCCCFF"]P11[/TD]
[TD="bgcolor: #CCCCFF"]P12[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: #CCCCFF"]Apr[/TD]
[TD="bgcolor: #CCCCFF"]May[/TD]
[TD="bgcolor: #CCCCFF"]Jun[/TD]
[TD="bgcolor: #CCCCFF"]Jul[/TD]
[TD="bgcolor: #CCCCFF"]Aug[/TD]
[TD="bgcolor: #CCCCFF"]Sep[/TD]
[TD="bgcolor: #CCCCFF"]Oct[/TD]
[TD="bgcolor: #CCCCFF"]Nov[/TD]
[TD="bgcolor: #CCCCFF"]Dec[/TD]
[TD="bgcolor: #CCCCFF"]Jan[/TD]
[TD="bgcolor: #CCCCFF"]Feb[/TD]
[TD="bgcolor: #CCCCFF"]Mar[/TD]
[TD="bgcolor: #CCCCFF"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 2"]Employee 1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent, align: right"]-1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 2"]Employee 2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]-1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 2"]Employee 3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]-1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 2"]Employee 4 [/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]-1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 104"]
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2673;"><colgroup><col width="64" style="width: 48pt;"><tbody>[TR]
[TD="width: 75, bgcolor: #CCCCFF"]Employee
[/TD]
[TD="width: 64, bgcolor: #CCCCFF"]Left?
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Employee 1
[/TD]
[TD="bgcolor: transparent"]P03
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Employee 2[/TD]
[TD="bgcolor: transparent"]P06[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Employee 3[/TD]
[TD="bgcolor: transparent"]P09[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Employee 4[/TD]
[TD="bgcolor: transparent"]P11
[/TD]
[/TR]
</tbody>[/TABLE]



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!