2022 Archives by date, by thread · List index

# Re: [libreoffice-users] VLOOKUP Syntax to find MAX on a particular date

```On 8 Aug 2022 at 21:25, Hylton Conacher (ZR1HPC)
wrote:

Date sent:              Mon, 8 Aug 2022 21:25:44 +0200
To:                     LibreOffice Users
<users@global.libreoffice.org>
From:                   "Hylton Conacher (ZR1HPC)"
<hylton@conacher.co.za>
Subject:                [libreoffice-users] VLOOKUP Syntax to find
MAX on a particular date

```
```Hi,

Using LO Calc 7.3.2.2 and I have a 28 column table RAW Data table in
excess of 65k rows. A three column sample below:
Date                          Simple Date             Outdoor Temp
2022-01-01T00:05:00+02:00     2022-01-01 00:05:00     17.5
2022-01-01T00:00:00+02:00     2022-01-01 00:00:00     17.4

I have a secondary table, called Amended RAW Data containing just the 10
digit date and the corresponding value of the column value I am seeking,
whether it be the Outdoor Temp, Daily Rainfall etc

What I need to do is query the following table, searching for a specific
10 digit date, and find the max value on a specific date.

I have tried so many different itinerations on the VLOOKUP formula and
browsed many elementary Google VLOOKUP pages that I need help. I have
tried(all return N/A):

=MAX(VLOOKUP(LEFT(\$'RAW Data'.\$B\$2:\$B\$62037,10),\$'RAW
Data'.\$B\$2:\$L\$62037,1,FALSE))

=MAX(VLOOKUP(\$A11,\$'RAW Data'.\$B\$2:\$M\$62037,11))

=MAX(VLOOKUP(\$A11,\$'RAW Data'.\$B\$2:\$M\$62037,11))

=MAX(VLOOKUP(LEFT(\$'RAW Data'.\$B\$2:\$B\$62307,10)=\$'Amended RAW
Data'.\$A\$11,\$'RAW Data'.\$B\$2:\$L\$62037,11,0))

References to 'Amended RAW Data'.\$A\$11 refer to a date that I know has
greater than zero value, and the 11th column data I want to max, within
a date.

Help appreciated
Hylton
```
```
Not sure I understand you completely, but here is a test I
did using maxifs.

In cells A5-A8
Date

2022-01-01T00:05:00+02:00

2022-01-01T00:00:00+02:00

2022-01-02T00:00:00+02:00

In cells B5-B8
Simple Date

01/01/2022 12:05 AM

01/01/2022 12:00 AM

01/02/2022 12:00 AM

In cells C5-C8
Outdoor Temp

17.5

17.4

26

In cells E6-E7
01/01/22

01/02/22

In cells F6 and F7
=MAXIFS(\$C\$6:\$C\$8,\$B\$6:\$B\$8,">="&\$E6,\$B\$6:\$B\$8,"<"&(\$E6+1))
=MAXIFS(\$C\$6:\$C\$8,\$B\$6:\$B\$8,">="&\$E7,\$B\$6:\$B\$8,"<"&(\$E7+1))

Displayed values of 17.5 and 26
Using Maxifs to get the max of range with outdoor temp, but date needs to be >= date in E
and less than that +1 (next day)..

```
```
--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
```
```

+------------------------------------------------------------+
Michael D. Setzer II - Computer Science Instructor
(Retired)
mailto:mikes@guam.net
mailto:msetzerii@gmail.com
Guam - Where America's Day Begins
G4L Disk Imaging Project maintainer
http://sourceforge.net/projects/g4l/
+------------------------------------------------------------+

--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/