Apr 17 2021 09:18 AM - edited Apr 17 2021 11:28 AM
Hello! I am currently using the following LOOKUP formula for my real estate managment spreadsheet
=LOOKUP(2;1/(Konten!$A:$A>=$EJ$3)/(Konten!$G:$G=$GD4)/(Konten!$F:$F="SOLL VZ HK");(Konten!$C:$C))
to look for a date in row Konten!A:A smaller than the date given in $EJ$3 under the condition that
to return the found value in Konten!$C:$C.
For instance: On April 17, 2021 ($EJ$3) for the account "Ly1.OGre Miramar Privat" ($GD4) it returns the value 75,81 which is the the value for April, 1, 2021 (april fools!) and the closest to April 17, 2021. I would always need the value of the date one instance earlier than the given date.
In the desparate need to speed up my spreadsheet with 16k rows of data (it takes over 1 minute to calculate) I wanted to speed things up and translate my LOOKUP formula into a XLOOKUP. I thought I would need a nested XLOOKUP similiar to Example Nr. 5 in here and came up with
=XLOOKUP(Tabelle1!EJ3;Konten!A:A;XLOOKUP(Tabelle1!GD3;Konten!G:G;XLOOKUP("SOLL VZ HK";Konten!F:F;Konten!C:C)))
but this gives me back an error and I dont understand why. Please help!
If you have solutions with MATCH or INDEX that would be faster than my existing LOOKUP, please don't hesitate to post some ideas. Thank you very much!
Apr 17 2021 09:33 AM
Apr 17 2021 10:36 AM
SolutionI'd try to use dynamic ranges instead like
=LOOKUP(2,1/
(Konten!$A$1:INDEX(Konten!$A:$A, COUNTA(Konten!$A:$A))>=$EJ$3)/
(Konten!$G$1:INDEX(Konten!$G:$G, COUNTA(Konten!$A:$A))=$GD4)/
(Konten!$F$1:INDEX(Konten!$F:$F, COUNTA(Konten!$A:$A))="SOLL VZ HK"),
(Konten!$C$1:INDEX(Konten!$C:$C, COUNTA(Konten!$A:$A)))
)
or, to simplify a bit
=LET(n, COUNTA(Konten!$A:$A),
LOOKUP(2,1/
(Konten!$A$1:INDEX(Konten!$A:$A, n ) >= $EJ$3)/
(Konten!$G$1:INDEX(Konten!$G:$G, n ) = $GD4)/
(Konten!$F$1:INDEX(Konten!$F:$F, n ) = "SOLL VZ HK"),
(Konten!$C$1:INDEX(Konten!$C:$C, n ))
)
Even better if you convert your range into structured table.
In general I'm not sure that XLOOKUP is faster than LOOKUP. In any case, since you have mix of exact and approximate criteria, I guess XLOOKUP shall be used the same way
=LET(n, COUNTA(Konten!$A:$A),
XLOOKUP(1,1/
(Konten!$A$1:INDEX(Konten!$A:$A, n ) >= $EJ$3)/
(Konten!$G$1:INDEX(Konten!$G:$G, n ) = $GD4)/
(Konten!$F$1:INDEX(Konten!$F:$F, n ) = "SOLL VZ HK"),
(Konten!$C$1:INDEX(Konten!$C:$C, n ))
)
As for the second formula, in example second XLOOKUP returns reference on the column (see third parameter). In your formula
=XLOOKUP(Tabelle1!EJ3;
Konten!A:A;
XVERWEIS(Tabelle1!GD3;
Konten!G:G;
XVERWEIS("SOLL VZ HK";
Konten!F:F;
Konten!C:C)
))
nested XLOOKUP returns reference on the cell where array is expected.
Apr 17 2021 11:29 AM
Apr 18 2021 09:34 AM - edited Apr 18 2021 09:44 AM
@Sergei Baklan Thank you very much Sergei!!!
I converted the table into a structured table and was able to implement your suggestion with some slight changes / help from here . It goes:
=LET(n; (COUNTA(Tabelle1[Datum]));
XLOOKUP(1;1/
(Tabelle1[[#Headers];[Datum]]:INDEX(Tabelle1[Datum]; n) >= [@DatumAnschreiben])/
(Tabelle1[[#Headers];[Konto]]:INDEX(Tabelle1[Konto]; n) = [@NameKonto])/
(Tabelle1[[#Headers];[Betreff]]:INDEX(Tabelle1[Betreff]; n) = "SOLL VZ NK");
(Tabelle1[[#Headers];[Betrag]]:INDEX(Tabelle1[Betrag]; n))))
Weirdly, I couldn't select the cells with the mouse, I had to type it.
The calculation is a bit faster, I'd say by 20%. But I also did some other simplifications, so it is hard to say...
Greetings from the Morsum, Island of Sylt, Germany!!
Apr 18 2021 12:01 PM
If you convereted range to table same formula will be much simplier:
XLOOKUP(1;1/
(Tabelle1[Datum] >= [@DatumAnschreiben])/
(Tabelle1[Konto] = [@NameKonto])/
(Tabelle1[Betreff] = "SOLL VZ NK");
(Tabelle1[Betrag])
)
And I'd try instead
INDEX(FILTER(Tabelle1[Betrag];
(Tabelle1[Datum] >= [@DatumAnschreiben])*
(Tabelle1[Konto] = [@NameKonto])*
(Tabelle1[Betreff] = "SOLL VZ NK")
);1)
perhaps it gives better result. INDEX() here is to return first element if FILTER() finds more than one which meets criteria.
Apr 18 2021 02:46 PM
@(FILTER(Tabelle1[Betrag];
(Tabelle1[Datum] >= [@DatumAnschreiben])*
(Tabelle1[Konto] = [@NameKonto])*
(Tabelle1[Betreff] = "SOLL VZ NK")
)
should do the same job, but perhaps that is a little too cryptic?
Apr 19 2021 01:17 AM
Exactly, but I do not rely on "@" and prefer to use straightforward INDEX().
Back to XLOOKUP(), copy/pasting from LOOKUP() I forgot to remove division, this variant
XLOOKUP(1;
(Tabelle1[Datum] >= [@DatumAnschreiben])*
(Tabelle1[Konto] = [@NameKonto])*
(Tabelle1[Betreff] = "SOLL VZ NK");
(Tabelle1[Betrag])
)
It does the same job as FILTER() but I'm not sure if any difference from performance point of view.
Apr 19 2021 01:09 PM
Eventually one settles to a preferred style but I rather like exploring options.
(i) Concise but cryptic, (ii) basic, (iii) concealed
On the timing issues, my first instinct was to back XLOOKUP since it is only looking for the first occurrence. Yet, in reality, I suspect the entire array of criteria is evaluated before XLOOKUP is called, then XLOOKUP is faced with an unsorted list so further processing is required.
Added note: Over 100,000 cells XLOOKUP was noticeably faster (about 30% say) but the variability from run to run was greater than the differences between methods.
Apr 19 2021 03:26 PM
Interesting. Tried to test as well (300 000 raw data x 1000 table with formulas)
Couple of each test, didn't see big difference between attempts. INDEX/MATCH works. INDEX/XMATCH gives the same, didn't include here.
Apr 17 2021 10:36 AM
SolutionI'd try to use dynamic ranges instead like
=LOOKUP(2,1/
(Konten!$A$1:INDEX(Konten!$A:$A, COUNTA(Konten!$A:$A))>=$EJ$3)/
(Konten!$G$1:INDEX(Konten!$G:$G, COUNTA(Konten!$A:$A))=$GD4)/
(Konten!$F$1:INDEX(Konten!$F:$F, COUNTA(Konten!$A:$A))="SOLL VZ HK"),
(Konten!$C$1:INDEX(Konten!$C:$C, COUNTA(Konten!$A:$A)))
)
or, to simplify a bit
=LET(n, COUNTA(Konten!$A:$A),
LOOKUP(2,1/
(Konten!$A$1:INDEX(Konten!$A:$A, n ) >= $EJ$3)/
(Konten!$G$1:INDEX(Konten!$G:$G, n ) = $GD4)/
(Konten!$F$1:INDEX(Konten!$F:$F, n ) = "SOLL VZ HK"),
(Konten!$C$1:INDEX(Konten!$C:$C, n ))
)
Even better if you convert your range into structured table.
In general I'm not sure that XLOOKUP is faster than LOOKUP. In any case, since you have mix of exact and approximate criteria, I guess XLOOKUP shall be used the same way
=LET(n, COUNTA(Konten!$A:$A),
XLOOKUP(1,1/
(Konten!$A$1:INDEX(Konten!$A:$A, n ) >= $EJ$3)/
(Konten!$G$1:INDEX(Konten!$G:$G, n ) = $GD4)/
(Konten!$F$1:INDEX(Konten!$F:$F, n ) = "SOLL VZ HK"),
(Konten!$C$1:INDEX(Konten!$C:$C, n ))
)
As for the second formula, in example second XLOOKUP returns reference on the column (see third parameter). In your formula
=XLOOKUP(Tabelle1!EJ3;
Konten!A:A;
XVERWEIS(Tabelle1!GD3;
Konten!G:G;
XVERWEIS("SOLL VZ HK";
Konten!F:F;
Konten!C:C)
))
nested XLOOKUP returns reference on the cell where array is expected.