simple comparing 2 columns of data

[krisbuddies]

hi guys

Data as follows[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]john[/TD]
[TD][/TD]
[TD]amy[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]bob[/TD]
[TD][/TD]
[TD]bob[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]tim[/TD]
[TD][/TD]
[TD]keith[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]amy[/TD]
[TD][/TD]
[TD]emma[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]becky[/TD]
[TD][/TD]
[TD]lisa[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]keith[/TD]
[TD][/TD]
[TD]john[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]george[/TD]
[TD][/TD]
[TD]lisa[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]dan[/TD]
[TD][/TD]
[TD]dan[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]lisa[/TD]
[TD][/TD]
[TD]tim[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]emma[/TD]
[TD][/TD]
[TD]becky[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


What I need is a formula (preferably, I don't like macro's!) in e that looks at columns c & a and shows how much that person has moved up or down.

So
e1 looks at c1, sees "amy", looks at a1:a10 to find "amy", therefore results in +3 (4-1).
e2 looks at c2, sees "bob", looks at a1:a10 to find "bob", therefore results in 0 (2-2).


Does that make sense??

Thanks in advance guys

 

[steve the fish]

Try this out:

=IFERROR(MATCH(C1,A:A,0)-ROW(C1),"-")

 

[AlanY]

try this

Book1
	A	B	C	D	E
1	john		amy		3
2	bob		bob		0
3	tim		keith		3
4	amy		emma		6
5	becky		lisa		4
6	keith		john		-5
7	george		lisa		4
8	dan		dan		0
9	lisa		tim		-6
10	emma		becky		-5
Sheet1

Cell Formulas
Range	Formula
E1	=MATCH(C1,A:A,0)-MATCH(C1,C:C,0)

 

[krisbuddies]

Thanks both, really appreciate it.

 

[AlanY]

you're welcome