Index & Match Help (I think….)

[FuriousD]

Looking for some help, think I need to use a multiple criteria Index & Match. I've done it before but can't get it to work this time. Below is some dummy data

A	B	C	D	E
Req ID	Job Title	Region	Candidate Name	Start Date
1234	Jobby Job	NE	AC Slater	1/2/2018
1234	Jobby Job	SW	Zach Morris
1234	Jobby Job	MW	Kelly Kapowski

<tbody>
</tbody>

So above I have 3 candidates on 1 req. I want to pull into a summary page the candidate name who has been offered.

I tried [FONT=&quot]=INDEX(Report!D1:D4,MATCH[/FONT][FONT=&quot](Report![/FONT][FONT=&quot]A1&E1,[/FONT][FONT=&quot]Report!A1:A4&Report!E1:E4,0[/FONT][FONT=&quot])[/FONT]) but I'm getting 0 as a result. Any thoughts?
Something better than I&M (no vba please, I'm In HR).


TIA

 

[FDibbins]

If you will only have 1 date in E...

	A​	B​	C​	D​	E​	F​	G​
1​	Req ID	Job Title	Region	Candidate Name	Start Date
2​	1234	Jobby Job	NE	AC Slater	1/2/2018		AC Slater
3​	1234	Jobby Job	SW	Zach Morris
4​	1234	Jobby Job	MW	Kelly Kapowski

G2=INDEX($D$2:$D$4,MATCH(MAX($E$2:$E$4),$E$2:$E$4,90))

 

[FuriousD]

Thanks for the help. A couple of things...

1. How do you factor in column A (there are like ~200 different reqs with multiple candidates on most?). I think I need it to also factor req number (column A) in.
2. What does the 90 denote in your formula?

 

[FDibbins]

1. ooops missed that you needed to test the Req...

	A​	B​	C​	D​	E​	F​	G​
1​	Req ID	Job Title	Region	Candidate Name	Start Date		1234​
2​	1234	Jobby Job	NE	AC Slater	1/2/2018		Zach Morris
3​	1234	Jobby Job	SW	Zach Morris
4​	1235	Jobby Job	MW	Kelly Kapowski	1/2/2018

G2=INDEX($D$2:$D$4,MATCH(G1&" "&MAX($E$2:$E$4),INDEX($A$2:$A$4&" "&$E$2:$E$4,0)))

2. he 90 was a typo, meant it to be 0 (see above)