Vlookup: can it do this?

[enigmahack]

I've been messing with vlookup for a while, and I think it can, but I'm not sure entirely, so I'm working on it

I have a pretty simple task here - we have a list of agents that have agent id's and there's a large number of them.

What I want to do:
have a list on one form of all agents.
have a list on another form of all attrits *fired/quit, whatever*
from the attrit list, I want to lookup the employee ID, and if there's a match on the "all agent" list, change that line somehow, in either color, etc...
There's almost 1500 agents and with so many coming in and going out all the time it's hard to look through that list every time.
Anyway, if someone has an idea, I'd be very grateful!

 

[enigmahack]

Yeah, I know how to use autofiltering - but here's my issue:
I guess they don't want the entries automatically deleted. *Shrugs*

So I tried this:
=IF(MATCH('Attrit List'!C:C,B:B ),0,1)
Basically if something matches this list, ouput = 0 so I can conditional format that whole line - they can see the reps attrit.
But it always outputs as true...

Thanks for being patient with me folks

 

[Barrie Davidson]

Eh, what's 'attrits'?

I believe it's shorthand for attrition's.

 

[Barrie Davidson]

Shouldn't that be?

=IF(MATCH(B:B,'Attrit List'!C:C,0),0,1)

Is this being put in your agent listing and you're looking up the value in the worksheet called 'Attrit List'?

 

[enigmahack]

Ahhhh, that's what I did wrong - let me punch that in and see if it works.

 

[enigmahack]

Awesome awesome awesome - thanks SO much for all everyones help! That's wicked cool

 

[Aladin Akyurek]

Sorry, I could have been a little clearer:

Sheet 1 - list of reps - the list basically looks like this:

John A - 111111
John B - 111112
John C - 111113

etc.

Sheet 2 - list of Attritions:
John L - 111122

What I need it to do is to basically highlight the line that John L - 111122
exists on Sheet 1 because he's fired - The date idea would work, but we don't have that information provided to us - we simply get their emp.ID letting us know that they left.

Attritions

Book2
	A	B	C	D
1	Attritions
2	Emp	Emp ID
3	John B	111112
4	John L	111125
5
Attritions

Master

Book2
	A	B	C	D	E
1			0	2
2	Emp	Emp ID	Idx	Active: Emp	Active: Emp ID
3	John A	111111	1	John A	111111
4	John B	111112		John C	111113
5	John C	111113	2
6	John L	111125
7
Master

C1 must house a 0.

C3, copied down:

=IF(B3<>"",IF(ISNA(MATCH(B3,Attritions!$B$3:$B$4,0)),LOOKUP(9.99999999999999E+307,$C$1:C2)+1,""),"")

D1:

=LOOKUP(9.99999999999999E+307,$C$1:$C$6)

D3, copied across to E3 then down:

=IF(ROW()-ROW(D$3)+1<=$D$1,LOOKUP(ROW()-ROW(D$3)+1,$C$3:$C$6,A$3:A$6),"")