Once the user selects a month, the data preparation table should display the data corresponding to that month for each App.
To set it up, you will need your App names as the first column.
In this case, we will assume that the App names are fixed and unchanging and all you have to do is copy them over to the Data Preparation table and sort them alphabetically.
Otherwise, you can make this dynamic by using cell references.
Use a cell reference to the selected Month as the header of the Data Preparation Table.
Cell Q4 = B12
Proceed to extracting the correct data set by using the combination of the App name and month selected.
The easiest way to do it is to use Excel’s INDEX() and MATCH() functions in finding the answer.
The syntax of the INDEX() function is:
= INDEX(array, row_num, [column_num])
- array – the area where the answer can be found, $C$4:$N$9
- row_num – how many rows to move down. The MATCH() function will be used to find the App name at the raw data table.
- column_num – how many columns to the right. The MATCH() function will be used to find the Month at the raw data table.
The syntax of the MATCH() function is:
= MATCH(lookup_value, lookup_array,match_type)
- For the row_num parameter, the lookup value is the App name on the data preparation table (P5) and the lookup_array is $B$5:$B$9
- For the column_num parameter, the lookup value is the month name on the data preparation table (Q4) and the lookup_array is $C$4:$N$9
- The match_type is set to 0 since you need an exact match.
The final formula is:
CELL Q5 = INDEX($C$5:$N$9,MATCH(P5, $B$5:$B$9,0),MATCH($Q$4,$C$4:$N$4,0)
Make sure you fix the month name and the arrays before pulling the formula down to the last row.
(A more detailed INDEX()-MATCH() walkthrough can be found through this link.)