cyrilbrd
Well-known Member
- Joined
- Feb 2, 2012
- Messages
- 4,113
- Office Version
- 365
- Platform
- Windows
- Mobile
This post is related to this thread.
Considering one source of data with multiple columns such as:
And consider on a different sheet mutple tables that would be populated based on the information found in the Tbl_Source such as:
What would be the best solution to populate the information from Tbl_Source column Val into each tables using VBA (or UDF).
The objective being to be able to populate the tables via VBA based on 3 conditions, "cdt", "type" and "cat"
Considering one source of data with multiple columns such as:
VBA-INDEX-MATCH-Based-on-Multiple-Criteria.xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
4 | cdt1 | col2 | col3 | col4 | type | cat | Val | ||
5 | cdt1 | type1 | cat1 | 10 | |||||
6 | cdt1 | type1 | cat2 | 95 | |||||
7 | cdt1 | type1 | cat3 | 11 | |||||
8 | cdt1 | type4 | cat4 | 12 | |||||
9 | cdt1 | type1 | cat5 | 100 | |||||
10 | cdt1 | type1 | cat6 | 22 | |||||
11 | cdt1 | type2 | cat1 | 40 | |||||
12 | cdt1 | type5 | cat2 | 56 | |||||
13 | cdt1 | type2 | cat3 | 13 | |||||
14 | cdt1 | type2 | cat4 | 55 | |||||
15 | cdt1 | type2 | cat5 | 53 | |||||
16 | cdt1 | type2 | cat6 | 15 | |||||
17 | cdt1 | type3 | cat1 | 22 | |||||
18 | cdt1 | type3 | cat3 | 86 | |||||
19 | cdt1 | type3 | cat4 | 84 | |||||
20 | cdt1 | type3 | cat5 | 77 | |||||
21 | cdt1 | type3 | cat6 | 30 | |||||
22 | cdt1 | type4 | cat2 | 94 | |||||
data |
And consider on a different sheet mutple tables that would be populated based on the information found in the Tbl_Source such as:
VBA-INDEX-MATCH-Based-on-Multiple-Criteria.xlsm | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | Title | cdt1 | |||||
2 | |||||||
3 | |||||||
4 | |||||||
5 | |||||||
6 | table1 | cat1 | cat2 | cat3 | cat4 | ||
7 | type1 | 10 | 95 | 11 | |||
8 | type2 | 40 | 13 | 55 | |||
9 | |||||||
10 | |||||||
11 | |||||||
12 | |||||||
13 | |||||||
14 | Table2 | cat1 | cat2 | cat3 | |||
15 | type3 | 22 | 86 | ||||
16 | type4 | 94 | |||||
results |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B7:E8 | B7 | =IFERROR(INDEX(Tbl_Source[Val],MATCH(1,($B$1=Tbl_Source[cdt1])*($A7=Tbl_Source[type])*(B$6=Tbl_Source[cat]),0)),"") |
B15:D16 | B15 | =IFERROR(INDEX(Tbl_Source[Val],MATCH(1,($B$1=Tbl_Source[cdt1])*($A15=Tbl_Source[type])*(B$14=Tbl_Source[cat]),0)),"") |
What would be the best solution to populate the information from Tbl_Source column Val into each tables using VBA (or UDF).
The objective being to be able to populate the tables via VBA based on 3 conditions, "cdt", "type" and "cat"