Date: prev next · Thread: first prev next last
2021 Archives by date, by thread · List index


I have LO and am battling with understanding which formula to use as well as the syntax for that formula.

I am aware of the availability of vlookup, hlookup, Index/Match formula and have settled I think on the right one i.e. Index/Match

Below is a portion of my spreadsheet that is divided as below with a blank column between each year. What I want to calculate is the date the Max rain occurred. I am OK with the formula to obtain the MAX but I need help in constructing a formula to get the corresponding date.

I had though the best would be Index and Match but no matter how I enter it I cannot get the date listed under the Date column of 2020 or 2021, never mind actually retrieving the year from the same column as the date the originated.

=INDEX(a1:h14;MATCH(h3;a3:h3;0)) just gives me 01/01 for the first date under Highest Monthly

I have looked aver tutorial and their mothers trying to find out what ranges to insert into the Index(Match()) formula with ZERO success.

I am manually entering the dates under each year when the max value is revealed by my formula. Would love to have it automated but my entire spreadsheet covers over 400 rows and more than 52 columns resulting in 26 tabs of graphs from the Data sheet.

Is my data in the wrong order i.e. should the rainfall value column be before the Date it occurred?

I do not understand what ranges need to consist of when using Index/Match.

Can someone point me to a decent tutorial explaining the different terms i.e. Reference, Row, Column, Range, Search Criterion, Lookup array.

2020                    2021                    Highest Monthly 
Date    2020 Rain       Date    2021 Rain       Date            Rain
01/19   9,5             01/15   3               2020/01/19      9,5
02/16   1,5             02/14   3,5             2021/02/14      3,5
03/25   3,5             03/14   19              2021/03/14      19
04/11   20              04/26   7               2020/04/11      20
05/28   27,5            05/20   43              2021/05/20      43
06/11   26              #N/A    0                               0
07/09   85,5            #N/A    0                               0
08/28   35              #N/A    0                               0
09/02   21              #N/A    0                               0
10/28   15              #N/A    0                               0
11/06   25              #N/A    0                               0
12/26   2               #N/A    0                               0

If you want the entire spreadsheet it is available on direct request, but ultimately I would like to understand how it works.

This will at least enable to use the formula successfully on newer versions on LO.


To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
Privacy Policy:


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.