Find Specific Value by Referencing multiple cells...

[Robz999]

Hello,

I have annual financial statements issued for each area, every year. Each financial statement is saved as a separate excel file. I would like to compare the results area by area, and year by year. To do this, I need my main spread sheet to reference specific cells in other spreadsheets.</SPAN>
But there are two challenges I can’t figure out how to overcome.</SPAN>

1. The cells I’m interested in are not always on the same row. (Ex. if one area doesn’t sell a specific product, that product is omitted. So one area may sell 3 products and would have Product A, Product B, Product and Product C in Rows 1, 2, and 3. Another area may sell 4 different products and would have Product B, Product D, Product Q, and Product R in Rows 1, 2, 3 and 4). Generally, I could manage this with VLOOKUP/INDEX, but part 2 confounds this. </SPAN>
2. The Product names repeat themselves under different headers. </SPAN>

Example: (Header in Column A; Product in Column D)</SPAN>
Volumes- </SPAN>
Product A </SPAN>
Volumes per day- </SPAN>
Product A </SPAN>
Prices- </SPAN>
Product A</SPAN>
Revenue-</SPAN>
Product A</SPAN>
So what I need it to do is look up the correct header, then look ONLY in the values below that header for the value I want.</SPAN>

*Note*: The Headers are always the same; Only the products (and consequently the row #) change. I have included an example below.</SPAN>

Output:

	Volume per day</SPAN>
	A</SPAN>	B</SPAN>	C</SPAN>	D</SPAN>	G</SPAN>	H</SPAN>	Q</SPAN>	S</SPAN>	T</SPAN>
Property 1</SPAN>	2</SPAN>	22</SPAN>	15</SPAN>	9</SPAN>	NA</SPAN>	NA</SPAN>	NA</SPAN>	NA</SPAN>	NA</SPAN>
Property 2</SPAN>	4</SPAN>	22</SPAN>	NA</SPAN>	NA</SPAN>	NA</SPAN>	NA</SPAN>	14</SPAN>	9</SPAN>	4</SPAN>

<TBODY>
</TBODY>

</SPAN>


Data: (Note- each "Property" is a different spreadsheet)

PROPERTY 1</SPAN>	Year:</SPAN>	2011</SPAN>
Volumes</SPAN>
Product</SPAN>	Account Major</SPAN>	Account Minor</SPAN>	Account Name</SPAN>	Total</SPAN>
	6000</SPAN>	10</SPAN>	A</SPAN>	730</SPAN>
	6000</SPAN>	20</SPAN>	B</SPAN>	8030</SPAN>
	6000</SPAN>	22</SPAN>	C</SPAN>	5475</SPAN>
	6000</SPAN>	30</SPAN>	D</SPAN>	3285</SPAN>
Volumes Per Day</SPAN>
Product</SPAN>	Account Major</SPAN>	Account Minor</SPAN>	Account Name</SPAN>	Average</SPAN>
	6000</SPAN>	10</SPAN>	A</SPAN>	2</SPAN>
	6000</SPAN>	20</SPAN>	B</SPAN>	22</SPAN>
	6000</SPAN>	22</SPAN>	C</SPAN>	15</SPAN>
	6000</SPAN>	30</SPAN>	D</SPAN>	9</SPAN>
Prices</SPAN>
Product</SPAN>	Account Major</SPAN>	Account Minor</SPAN>	Account Name</SPAN>	Average</SPAN>
	6000</SPAN>	10</SPAN>	A</SPAN>	$ 0.50 </SPAN>
	6000</SPAN>	20</SPAN>	B</SPAN>	$ 1.10 </SPAN>
	6000</SPAN>	22</SPAN>	C</SPAN>	$ 1.90 </SPAN>
	6000</SPAN>	30</SPAN>	D</SPAN>	$ 0.70 </SPAN>
Revenue</SPAN>
Account Type</SPAN>	Account Major</SPAN>	Account Minor</SPAN>	Account Name</SPAN>	Total</SPAN>
	6000</SPAN>	10</SPAN>	A</SPAN>	$ 365.00 </SPAN>
	6000</SPAN>	20</SPAN>	B</SPAN>	$ 8,833.00 </SPAN>
	6000</SPAN>	22</SPAN>	C</SPAN>	$ 10,402.50 </SPAN>
	6000</SPAN>	30</SPAN>	D</SPAN>	$ 2,299.50 </SPAN>

<TBODY>
</TBODY>

PROPERTY 2</SPAN>	Year:</SPAN>	2011</SPAN>
Volumes</SPAN>
Product</SPAN>	Account Major</SPAN>	Account Minor</SPAN>	Account Name</SPAN>	Total</SPAN>
	6000</SPAN>	10</SPAN>	A</SPAN>	1460</SPAN>
	6000</SPAN>	22</SPAN>	C</SPAN>	8030</SPAN>
	6000</SPAN>	90</SPAN>	Q</SPAN>	5110</SPAN>
	6300</SPAN>	10</SPAN>	S</SPAN>	3285</SPAN>
	6300</SPAN>	17</SPAN>	T</SPAN>	1460</SPAN>
Volumes Per Day</SPAN>
Product</SPAN>	Account Major</SPAN>	Account Minor</SPAN>	Account Name</SPAN>	Average</SPAN>
	6000</SPAN>	10</SPAN>	A</SPAN>	4</SPAN>
	6000</SPAN>	20</SPAN>	B</SPAN>	22</SPAN>
	6000</SPAN>	90</SPAN>	Q</SPAN>	14</SPAN>
	6300</SPAN>	10</SPAN>	S</SPAN>	9</SPAN>
	6300</SPAN>	17</SPAN>	T</SPAN>	4</SPAN>
Prices</SPAN>
Product</SPAN>	Account Major</SPAN>	Account Minor</SPAN>	Account Name</SPAN>	Average</SPAN>
	6000</SPAN>	10</SPAN>	A</SPAN>	$ 0.50 </SPAN>
	6000</SPAN>	20</SPAN>	B</SPAN>	$ 1.10 </SPAN>
	6000</SPAN>	90</SPAN>	Q</SPAN>	$ 0.10 </SPAN>
	6300</SPAN>	10</SPAN>	S</SPAN>	$ 9.00 </SPAN>
	6300</SPAN>	17</SPAN>	T</SPAN>	$ 14.00 </SPAN>
Revenue</SPAN>
Account Type</SPAN>	Account Major</SPAN>	Account Minor</SPAN>	Account Name</SPAN>	Total</SPAN>
	6000</SPAN>	10</SPAN>	A</SPAN>	$ 730.00 </SPAN>
	6000</SPAN>	20</SPAN>	B</SPAN>	$ 8,833.00 </SPAN>
	6000</SPAN>	90</SPAN>	Q</SPAN>	$ 511.00 </SPAN>
	6300</SPAN>	10</SPAN>	S</SPAN>	$ 29,565.00 </SPAN>
	6300</SPAN>	17</SPAN>	T</SPAN>	$ 20,440.00 </SPAN>

<TBODY>
</TBODY>

 

[konew1]

You can use offset function to find the range where you need to run the vlookup. I took another sheet of my own, because I wanted to be sure this will work across different workbooks, so you won't recognise any of your data but you will be able to adapt the formula.

Data File first

Excel Workbook
	F	G	H	I	J
2			4	5	7
3	Head1	4	8	9	11
4	P1	5	9	10	12
5	P2	7	11	12	14
6	Head2	9	13	14	16
7	P1	11	15	16	18
8	P2	13	17	18	20
9	Head3	16	20	21	23
10	P1	17	21	22	24
11	P2	19	23	24	26
12	P3	23	27	28	30
13	Head4
Sheet1

Output file

Excel Workbook
	A	B	C	D
1	*	*	*	*
2	All Headings	*	*	*
3	Head1	3	*	*
4	Head2	6	*	*
5	Head3	9	*	*
6	Head4	13	*	*
7	*	*	*	*
8	Combined data	*	*	*
9	Head1	*	*	*
10	P1	*	5	9
11	P2	*	7	11
12	P3	*	*	*
13	P4	*	*	*
Sheet10