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

 

[ryansm05]

Yep just the one, Fluff!

 

[Fluff]

In that case try
=IFERROR(INDEX($B$1:$M$1,MATCH(9^10,INDEX($B$3:$M$6,MATCH(A11,$A$3:$A$6,0),0),1)),"")

 

[jasonb75]

Yep just the one, Fluff!

But the example in post 1 has two in each row (1 and -1) so checking for anything other than 0 in these cases could return incorect results.

 

[ryansm05]

In that case try
=IFERROR(INDEX($B$1:$M$1,MATCH(9^10,INDEX($B$3:$M$6,MATCH(A11,$A$3:$A$6,0),0),1)),"")

@Fluff I've tried this out but it's just returning P12 in every cell?

@jasonb75 sorry I should have clarified that this additional formula will be limited to one value per row (unlike my example for the initial formula request which had 2)

Thanks
Ryan

 

[jasonb75]

The table that Fluff posted earlier has been corrupted by the forum update so I can't see the results there, but based on your data sample it should do what you need.

There could be problems however if the empty cells are not actually empty, but, for example have 0's hidden by conditional formatting.

Just in case it is an error matching it up to your actual data.

In the formula, $B$1:$M$1 should reference the top row with P01 to P12
$B$3:$M$6 should reference the whole of the top table excluding the top row and the column of names.
A11 should reference the first employee name in the second table.
$A$3:$A$6 should reference the list of employee names in the lefmost column of the top table.

Are there any merged cells in the table? They are the most well known formula killer.

 

[Fluff]

Works for me

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
4	Employee 2					300
5	Employee 3										11
6	Employee 4							-1
7
8
9
10
11	Employee 1	P02
12	Employee 2	P05
13	Employee 3	P10
14	Employee 4	P07
Master

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

What do you have in the blank cells?

 

[ryansm05]

Works for me
What do you have in the blank cells?

I've been trying to work this out as it's definitely not working for me - and you're right, it's the blank cells!

The blank cells are showing as zero with a custom format type: #.### - so I'm basically seeing a tiny . in every blank cell.

This data comes from a corporate system that I use and so that output will not change. I guess what would be ideal would be to exclude any zeros from this formula (sorry to keep asking!)

Thanks in advance!
Ryan

 

[jasonb75]

Try

=IFERROR(INDEX($B$1:$M$1,MATCH(2,1/INDEX($B$3:$M$6,MATCH(A11,$A$3:$A$6,0),0),1)),"")

This will need to be array confirmed with Ctrl Shift Enter

 

[Fluff]

Being primarily VBA based, this is beyond my formula knowledge.

 

[ryansm05]

Hi Jason,

This looks to be returning the first value (P01) - I have confirmed the array with Ctrl+Shift+Enter too.

Try

=IFERROR(INDEX($B$1:$M$1,MATCH(2,1/INDEX($B$3:$M$6,MATCH(A11,$A$3:$A$6,0),0),1)),"")

This will need to be array confirmed with Ctrl Shift Enter

@jasonb75 that's incredible excel knowledge - it works! What exactly is the (2,1/INDEX doing?

@Fluff you've been super helpful so thanks again.

Thanks guys!
Ryan