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

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Req ID[/TD]
[TD]Job Title[/TD]
[TD]Region[/TD]
[TD]Candidate Name[/TD]
[TD]Start Date[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]Jobby Job[/TD]
[TD]NE[/TD]
[TD]AC Slater[/TD]
[TD]1/2/2018[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]Jobby Job[/TD]
[TD]SW[/TD]
[TD]Zach Morris[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]Jobby Job[/TD]
[TD]MW[/TD]
[TD]Kelly Kapowski[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr]
[tr][td]

1​

[/td][td]Req ID[/td][td]Job Title[/td][td]Region[/td][td]Candidate Name[/td][td]Start Date[/td][td][/td][td][/td][/tr]

[tr][td]

2​

[/td][td]1234[/td][td]Jobby Job[/td][td]NE[/td][td]AC Slater[/td][td]1/2/2018[/td][td][/td][td]AC Slater[/td][/tr]

[tr][td]

3​

[/td][td]1234[/td][td]Jobby Job[/td][td]SW[/td][td]Zach Morris[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]1234[/td][td]Jobby Job[/td][td]MW[/td][td]Kelly Kapowski[/td][td][/td][td][/td][td][/td][/tr]
[/table]
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...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr]
[tr][td]

1​

[/td][td]Req ID[/td][td]Job Title[/td][td]Region[/td][td]Candidate Name[/td][td]Start Date[/td][td][/td][td]

1234​

[/td][/tr]

[tr][td]

2​

[/td][td]1234[/td][td]Jobby Job[/td][td]NE[/td][td]AC Slater[/td][td]1/2/2018[/td][td][/td][td]Zach Morris[/td][/tr]

[tr][td]

3​

[/td][td]1234[/td][td]Jobby Job[/td][td]SW[/td][td]Zach Morris[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]1235[/td][td]Jobby Job[/td][td]MW[/td][td]Kelly Kapowski[/td][td]1/2/2018[/td][td][/td][td][/td][/tr]
[/table]
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)