Forum Discussion
LOOKUP to XLOOKUP Migration
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
- it matches the given account $GD4 with row Konten!$G:$G and
- it machtes the given category name "SOLL VZ HK" with row Konten!$F:$F
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 https://support.microsoft.com/de-de/office/xverweis-funktion-b7fd680e-6d10-43e6-84f9-88eae8bf5929?ui=de-DE&rs=de-DE&ad=DE&fromAR=4 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!
I'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.
9 Replies
I'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.
SergeiBaklan 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 https://support.microsoft.com/en-us/office/using-structured-references-with-excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e . 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!!
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.
- Hi,
Did you see that you mix the German XVERWEIS and the English XLOOKUP ?
-Hans- Hi Hans, thank you, I corrrected it in the message. I forgot to change that for this message..
