2022 Archives by date, by thread · List index

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

```For the following I assumed the “simple date” column is is date format..
If not you can get to that by entering the formula =IFERROR(DATEVALUE(LEFT(\$RAW.A3;10));"") and
pull that down.

On another sheet I entered the “query date” in B2

The next formula gives you the max temp on that date:
=MAXIFS(\$’RAW data'.\$C\$2:\$C\$16;\$’RAW data'.B\$2:B\$16;"="&\$B\$1)

Hope this helps,
Rob

```
```Op 8 aug. 2022, om 21:25 heeft Hylton Conacher (ZR1HPC) <hylton@conacher.co.za> het volgende
geschreven:

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

--
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/
```
```

--
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/