2021 Archives by date, by thread · List index

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