Formula Edit to Return Max Value

Alex O

Active Member
Joined
Mar 16, 2009
Messages
343
Office Version
  1. 365
Platform
  1. Windows
I'm using the formula below to return a list of comments from Sheet2 (Comments) in my workbook, and it's working flawlessly. But I'm stumped on how I get have it return the most recent date. So using the example below, for client 574643 it would only return the 1/16/2017 entry.

Any help is appreciated!


=IF(ROWS(Comments!$A$5:B5)>COUNTIF(Comments!$A$5:$A$2102,Report!$BB$11),"",INDEX(Comments!$E$5:$E$2102,SMALL(INDEX((Comments!$A$5:$A$2102=Report!$BB$11)*(ROW(Comments!$A$5:$A$2102)-ROW(Comments!$A$5)+1),),COUNTIF(Comments!$A$5:$A$2102,"<>"&Report!$BB$11)+ROWS(Comments!$A$5:B5))))

A B C D
574643365384981/4/2017OF DAYS. OK TO CHECK BACK WITH HER NEXT WEEK.
574643365384991/4/2017E/M CLT
574643365568601/6/2017PER CLT (A C) - LAST PAYMENT FOR $3681.98
574643365568611/6/2017RECEIVED ON 7/29/16
574643365749811/10/2017PER CLT - PER SPREADSHEET - BAL
574643365749821/10/201710808.86
574643365749851/10/2017MADE PMT
574643365749931/10/2017(574643-1)PRINCIPAL ADJUSTMENT: ADJ DOWN $12,280.10 TO
574643365749941/10/2017READ $10,808.86
574643365976151/12/2017CALLED #0621 - ELLEN VM LM
574643365976161/12/2017AUTOMATIC STATUS CHANGE FROM PPA TO 168.
574643366160231/16/2017CALLED #0621 - VM LM ELLEN
5746443648435912/27/2016THE CHARLOTTE OBSERVER
<colgroup><col width="49" style="width: 37pt; mso-width-source: userset; mso-width-alt: 1792;"> <col width="63" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2304;"> <col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;"> <col width="401" style="width: 301pt; mso-width-source: userset; mso-width-alt: 14665;"> <tbody> </tbody>
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

63falcondude

Well-known Member
Joined
Jan 15, 2016
Messages
3,572
Maybe something like this?

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">574643</td><td style="text-align: right;;">36538498</td><td style="text-align: right;;">1/4/2017</td><td style=";">OF DAYS. OK TO CHECK BACK WITH HER NEXT WEEK.</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">574643</td><td style="text-align: right;;">36538499</td><td style="text-align: right;;">1/4/2017</td><td style=";">E/M CLT</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">574643</td><td style="text-align: right;;">36556860</td><td style="text-align: right;;">1/6/2017</td><td style=";">PER CLT (A C) - LAST PAYMENT FOR $3681.98</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">574643</td><td style="text-align: right;;">36556861</td><td style="text-align: right;;">1/6/2017</td><td style=";">RECEIVED ON 7/29/16</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">574643</td><td style="text-align: right;;">36574981</td><td style="text-align: right;;">1/10/2017</td><td style=";">PER CLT - PER SPREADSHEET - BAL</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">574643</td><td style="text-align: right;;">36574982</td><td style="text-align: right;;">1/10/2017</td><td style="text-align: right;;">10808.86</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">574643</td><td style="text-align: right;;">36574985</td><td style="text-align: right;;">1/10/2017</td><td style=";">MADE PMT</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">574643</td><td style="text-align: right;;">36574993</td><td style="text-align: right;;">1/10/2017</td><td style=";">(574643-1)PRINCIPAL ADJUSTMENT: ADJ DOWN $12,280.10 TO</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">574643</td><td style="text-align: right;;">36574994</td><td style="text-align: right;;">1/10/2017</td><td style=";">READ $10,808.86</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">574643</td><td style="text-align: right;;">36597615</td><td style="text-align: right;;">1/12/2017</td><td style=";">CALLED #0621 - ELLEN VM LM</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">574643</td><td style="text-align: right;;">36597616</td><td style="text-align: right;;">1/12/2017</td><td style=";">AUTOMATIC STATUS CHANGE FROM PPA TO 168.</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">574643</td><td style="text-align: right;;">36616023</td><td style="text-align: right;;">1/16/2017</td><td style=";">CALLED #0621 - VM LM ELLEN</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">574644</td><td style="text-align: right;;">36484359</td><td style="text-align: right;;">12/27/2016</td><td style=";">THE CHARLOTTE OBSERVER</td></tr><tr ><td style="color: #161120;text-align: center;">14</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: #161120;text-align: center;">15</td><td style=";">Client</td><td style=";">Comment</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">574643</td><td style=";">CALLED #0621 - VM LM ELLEN</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">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: #FFFFFF" ><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: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><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: #DAE7F5;color: #161120">B16</th><td style="text-align:left">{=INDEX(<font color="Blue">D1:D13,MATCH(<font color="Red">A16&MAX(<font color="Green">IF(<font color="Purple">A1:A13=A16,C1:C13</font>)</font>),A1:A13&C1:C13</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 />
 

MARZIOTULLIO

Well-known Member
Joined
Aug 22, 2015
Messages
767
A
B
C
D
E
F
G
H
I
1
574643​
36538498​
1/4/2017​
OF DAYS. OK TO CHECK BACK WITH HER NEXT WEEK.max DATE
2
574643​
36538499​
1/4/2017​
E/M CLT
574643​
1/16/2017​
3
574643​
36556860​
1/6/2017​
PER CLT (A C) - LAST PAYMENT FOR $3681.98
574644​
12/27/2016​
4
574643​
36556861​
1/6/2017​
RECEIVED ON 7/29/16
5
574643​
36574981​
1/10/2017​
PER CLT - PER SPREADSHEET - BAL
6
574643​
36574982​
1/10/2017​
10808.86​
7
574643​
36574985​
1/10/2017​
MADE PMT
8
574643​
36574993​
1/10/2017​
(574643-1)PRINCIPAL ADJUSTMENT: ADJ DOWN $12,280.10 TO
9
574643​
36574994​
1/10/2017​
READ $10,808.86
10
574643​
36597615​
1/12/2017​
CALLED #0621 - ELLEN VM LM
11
574643​
36597616​
1/12/2017​
AUTOMATIC STATUS CHANGE FROM PPA TO 168.
12
574643​
36616023​
1/16/2017​
CALLED #0621 - VM LM ELLEN
13
574644​
36484359​
12/27/2016​
THE CHARLOTTE OBSERVER

<tbody>
</tbody>



I2=
MAX(IF($A$1:$A$13=$H2,$C$1:$C$13)) control+shift +enter copy down

format coulumn I as date
 

Alex O

Active Member
Joined
Mar 16, 2009
Messages
343
Office Version
  1. 365
Platform
  1. Windows
It works perfect for returning the max date, but it returns 0 when trying to retrieve the comment

=MAX(IF(Comments!$A$5:$A$2102=$BB$11,Comments!$D$5:$D$2102)))



A

B

C

D

E

F

G

H

I

1

574643​

36538498​

1/4/2017​
OF DAYS. OK TO CHECK BACK WITH HER NEXT WEEK.
max DATE

2

574643​

36538499​

1/4/2017​
E/M CLT

574643​

1/16/2017​

3

574643​

36556860​

1/6/2017​
PER CLT (A C) - LAST PAYMENT FOR $3681.98

574644​

12/27/2016​

4

574643​

36556861​

1/6/2017​
RECEIVED ON 7/29/16

5

574643​

36574981​

1/10/2017​
PER CLT - PER SPREADSHEET - BAL

6

574643​

36574982​

1/10/2017​

10808.86​

7

574643​

36574985​

1/10/2017​
MADE PMT

8

574643​

36574993​

1/10/2017​
(574643-1)PRINCIPAL ADJUSTMENT: ADJ DOWN $12,280.10 TO

9

574643​

36574994​

1/10/2017​
READ $10,808.86

10

574643​

36597615​

1/12/2017​
CALLED #0621 - ELLEN VM LM

11

574643​

36597616​

1/12/2017​
AUTOMATIC STATUS CHANGE FROM PPA TO 168.

12

574643​

36616023​

1/16/2017​
CALLED #0621 - VM LM ELLEN

13

574644​

36484359​

12/27/2016​
THE CHARLOTTE OBSERVER

<tbody>
</tbody>



I2=
MAX(IF($A$1:$A$13=$H2,$C$1:$C$13)) control+shift +enter copy down

format coulumn I as date
 

63falcondude

Well-known Member
Joined
Jan 15, 2016
Messages
3,572

ADVERTISEMENT

It works perfect for returning the max date, but it returns 0 when trying to retrieve the comment

=MAX(IF(Comments!$A$5:$A$2102=$BB$11,Comments!$D$5:$D$2102)))

Did you see post #2?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,474
Members
414,070
Latest member
DuncanLucas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top