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
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="class: xl64, width: 141"]Vendor
ID[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Max Manual Audit Date[/TD]
[TD]Max of EtQ Audit Date[/TD]
[TD][TABLE="width: 147"]
<tbody>[TR]
[TD="class: xl66, width: 147"]Manual
Rating[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 119"]
<tbody>[TR]
[TD="class: xl66, width: 119"]EtQ
Rating[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2900000909[/TD]
[TD][TABLE="width: 169"]
<tbody>[TR]
[TD="class: xl66, width: 169, align: right"]2018-06-01[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 184"]
<tbody>[TR]
[TD="class: xl66, width: 184, align: right"]2016-06-01[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Quality Tab[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Vendor ID[/TD]
[TD]Manual Date[/TD]
[TD]EtQ Date[/TD]
[TD]Manual Rating[/TD]
[TD]EtQ Rating[/TD]
[/TR]
[TR]
[TD]2900000909[/TD]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="class: xl66, width: 141, align: right"]2018-06-01[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl67, width: 111, align: right"]2016-06-01[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 99"]
<tbody>[TR]
[TD="class: xl66, width: 99, align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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.