question3.ods (22.1 KB)
I have a similar situation with this one, i tried something but didn’t work. Can you help please?
My try:
=XLOOKUP(B2&$C$1&$D$1,$J$2:$M$2&$J$3:$M$3&$I$4:$I$18,XLOOKUP(C2,$J$4:$M$18,$J$4:$M$18,1,-1,1,1))
I made another post, because the last one was a bit different.
Em seu arquivo, em D2, qual a resposta esperada?
In your file, in D2, what is the expected response?
@schiavinatto
the formula »tells« you the »expected«
OFFSET($I$3;MATCH($D$1;$I$4:$I$18;0);MATCH($B2;$J$2:$M$2;0);COUNTIF($I$4:$I$18;$D$1);2)Don’t ramble on if you don’t know what you’re talking about.
What is the expected value?
I can’t rely on the formula; it might be poorly formulated.
question3.ods (22.5 KB)
The d3 cell.
I colored the numbers in red to show what i mean; if the value isn’t in the table, to take the next smaller value; in this example 12 isn’t in the table, based on the conditions mentioned, so the formula should get the factor regarding the 10 value.
In my file the smallest value in the table is always 0, so no worries regarding this.
=VLOOKUP($C2 ; SORT( FILTER( FILTER( $J$4:$M$18 ; $I$4:$I$18=D$1 ) ; $J$2:$M$2=$B2)) ;2)
good luck!
Thank you, it works well together with an ifna function, but my problem is that functions like sort and filter are slow, and slow down the whole file. Is there any way to do it with functions that utilize concatenation?
Definitely… let me know when you find it
For example, how would you do it with xlookup?
I tried, but didn’t work, that’s why i’m asking.
I never would use concatenation nor XLOOKUP for THIS task! Dont twist my words.
by the way:
after this reply … Why the hell should I wasting MY TIME again and again ??
Maybe someone else would be kind to help then.
A simple example.
question333.ods (24,2 KB)
Of course, @Karolus’s offset formula would be more elegant.
Unfortunately, your solution is limited to 2 categories, and on my file there are 8.
Regarding @karolus , I like both his solutions, the problem is that i found out somewhere on the internet that some functions slow down the file. Is there a benchmark with all the functions regarding speed?
My file is pretty huge, and because of the beginning error i have to hit recalculate hard to see the result, which takes a couple of minutes.
This is the topic where i spoke about it:
https://ask.libreoffice.org/t/error-522-when-i-open-a-huge-file/137137
I have asked the AI about some functions:
Filter function
Offset function
nobody except @emy1 saw this famous-huge-file, therefore nobody except you can benchmark and probably speed up by reorganizing the data ??.
Also tried this way:
=XLOOKUP(XLOOKUP(B3&$C$1&$D$1,$J$2:$M$2&$J$3:$M$3&$I$4:$I$18,$J$4:$K$18,1,-1,1),$J$4:$K$18,$L$4:$M$18,1,-1,1)
This is what the AI said about my function:
function
The function gave a 504 error.
question33.ods (22.7 KB)
@karolus
I was talking about the functions, some take longer to process than others.
Regarding sharing the file, i can’t do that, confidential information.