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/
> Privacy Policy: https://www.documentfoundation.org/privacy


-- 
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/
Privacy Policy: https://www.documentfoundation.org/privacy