INDEX MATCH wrong value

SH1988

New Member
Joined
Sep 6, 2019
Messages
1
Hi need your help with the func that return the wrong numbers for part of my cells.
my func :
{=INDEX($C$5:$C$18044,MATCH(MAX(IF($B$5:$B$18044=B6,$E$5:$E$18044)),$E$5:$E$18044,0))}
My Data looks like this:

MailingTypeMailingJNReport DateMailingDate W/E Issue Count By Date date of the highest Issue count
ADD InqM26809/15/201809/14/201810/06/2018
ADD InqM26809/22/201809/14/2018 410/06/2018
ADD InqM26809/29/201809/14/2018 13210/06/2018
ADD InqM26810/06/201809/14/2018 29010/06/2018
ADD InqM26810/13/201809/14/2018 14810/06/2018
ADD InqM26810/20/201809/14/2018 7710/06/2018
ADD InqM26810/27/201809/14/2018 3310/06/2018
ADD InqM26811/03/201809/14/2018 1810/06/2018
ADD InqM26811/10/201809/14/2018 2810/06/2018
ADD InqM26811/17/201809/14/2018 1210/06/2018


<colgroup><col><col><col span="2"><col><col></colgroup><tbody>
</tbody>
JONAIL52904/21/201804/20/201806/23/201864
JONAIL52904/28/201804/20/2018 306/23/201864
JONAIL52905/05/201804/20/2018 1106/23/201864
JONAIL52905/12/201804/20/2018 1206/23/201864
JONAIL52905/19/201804/20/2018 1706/23/201864
JONAIL52905/26/201804/20/2018 906/23/201864
JONAIL52906/02/201804/20/2018 406/23/201864
JONAIL52906/09/201804/20/2018 - 06/23/201864
JONAIL52906/16/201804/20/2018 - 06/23/201864
JONAIL52906/23/201804/20/2018 - 06/23/201864

<colgroup><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,328
Welcome to the MrExcel Board!

The formula as presented can return incorrect results if there is a tie on the Issue Count in different MailingJN's. Try:

<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 /><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><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</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><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="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><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</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><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">MailingType</td><td style=";">MailingJN</td><td style=";">Report Date</td><td style=";">MailingDate W/E</td><td style=";">Issue Count By Date</td><td style=";">date of the highest Issue count</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">ADD Inq</td><td style=";">M268</td><td style="text-align: right;;">9/15/2018</td><td style="text-align: right;;">9/14/2018</td><td style="text-align: right;;"></td><td style="text-align: right;;">10/6/2018</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">ADD Inq</td><td style=";">M268</td><td style="text-align: right;;">9/22/2018</td><td style="text-align: right;;">9/14/2018</td><td style="text-align: right;;">4</td><td style="text-align: right;;">10/6/2018</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">ADD Inq</td><td style=";">M268</td><td style="text-align: right;;">9/29/2018</td><td style="text-align: right;;">9/14/2018</td><td style="text-align: right;;">132</td><td style="text-align: right;;">10/6/2018</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">ADD Inq</td><td style=";">M268</td><td style="text-align: right;;">10/6/2018</td><td style="text-align: right;;">9/14/2018</td><td style="text-align: right;;">290</td><td style="text-align: right;;">10/6/2018</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">ADD Inq</td><td style=";">M268</td><td style="text-align: right;;">10/13/2018</td><td style="text-align: right;;">9/14/2018</td><td style="text-align: right;;">148</td><td style="text-align: right;;">10/6/2018</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">ADD Inq</td><td style=";">M268</td><td style="text-align: right;;">10/20/2018</td><td style="text-align: right;;">9/14/2018</td><td style="text-align: right;;">77</td><td style="text-align: right;;">10/6/2018</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">ADD Inq</td><td style=";">M268</td><td style="text-align: right;;">10/27/2018</td><td style="text-align: right;;">9/14/2018</td><td style="text-align: right;;">33</td><td style="text-align: right;;">10/6/2018</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">ADD Inq</td><td style=";">M268</td><td style="text-align: right;;">11/3/2018</td><td style="text-align: right;;">9/14/2018</td><td style="text-align: right;;">18</td><td style="text-align: right;;">10/6/2018</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">ADD Inq</td><td style=";">M268</td><td style="text-align: right;;">11/10/2018</td><td style="text-align: right;;">9/14/2018</td><td style="text-align: right;;">28</td><td style="text-align: right;;">10/6/2018</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">ADD Inq</td><td style=";">M268</td><td style="text-align: right;;">11/17/2018</td><td style="text-align: right;;">9/14/2018</td><td style="text-align: right;;">12</td><td style="text-align: right;;">10/6/2018</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="color: #333333;;"></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;;">#N/A</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">JONAI</td><td style=";">L529</td><td style="text-align: right;;">4/21/2018</td><td style="text-align: right;;">4/20/2018</td><td style="text-align: right;;"></td><td style="text-align: right;;">5/19/2018</td><td style="text-align: right;;">64</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">JONAI</td><td style=";">L529</td><td style="text-align: right;;">4/28/2018</td><td style="text-align: right;;">4/20/2018</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5/19/2018</td><td style="text-align: right;;">64</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">JONAI</td><td style=";">L529</td><td style="text-align: right;;">5/5/2018</td><td style="text-align: right;;">4/20/2018</td><td style="text-align: right;;">11</td><td style="text-align: right;;">5/19/2018</td><td style="text-align: right;;">64</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">JONAI</td><td style=";">L529</td><td style="text-align: right;;">5/12/2018</td><td style="text-align: right;;">4/20/2018</td><td style="text-align: right;;">12</td><td style="text-align: right;;">5/19/2018</td><td style="text-align: right;;">64</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">JONAI</td><td style=";">L529</td><td style="text-align: right;;">5/19/2018</td><td style="text-align: right;;">4/20/2018</td><td style="text-align: right;;">17</td><td style="text-align: right;;">5/19/2018</td><td style="text-align: right;;">64</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style=";">JONAI</td><td style=";">L529</td><td style="text-align: right;;">5/26/2018</td><td style="text-align: right;;">4/20/2018</td><td style="text-align: right;;">9</td><td style="text-align: right;;">5/19/2018</td><td style="text-align: right;;">64</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style=";">JONAI</td><td style=";">L529</td><td style="text-align: right;;">6/2/2018</td><td style="text-align: right;;">4/20/2018</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5/19/2018</td><td style="text-align: right;;">64</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style=";">JONAI</td><td style=";">L529</td><td style="text-align: right;;">6/9/2018</td><td style="text-align: right;;">4/20/2018</td><td style=";">-</td><td style="text-align: right;;">5/19/2018</td><td style="text-align: right;;">64</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style=";">JONAI</td><td style=";">L529</td><td style="text-align: right;;">6/16/2018</td><td style="text-align: right;;">4/20/2018</td><td style=";">-</td><td style="text-align: right;;">5/19/2018</td><td style="text-align: right;;">64</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style=";">JONAI</td><td style=";">L529</td><td style="text-align: right;;">6/23/2018</td><td style="text-align: right;;">4/20/2018</td><td style=";">-</td><td style="text-align: right;;">5/19/2018</td><td style="text-align: right;;">64</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)">F5</th><td style="text-align:left">{=INDEX(<font color="Blue">$C$5:$C$18044,MATCH(<font color="Red">B5&"|"&MAX(<font color="Green">IF(<font color="Purple">$B$5:$B$18044=B5,$E$5:$E$18044</font>)</font>),$B$5:$B$18044&"|"&$E$5:$E$18044,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 />
 

Watch MrExcel Video

Forum statistics

Threads
1,111,703
Messages
5,541,302
Members
410,545
Latest member
Upsindustrial20
Top