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

 

[Fluff]

How about

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		P01	P02	P03	P04	P05	P06	P07	P08	P09	P10	P11	P12
2		Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar
3	Employee 1		1	-1
4	Employee 2			1			-1
5	Employee 3						1			-1
6	Employee 4		1									-1
7
8
9
10	Employee	Left?
11	Employee 1	P03
12	Employee 2	P06
13	Employee 3	P09
14	Employee 4	P11
Data

Cell Formulas
Range	Formula
B11	=IFERROR(INDEX($B$1:$M$1,MATCH(-1,INDEX($B$3:$M$6,MATCH(A11,$A$3:$A$6,0),0),0)),"")

 

[Special-K99]

Try

=IFERROR(INDEX(B$1:M$1,1,MATCH(-1,B2:M2,0)),"Employee not yet left")
copy down the column

 

[jmacleary]

Try this formula. It assumes your employee list at the top runs from A1 to A12, and the table below starts in row 16 ( so the cell for employee 1 would be B17. Adjust the numbers as necessary, or replace the ranges with named ranges.

=INDEX(A$1:M$1,1,MATCH(-1,OFFSET(A$1,MATCH(A17,A$1:A$12,0)-1,0,1,13),0))

 

[ryansm05]

Thanks everyone for your suggestions - all worked perfectly!

Much appreciated

Ryan

 

[Fluff]

Glad we could help & thanks for the feedback

 

[ryansm05]

Hi, one further question - if I wanted to adapt the formula for a very similar predicament but where '-1' could range between multiple numbers (-100 to 200) …. is it possible to look up value X rather than specific to '-1'?

Value X being a value between -100 : 200

Thanks
Ryan

 

[Fluff]

Put the value you want to lookup in C10 & use
=IFERROR(INDEX($B$1:$M$1,MATCH($C$10,INDEX($B$3:$M$6,MATCH(A11,$A$3:$A$6,0),0),0)),"")

 

[ryansm05]

The issue is that the value will vary - I've extended this formula to 1000's of rows also. I guess what I'm looking for is the formula to look for any value other than 0.

Is this possible?

 

[Fluff]

Will you only have 1 non blank cell in each row?