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

 

[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?