INDEX and MATCH with multiple criteria is not returning required results

[Sean15]

Hi:

The formulas in range J26:L28 are not returning the required results. Could you help please?

INDEX & MATCH with multiple criteria.xlsx
	E	F	G	H	I	J	K	L
21	derek	exec	1250
22	mark	payroll	1425
23	paul	IT	5300
24
25						IT	exec	payroll
26					derek	#REF!	1250	#REF!
27					mark	#REF!	1425	#REF!
28					paul	#REF!	5300	#REF!
29
Sheet1

Cell Formulas
Range	Formula
J26:L28	J26	=INDEX($G$21:$G$23,MATCH($I26,$E$21:$E$23,0), MATCH(J$25,$F$21:$F$23,0))

 

[StephenCrump]

It's not clear what you're trying to do. What are your required results in J26:L28?

 

[Habtest]

Is this what you are looking for?

Book1
	E	F	G	H	I	J	K	L
21	derek	exec	1,250
22	mark	payroll	1,425
23	paul	IT	5,300
24
25						IT	exec	payroll
26					derek		1,250
27					mark			1,425
28					paul	5,300
Sheet1

Cell Formulas
Range	Formula
J26:L28	J26	=IFERROR(INDEX($G$21:$G$23,MATCH(1,($E$21:$E$23=$I26)*($F$21:$F$23=J$25),0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Sean15]

Is this what you are looking for?

Yes, but is a non array formula available? I don't want to use CTRL+SHIFT+ENTER to enter formula.

 

[Alex Blakenburg]

Yes, but is a non array formula available? I don't want to use CTRL+SHIFT+ENTER to enter formula.

Does this do what you had in mind ?

20210722 Lookup MultiCriteria.xlsx
	E	F	G	H	I	J	K	L	M
21	derek	exec	1250
22	mark	payroll	1425
23	paul	IT	5300
24
25						IT	exec	payroll
26					derek		1250
27					mark			1425
28					paul	5300
29
Sheet1

Cell Formulas
Range	Formula
J26:L28	J26	=IFERROR(LOOKUP(2,1/(($E$21:$E$23=$I26)*($F$21:$F$23=J$25)),$G$21:$G$23),"")

 

[plshelpexcel]

Perhaps this could also work for you:

Book2
	E	F	G	H	I	J	K	L
21	derek	exec	1250
22	mark	payroll	1425
23	paul	IT	5300
24
25						IT	exec	payroll
26					derek	-	1,250	-
27					mark	-	-	1,425
28					paul	5,300	-	-
Sheet1

Cell Formulas
Range	Formula
J26:L28	J26	=+SUMIFS($G$21:$G$23,$E$21:$E$23,$I26,$F$21:$F$23,J$25)

 

[Sean15]

J26:L28

J26

=+SUMIFS($G$21:$G$23,$E$21:$E$23,$I26,$F$21:$F$23,J$25)

Thank you very much. But why is there + before SUMIFS?

 

[plshelpexcel]

Thank you very much. But why is there + before SUMIFS?

You’re welcome! The + before SUMIFS is not needed.