I have a table that looks like this:
1.ods (37.2 KB)
On my file, the list goes for the whole year, beginning from 1st December 2025 until 30th November 2026.
How can i get on which date was the minimum and maximum value for each column? I got the min and max, but I can’t get on which date happened, vlookup and hlookup don’t work.
Can you help me please? thank you.
Hi @emy1, here’s an example, but if there are two dates with the same value, it returns the first one found.
1_GS.ods (26,4,KB)
Does xlookup replace index-match?
=XLOOKUP(B36,B$2:B$33,$A$2:$A$33)
=INDEX(A2:A33,MATCH(B36,B2:B33,0))@emy1
If you encounter multiple dates matching min max, a standard filter would work:
.
Data > More Filters > Standard Filter
.
If your data is not static, you would need the hullabaloo discussed here:
.
https://ask.libreoffice.org/t/dynamic-list-of-unique-cells-in-a-column/131249
@clear I need my list of days to remain intact, i want just to extract a number from a table.
With the Standard Filter approach …
.
Data > More Filters > Standard Filter
.
… your list of days (and all the percents) would remain intact. The last step of Standard Filter is to select the cells where you want Calc to put your min or max dates.
.
I’d only recommend using the Standard Filter approach if you encounter multiple days on which the Max (or the Min) occurs.
.
If the max/min occur only on one day, then my SUMIFS approach is so much simpler.
@lodf2023 the index function gives only the first day, but i need all days.
The xlookup function is not even recognized by the program.
ask132592.odb (17.4 KB)
Open the document.
Open the form.
The upper grid shows the minimum and maximum of each group.
The pair of grids shows the related min record(s) and max record(s) in the table for the group selected in the upper grid.
=TEXT(SUMIFS(dt,val,MAX(val)),“yyyy-mm-dd”)
.
or if the cell is formatted as date
.
SUMIFS(dt,val,MAX(val))
dateminmax.ods (28.2 KB)
Thats a really clever solution, isnt it? 
… but what would you expect if there are two or more matches ?!
full dates summed up to something like 2277-11-29
OP specified singular, both in the wording and in the ods. The odd date would mean their premise was wrong, though I would agree that the OP didn’t phrase their question without other ambiguities.
And why did YOU suggest SUMIFS on data with single matches??
Why do SUMIF and SUMIFS have different argument orders? And why on earth is it called SUMIF if the IF args come before the SUM arg? Crazy. SUMIF should be called IFSUM. I just stick with SUMIFS, even if SUMIF would do. SUMIFS syntax is the more logical of the two and easily expandable.
I get the same #Name? error
Do you get #Name error with the file I attached ?
I do get the error when i try your function into my file.
Initially i didn’t think there can be more than one day with the minimum value, now i see that there can be.
.
Ok, if you want to plan on multiple days, then the SUMIFS approach is not appropriate.
.
The next question is, is your project data static? For example is it historical data (from last year for example) and will not be edited?
.
.
My SUMIFS function requires Calc to Name the columns. You are getting the error because you have not instructed Calc to Name to columns. But it is trivial to do that.
Amount1, Amount2, …A1 to F33.Sheet > Named Ranges and Expressions > Create
Create Names From popup, checkmark Top row
OK
Sheet > Named Ranges and Expressions > Manage
In the Manage Names popup, you will see a list of Names that Calc created for you. You can use in formulas in place of things like A2:A33. Most of the time the Names that Calc created for you will be identical to what you typed into row 1, but sometimes Calc will prefix the Name with an underscore. If so, then you must use the Name shown in the Manage Names including the underscore. If you don’t like the underscore, try typing in a different column label in row 1.
@clear
It will be edited since it regards also the future, but i edit it day by day.
On my file i have labels on all columns. The file uploaded was just for an example, i can’t upload my file.
Since your data is dynamic, I don’t have any ideas on how to accomplish your goals. I’m sorry. The Standard Filter approach that I mentioned previously …
.
Data > More Filters > Standard Filter
.
would be too burdensome, in my opinion, because you would need to repeat it every day. Perhaps it could be automated.
.
A few weeks ago, I asked essentially the same question as you are asking here. There was an extended discussion and proposals which I found too complicated for my needs. I’m not sure if anyone came up with a solution. You might (or might not) find it helpful. Here it is:
https://ask.libreoffice.org/t/dynamic-list-of-unique-cells-in-a-column/131249
It is not enough to have labels in row 1. You would need to create Names out them. Fortunately, that task is trivial. Follow the 6 steps I mentioned previously for Sheet > Named Ranges and Expressions > Create