LOOKUP to XLOOKUP Migration

[daejoum]

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

1. it matches the given account $GD4 with row Konten!$G:$G and 
2. 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 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!

 

 

[#2]

Hi,
Did you see that you mix the German XVERWEIS and the English XLOOKUP ?
-Hans

[#3 · Accepted solution]

@daejoum 

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.

[#4]

Hi Hans, thank you, I corrrected it in the message. I forgot to change that for this message..

[#5]

@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!!

[#6]

@daejoum 

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.

[#7]

@Sergei Baklan 

@(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?

[#8]

@Peter Bartholomew 

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.

[#9]

@Sergei Baklan 

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.

[#10]

@Peter Bartholomew 

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.