INDEX-MATCH Alternative for Matrix Where COUNTIFS Works But I Need Values, Not Counts

[John 314]

Hello Folks,

I have a matrix that looks like this:

A1-NAME B1-CLASSNAME1 C1-CLASSNAME2 D1-CLASSNAME3 E1-CLASSNAME4 F1-CLASSNAME5 ... --> many columns
A2 NAME1
A3 NAME2 These cells are supposed to contain GRADES retreived from a separate dataset
A4 NAME3
...no duplicates
in this column

REFERENCE DATA COMES FROM THREE COLUMNS: 1. NAME, 2. CLASSNAME, 3. GRADE
--- There are many thousands of NAMES, hundreds of CLASSNAMES, and thousands of GRADES.
--- NAME1, NAME2...repeats for multiple classes, but each possible CLASSNAME for a given NAME can have only one GRADE

GOAL: TO PASTE A UNIQUE GRADE INTO EACH CELL WHERE: NAME1=CLASSNAME1... GRADE is positioned directly to the right of NAME and CLASSNAME in the reference dataset

NOTE: INDEX & MATCH only retreives the first occurrence of a TRUE event in each row, thus it does not work. However, COUNTIFS WORKS, but does not place GRADE in the cell, only the count.

Please help if you can. Thank you sooo much!

 

[DanteAmor]

Welcome to the forum!
Sorry if I misunderstood.
I present 2 options.

Option 1: Sheet A has data and we fill sheet B.

Book1
	A	B	C	D	E
1	NAME	CLASSNAME1	CLASSNAME2	CLASSNAME3	CLASSNAME4
2	Name1	1	2	3	4
3	Name2	5	6	7	8
4	Name3	9	10	11	12
5	Name4	13	14	15	16
6	Name5	17	18	19	20
7	Name6	21	22	23	24
8	Name7	25	26	27	28
SheetA

Book1
	A	B	C
1	NAME	CLASS	GRADE
2	Name1	CLASSNAME1	1
3	Name2	CLASSNAME2	6
4	Name3	CLASSNAME3	11
5	Name1	CLASSNAME4	4
6	Name2	CLASSNAME1	5
7	Name3	CLASSNAME2	10
8	Name1	CLASSNAME3	3
9	Name2	CLASSNAME4	8
10	Name3	CLASSNAME3	11
11	Name8	CLASSNAME4
SheetB

Cell Formulas
Range	Formula
C2:C11	C2	=IFERROR(INDEX(SheetA!$B$2:$E$8,MATCH(A2,SheetA!$A$2:$A$8,0),MATCH(B2,SheetA!$B$1:$E$1,0)),"")

________________________________________________________________
Option 2: Sheet Z has data and we fill sheet Y.

Book1
	A	B	C
1	NAME	CLASS	GRADE
2	Name1	CLASSNAME1	1
3	Name2	CLASSNAME2	6
4	Name3	CLASSNAME3	11
5	Name1	CLASSNAME4	4
6	Name2	CLASSNAME1	5
7	Name3	CLASSNAME2	10
8	Name1	CLASSNAME3	3
9	Name2	CLASSNAME4	8
10	Name3	CLASSNAME3	11
11	Name8	CLASSNAME4	15
SheetZ

Book1
	A	B	C	D	E
1	NAME	CLASSNAME1	CLASSNAME2	CLASSNAME3	CLASSNAME4
2	Name1	1		3	4
3	Name2	5	6		8
4	Name3		10	11
5	Name4
6	Name5
7	Name6
8	Name7
SheetY

Cell Formulas
Range	Formula
B2:E8	B2	=IFERROR(INDEX(SheetZ!$C$2:$C$11,MATCH($A2&"|"&B$1,INDEX(SheetZ!$A$2:$A$11&"|"&SheetZ!$B$2:$B$11,0),0)),"")

 

[John 314]

Thank you so much, DanteAmor. That worked! My final formula:

‘=IFERROR(INDEX(hh!$C$2:$C$3843,MATCH($A2&"|"&B$1,INDEX(hh!$A$2:$A$3843&"|"&hh!$B$2:$B$3843,0),0)),"")

Cheers!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.