Retrieve Data Using Data Validation

Retrieve Data Using Data Validation. I’m currently working on creating a comprehensive curriculum inventory master list that encompasses several classes and corresponding quarterly reporting pages. The project consists of a total of 5 sheets: Master, Quarter 1, Quarter 2, Quarter 3, and Quarter 4, each featuring four classes.

Retrieve Data Using Data Validation

The primary goal on each Quarterly reporting page is to select the correct class (already facilitated with data validation) and have the relevant data from the Master List populate accordingly. For example, if I choose “Pre K & Kindergarten,” a specific set of data should be retrieved, and if I select “1st and 2nd Grade,” the corresponding numbers should adjust accordingly.

Please find a sample file attached. The first page represents the master list, and the second page is the reporting page. The highlighted section is where data validation is applied, and the red area is where I would like the relevant numbers to appear.

Retrieve Data Using Data Validation

Master Sheet:

Yr1- Q1 Sheet:

Retrieve Data Using Data Validation

To achieve this, we employ the “INDEX” and “MATCH” functions. The “INDEX” function is used for value lookup, while the “MATCH” function is utilized to identify dimensions (row and column).

Follow the steps below:

Input the formula in cell E13:
=INDEX(Master!$A$5:$M$9,MATCH(‘Yr1- Q1’!$E$5,Master!$A$5:$A$9,0),MATCH(‘Yr1- Q1’!$D13,Master!$A$5:$M$5,0))
Copy the same formula into the range E14:E24.

Retrieve Data Using Data Validation

As you change the class, the numbers will automatically adjust accordingly.