simple comparing 2 columns of data

[krisbuddies]

hi guys

Data as follows

	a	b	c	d	e
1	john		amy
2	bob		bob
3	tim		keith
4	amy		emma
5	becky		lisa
6	keith		john
7	george		lisa
8	dan		dan
9	lisa		tim
10	emma		becky

<tbody>
</tbody>

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