Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Discussion Options

- Subscribe to RSS Feed
- Mark Discussion as New
- Mark Discussion as Read
- Pin this Discussion for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

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

Labels:

9 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Apr 17 2021 09:33 AM

Hi,

Did you see that you mix the German XVERWEIS and the English XLOOKUP ?

-Hans

Did you see that you mix the German XVERWEIS and the English XLOOKUP ?

-Hans

best response confirmed by
daejoum* (New Contributor)*

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Apr 17 2021 11:29 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.