grab max date from list

[jenn]

I have several columns of data. The most important being a client name and
their last date of service. Can I pivot and pull the patient name and only
grab their last date of service, or is there a vlookup that will grab the
most recent date?

 

[Biff]

Hi!

Why pull and grab when you can yank! <g>

Client names in column A
Dates in column G

Entered as an array with the key combo of CTRL,SHIFT,ENTER:

=MAX(IF(A1:A100="Client_Name",G1:G100))

Format the cell as DATE

Biff

 

[Ken Wright]

=SUMPRODUCT(MAX((RngNames=E1)*(RngDates)))

 

[jenn]

I must not have something quite right... when I follow the instructions my
column fills with the formula... =MAX(IF(A1:A100="Client_Name",G1:G100))

 

[Ragdyer]

Sounds like your cells were probably formatted as Text *before* you entered
the formula.

To check if this is so, select one of the "bad" cells displaying the
formula, and then:

<Ctrl> <Shift. <~>
Then <F2>
Then <Enter>

What this does is use a keyboard shortcut to format the cell to General,
then re-enter the formula.

If this *doesn't* work, another possibility is that you're in "formula view
mode".

To toggle in and out of this view, hold <Ctrl>, and then hit the key that
has the <~> on it.