[libreoffice-users] Calc Formula confusion an understanding needed Index/Match

Hi,

I have LO 7.0.6.2 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.

Regards
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