Index Match with Multiple Criteria and Named Ranges Only Works on Same Sheet

[kelseygueldalewis]

I have sheet 'Training Report' that lists employee names (first and last) in the first two columns and positions across the top in the first row.
I have another sheet 'Active Training' that records employee name(first and last), position, and date training started.

I want to use INDEX-MATCH to find the date training started in the 'Active Training' sheet from matching the first name, last name, and position from the 'Training Report' sheet, and report it in the 'Training Report' sheet.

Since I have multiple criteria, I am using the array formula for match (MATCH(1, (range1=criteria1)*(range2=criteria2)*(range3=criteria3),0).
I'm also using named ranges for the first name ('ActFirst'), last name ('ActLast'), position ('ActPos'), and start date ('ActStartDate').
So the formula looks more like =INDEX(ActStartDate, MATCH(1, (ActFirst=firstname)*(ActLast=lastname)*(ActPos=position),0).

When I plug this formula into the 'Training Report' sheet, it evaluates everything to 0. But if I set up the matrix from 'Training Report' in the 'Active Training' sheet, it produces the correct output. I'm guessing it's something to do with my named ranges, but I need some help.

[CK76]

Culprit is your ActStartDate.

When it's evaluated, it's referencing Column D of "Traning Report" and not "Active Traning".

If you are using Column D of "Active Training" then you need to use Sheet Name in your "D" string.

Ex:

Please Login or Register  to view this content.

Note: Same goes for all other named ranges.

[CARACALLA]

	A	B	C	D	E	F	G	H
1	FIRST	LAST	FIRST	SECOND	THIRD	FOURTH	FIFTH	SIXTH
2	Dave	A	1/1/2020
3	Dave	B			1/1/2020
4	Andy	B						1/1/2020
5	Heather	C					10/27/2020
6	Joseph	D				10/19/2020
7	Jill	F					10/10/2020
8	Mike	J			8/25/2020
9	Joe	M		8/23/2020
10	Sally	N	2/29/2020
11	Peter	O
12	Will	P
13	Jacob	R

Training Report


C2=IFERROR(INDEX('Active Training'!$D$2:$D$10,AGGREGATE(15,6,ROW('Active Training'!$D$2:$D$10)-ROW('Active Training'!$D$2)+1/('Training Report'!$A$2:$A$13&'Training Report'!$B$2:$B$13='Active Training'!$A2&'Active Training'!$B2)/('Active Training'!$C$2:$C$10='Training Report'!C$1),COLUMNS('Active Training'!$C$2:'Active Training'!$C2))),"")

Copy across and down

[kelseygueldalewis]

Beautiful! That fixed it - thank you!

[kelseygueldalewis]

Thanks Caracalla! I chose to use named ranges and indirect because the Active Training data gets updated so I need it to expand/contract as that information is added. I solved it with CK76's suggestion.

[CARACALLA]

change range

Active Training

c2=IF(C$1<>"",IFERROR(INDEX('Active Training'!$D$2:$D$1000,AGGREGATE(15,6,ROW('Active Training'!$D$2:$D$1000)-ROW('Active Training'!$D$2)+1/('Training Report'!$A$2:$A$1000&'Training Report'!$B$2:$B$1000='Active Training'!$A2&'Active Training'!$B2)/('Active Training'!$C$2:$C$1000='Training Report'!C$1),COLUMNS('Active Training'!$C$2:'Active Training'!$C2))),""),"")

copy across and down