Index Match Multiple Criteria - Two Tabs

[elizabethCat]

I am trying to do an index match. I want to match the Vendor ID and Max Manual Date in the Audit tab to the corresponding row in the Quality tab to get the Manual Rating and then to match the Vendor ID and Max of EtQ Audit Date to get the EtQ Rating. None of my formulas work. Can someone point me in the right direction? I'm tried several formulas on-line and I'm getting nowhere. Thank you!

Audit Tab

Vendor ID <tbody> </tbody>	Max Manual Audit Date	Max of EtQ Audit Date	Manual Rating <tbody> </tbody>	EtQ Rating <tbody> </tbody>
2900000909	2018-06-01 <tbody> </tbody>	2016-06-01 <tbody> </tbody>

<tbody>
</tbody>

Quality Tab

Vendor ID	Manual Date	EtQ Date	Manual Rating	EtQ Rating
2900000909	2018-06-01 <tbody> </tbody>	2016-06-01 <tbody> </tbody>	2 <tbody> </tbody>	0

<tbody>
</tbody>

 

[AlanY]

try this

Book1
	A	B	C	D	E
1	Audit Tab
2	Vendor	Max Manual Audit Date	Max of EtQ Audit Date	Manual	EtQ
3	ID			Rating	Rating
4	2900000909	01/06/2018	01/06/2016	3	3
5
6
7
8	Quality Tab
9	Vendor ID	Manual Date	EtQ Date	Manual Rating	EtQ Rating
10	2900000909	01/05/2018	01/05/2018	2	1
11	2900000909	01/06/2018	01/05/2018	3	2
12	2900000909	01/06/2018	01/06/2016	4	3
Sheet2

Cell Formulas
Range	Formula
D4	{=INDEX($D$10:$D$12,MATCH(A4&"|"&B4,$A$10:$A$12&"|"&$B$10:$B$12,0))}
E4	{=INDEX($E$10:$E$12,MATCH(A4&"|"&C4,$A$10:$A$12&"|"&$C$10:$C$12,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[EFANYoutube]

Hi Elizabeth
The only way I can think of is to put an "index" column on the left of your table and have CONCATENATE(the vendor id,the date) as the index, you can hide this to make it more appealing. Then match on the index.