Index match in multiple tabs

[G MJ]

Hi,

I looking for some guidance to a problem I'm having.

I have a spreadsheet with various properties set up in their own tab. Every year we add their current budget and note the variances year to year. The properties ages vary so some will have more information than others.

For example tab1 is for a property 4 years old, every year the new budget is added and there is a formula that highlights the differences on each year.

Property1
	YEAR1	YEAR2	YEAR3	YEAR4		YEAR2DIFF	YEAR3DIFF	YEAR4DIFF
Wages	1000​	1500​	1700​	1800​		50%​	13%​	6%​
Electricity	150​	175​	215​	400​		17%​	23%​	86%​
Heating	10​	20​	30​	40​		100%​	50%​	33%​
Maintenance	25​	50​	90​	190​		100%​	80%​	111%​

tab2 is a newer property with only 3 years but it follows the same process

Property2
	YEAR1	YEAR2	YEAR3		YEAR2DIFF	YEAR3DIFF
Wages	800​	1300​	1500​		63%​	15%​
Electricity	100​	115​	190​		15%​	65%​
Heating	80​	85​	100​		6%​	18%​
Maintenance	10​	18​	40​		80%​	122%​

I have around 30 tabs with properties of varying ages.

What I'm trying to achieve is a summary table that can let me know what all he differences are year on year for all properties. Below is what I would like to achieve

	Property1			Property2		Property2
	YEAR2DIFF	YEAR3DIFF	YEAR4DIFF	YEAR2DIFF	YEAR3DIFF	YEAR2DIFF	YEAR3DIFF
Wages	50%​	13%​	6%​	63%​	15%​
Electricity	17%​	23%​	86%​	15%​	65%​
Heating	100%​	50%​	33%​	6%​	18%​
Maintenance	100%​	80%​	111%​	80%​	122%​

I've been using vlookups but this is time consuming as each tab will have a different look up point. I've tried some form of index match but I'm having no luck.

What I need is something that will lookup to the property tab then if row2 equals YEAR2DIFF and column A equals Wages enter the value.

Thanks,

 

[etaf]

if you could put property in each cell along the header - then a grid look with indirect would work

=INDEX(INDIRECT(B$2&"!G3:I7"),MATCH($A4,INDIRECT(B$2&"!A3:A7"),0),MATCH(B$3,INDIRECT(B$2&"!$G$2:I2"),0))

i have added to dropbox - only be on for a few days - cant seem to get xl2bb to work - but will post once i can

Dropbox - File Deleted - Simplify your life

www.dropbox.com

EDIT Xl2BB

Grid-lookup-ETAF.xlsx
	A	B	C	D	E	F	G	H
1
2		Property1	Property1	Property1	Property2	Property2	Property3	Property2
3		YEAR2DIFF	YEAR3DIFF	YEAR4DIFF	YEAR2DIFF	YEAR3DIFF	YEAR2DIFF	YEAR3DIFF
4	Wages	50%	13%	6%	63%	15%	#REF!	15%
5	Electricity	17%	23%	86%	15%	65%	#REF!	65%
6	Heating	100%	50%	33%	6%	18%	#REF!	18%
7	Maintenance	100%	80%	111%	80%	122%	#REF!	122%
Sheet1

Cell Formulas
Range	Formula
B4:H7	B4	=INDEX(INDIRECT(B$2&"!G3:I7"),MATCH($A4,INDIRECT(B$2&"!A3:A7"),0),MATCH(B$3,INDIRECT(B$2&"!$G$2:I2"),0))

Grid-lookup-ETAF.xlsx
	A	B	C	D	E	F	G	H	I
1	Property1
2		YEAR1	YEAR2	YEAR3	YEAR4		YEAR2DIFF	YEAR3DIFF	YEAR4DIFF
3	Wages	1000	1500	1700	1800		50%	13%	6%
4	Electricity	150	175	215	400		17%	23%	86%
5	Heating	10	20	30	40		100%	50%	33%
6	Maintenance	25	50	90	190		100%	80%	111%
Property1

Grid-lookup-ETAF.xlsx
	A	B	C	D	E	F	G	H	I
1	Property2
2		YEAR1	YEAR2	YEAR3			YEAR2DIFF	YEAR3DIFF
3	Wages	800	1300	1500			63%	15%
4	Electricity	100	115	190			15%	65%
5	Heating	80	85	100			6%	18%
6	Maintenance	10	18	40			80%	122%
7
property2

 

[G MJ]

This is perfect, many thanks.

 

[etaf]

you are welcome