Index Match Multiple Criteria - Two Tabs

elizabethCat

New Member
Joined
Jan 25, 2019
Messages
1
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 DateMax 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 IDManual DateEtQ DateManual RatingEtQ Rating
2900000909
2018-06-01

<tbody>
</tbody>
2016-06-01

<tbody>
</tbody>
2

<tbody>
</tbody>
0

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
3,981
Office Version
365, 2019, 2016
Platform
Windows
try this

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Audit Tab</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Vendor</td><td style=";">Max Manual Audit Date</td><td style=";">Max of EtQ Audit Date</td><td style=";">Manual</td><td style=";">EtQ</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">ID</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Rating</td><td style=";">Rating</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">2900000909</td><td style="text-align: right;;">01/06/2018</td><td style="text-align: right;;">01/06/2016</td><td style="text-align: right;background-color: #E2EFDA;;">3</td><td style="text-align: right;background-color: #E2EFDA;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Quality Tab</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Vendor ID</td><td style=";">Manual Date</td><td style=";">EtQ Date</td><td style=";">Manual Rating</td><td style=";">EtQ Rating</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">2900000909</td><td style="text-align: right;;">01/05/2018</td><td style="text-align: right;;">01/05/2018</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">2900000909</td><td style="text-align: right;;">01/06/2018</td><td style="text-align: right;;">01/05/2018</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">2900000909</td><td style="text-align: right;;">01/06/2018</td><td style="text-align: right;;">01/06/2016</td><td style="text-align: right;;">4</td><td style="text-align: right;;">3</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D4</th><td style="text-align:left">{=INDEX(<font color="Blue">$D$10:$D$12,MATCH(<font color="Red">A4&"|"&B4,$A$10:$A$12&"|"&$B$10:$B$12,0</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E4</th><td style="text-align:left">{=INDEX(<font color="Blue">$E$10:$E$12,MATCH(<font color="Red">A4&"|"&C4,$A$10:$A$12&"|"&$C$10:$C$12,0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

EFANYoutube

Board Regular
Joined
May 19, 2017
Messages
52
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,085
Messages
5,466,540
Members
406,486
Latest member
varmehta

This Week's Hot Topics

Top