Index and Match or similar

[Access Beginner]

Hi all,

I have below some test data. Which in my version at work is a Pivot Table ( based on over 300,000 rows of data). The data is linked to a query in Access and when new data is imported and the pivot table is refreshed, values of 'Subject may' change. Once the new data has been updated in the Pivot Table, I copy the whole sheet and replace as values, thus removing the Pivot Table and leaving a static table. The raw data is then deleted, as the file is over 250MB with the data included, which is why I cannot use the power of a Pivot Table when sending the document up the chain.

What I do with this is then, create a dynamic table, which is used to create a chart, so a user can select his/her Region and the chart will update.

My issue, as the values of Subject may change, using a vlookup, means I will have to update the column number in each of the formulas. I had used Index and Match, but I cannot get it to work with 3 criteria.

This is a small example of the data, there could be up to 25 subjects and the subject numbers may vary from week to week. My data is run weekly.

I hope I have been clear.



Test data as below

Excel 2010
	B	C	D	E	F	G	H
4		Female	Female	Female	Male	Male	Male
5	Region Name	Subject 1	Subject 2	Subject 3	Subject 1	Subject 3	Subject 5
6	Region 1	1202	1595	1493	387	564	1306
7	Region 2	32	853	684	663	374	1625
8	Region 3	1669	1552	1765	1351	1277	1190
9	Region 4	627	1512	542	1143	1091	749
10	Region 5	1367	1510	640	247	492	1852
11	Region 6	75	1263	1945	1227	1441	181
12	Region 7	1474	442	1219	1531	1293	1808
13	Region 8	1265	411	1065	1032	1820	409
14	Region 9	651	1565	1735	270	0	1972
15	Region 10	1481	149	383	1654	855	1164
16	Region 11	1659	1459	646	1345	1049	290
17	Region 12	582	1494	1449	1843	1085	55
18	Region 13	1963	1209	1698	571	906	974
19	Region 14	1933	1013	1185	1376	715	1830
20	Region 15	1369	1044	1747	1262	10	1049
21	Region 16	1760	630	1679	1467	285	816
22	All Regions	19109	17701	19875	17369	13257	17270
23
24		Expected results when All Regions is selected ( data validation)
25		Female	Female	Female	Male	Male	Male
26		Subject 1	Subject 2	Subject 3	Subject 1	Subject 3	Subject 5
27	All Regions	19109	17701	19875	17369	13257	17270
Sheet1

 

[circledchicken]

Hi,

Perhaps try something like this for your 3 criteria match:

Sheet1

	A	B	C	D	E	F	G
1
2
3
4		Female	Female	Female	Male	Male	Male
5	Region Name	Subject 1	Subject 2	Subject 3	Subject 1	Subject 3	Subject 5
6	Region 1	1202	1595	1493	387	564	1306
7	Region 2	32	853	684	663	374	1625
8	Region 3	1669	1552	1765	1351	1277	1190
9	Region 4	627	1512	542	1143	1091	749
10	Region 5	1367	1510	640	247	492	1852
11	Region 6	75	1263	1945	1227	1441	181
12	Region 7	1474	442	1219	1531	1293	1808
13	Region 8	1265	411	1065	1032	1820	409
14	Region 9	651	1565	1735	270	0	1972
15	Region 10	1481	149	383	1654	855	1164
16	Region 11	1659	1459	646	1345	1049	290
17	Region 12	582	1494	1449	1843	1085	55
18	Region 13	1963	1209	1698	571	906	974
19	Region 14	1933	1013	1185	1376	715	1830
20	Region 15	1369	1044	1747	1262	10	1049
21	Region 16	1760	630	1679	1467	285	816
22	All Regions	19109	17701	19875	17369	13257	17270
23
24		Expected results when All Regions is selected ( data validation)
25		Female	Female	Female	Male	Male	Male
26		Subject 1	Subject 2	Subject 3	Subject 1	Subject 3	Subject 5
27	All Regions	19109	17701	19875	17369	13257	17270

<thead>
</thead><tbody>
</tbody>

Excel 2010

Worksheet Formulas Cell Formula B27 =INDEX(4:22,MATCH($A27,$A$4:$A$22,0),MATCH(B$25&B$26,INDEX(4:4&5:5,0),0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Access Beginner]

thanks for that, worked perfectly.

=INDEX(4:22.... part of the formula is referencing A4 to A22? Haven't seen cell referenced like that before, but then again, I'm still learning Excel.

Once again thanks

 

[circledchicken]

thanks for that, worked perfectly.

=INDEX(4:22.... part of the formula is referencing A4 to A22? Haven't seen cell referenced like that before, but then again, I'm still learning Excel.

Once again thanks

Your welcome! No - the 4:22 references the whole of those rows. E.g. if your last column is XFD then it would be similar to A4:XFD22