Index/Match - Search Table and then search column

[kobe_beef_14]

I'm running into a bit of a problem trying to do a lookup whereby I search for a specific table (with its own named range) and then a column within that table which corresponds to a row. Here's an analogy to make it a lot easier to understand:

I'm a teacher with a grade workbook. I have different sheets (Tests, Homework, etc.) and within each sheet are individual tables, which correspond to a specific assignment. Within "Tests," I might have individual tables named "Test 1/1," "Test 1/31" and so on. Each table also has its own named range (e.g.: Test_Jan_One).

I want to to be able to compare students' scores on each assignment, so I have a sheet named "Compare Tool." On it, there are three dropdowns: Two are the students in the class (identical, and each corresponds to a named range like "Class_Roster." In the third dropdown search I have a dropdown of ALL assignments. To make it easier, I've added a helper sheet that simply lists out all of the tests/homeworks/etc. into its own named range.

In theory, I could first select a single assignment from the dropdown, then select two students from the other two dropdowns, and then compare their scores from that assignment. I've tried using a double lookup, but quickly realized that I'm not exactly searching two columns, but rather over many tables (~15 or so) and then for data within a specific table.

In summary, Index/Match whereby I first match an entire table (that has its own named range) and then within that table, I can then match the students to their scores.

Thank you!

 

[AlanY]

I've made a simple mockup.
name range: Col A:B test1, D:E test2 & G:H test3
with the 3 dropdown for test & 2 students.

will that work for you?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Student 1	50		Student 1	55		Student 1	60			Student 3	Student 4
2	Student 2	60		Student 2	65		Student 2	70		TEST2	75	85
3	Student 3	70		Student 3	75		Student 3	80
4	Student 4	80		Student 4	85		Student 4	90
5	Student 5	90		Student 5	95		Student 5	100
Sheet1

Cell Formulas
Range	Formula
K2	=VLOOKUP(K$1,INDIRECT($J2),2,0)
L2	=VLOOKUP(L$1,INDIRECT($J2),2,0)

 

[kobe_beef_14]

I believe it will, thank you very much! Here's to learning and adding indirect to my repertoire. Thanks again!

 

[AlanY]

you're welcome