Index Match Array

[xMIKExSMITHx]

1	A	B	C	D	E	F	G	H	I	J
2			2017	2018	2018	2018	2018		Year	2018
3		Employee	Dec	Jan	Feb	Mar	Apr		Month	Feb
4		Doe John	20.00	22.00	23.00	24.00	24.00		Employee	Jones Mike
5		Jones Mike	15.00	17.00	18.00	19.00	19.00		Rate	???

<tbody>
</tbody>

I have the above table (B2:G5) in my sheet. I want my index match to locate the rate of $18.00 for the employee based on the criteria set in J2-J4. I know the index match has to be set as an array, but no matter how I adjust the formula, I can't seem to tweak it correctly. This is what I have so far...

={INDEX(B:G,MATCH(1,(J2=C:G)*(J3=C:G)*(J4=B:B),0))}

 

[Weazel]

you don't really need to use index and match....assuming there aren't and duplicates you could use something like sumproduct to return the value which wouldn't require control shift enter

Unknown

	A	B	C	D	E	F	G	H	I
1		2017	2018	2018	2018	2018		Year	2018
2	Employee	Dec	Jan	Feb	Mar	Apr		Month	apr
3	Doe John	20	22	23	24	24		Employee	Jones Mike
4	Jones Mike	15	17	18	19	19		Rate	19

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula I4 =SUMPRODUCT((B1:F1=I1)*(B2:F2=I2)*(A3:A4=I3)*(B3:F4)) <tbody> </tbody>

<tbody>
</tbody>

 

[Aladin Akyurek]

In J5 control+shift+enter, not just enter:

=VLOOKUP(J$4,$B$4:$G$5,MATCH(J$3,IF($B$2:$G$2=J$2,$B$3:$G$3),0),0)

 

[xMIKExSMITHx]

Thank you both!!