using index match to compare numbers on different sheets based on a common criteria

[chrisk67]

I am trying to compare numerical amounts on different sheets/tabs for common criteria, for example sales of an item on two months to determine the difference.

I have a vlookup for this, but would prefer an index match

The sheets would be as follows

sheet 1

row 1 row 2

apples 10
bananas 15
cherries 15


sheet 2

row 1 row 2

apples 20
bananas 20
cherries 10

so the formula cells would show
-10
+5
-5

 

[Fluff]

How about

Book1
	A	B
2	Apples	-10
3	Bananas	-5
4	Pears	5
Sheet3

Cell Formulas
Range	Formula
B2	=INDEX(Sheet1!$B$2:$B$4,MATCH(A2,Sheet1!$A$2:$A$4,0))-INDEX(Sheet2!$B$2:$B$4,MATCH(A2,Sheet2!$A$2:$A$4,0))

 

[chrisk67]

fantastic, this was driving me mad. 5 star mod

 

[Fluff]

You're welcome & thanks for the feedback