Looking for a specific formula syntax [filter without FILTER]

English
#1

Hi, In Libreoffice calc I would like to look up the intrest rate that was applied on a bank account on a specific date and for a specific account holder. That intrest rate can be looked up in a base table with the dates on which the rates were modified.
Unfortunately I can’t use the new the FILTER function because my laptop is too outdated for the latest version in which this function was introduced.
Can anybody imagine any way to solve this? Maybe a vlookup inside of another and split the base table?

image
image803×377 46.8 KB

illustrate request.ods (20.5 KB)

#2

VLOOKUP needs criteria to the left, so add a column left of A as new A and concat your search criteria by formula
=B2&'_'&E2
Now simply concat also your search term for VLOOKUP()

#3

If you use a real filter you can maybe reduce your table instead. (A filter can also output in another area…) You may need a button to update this on change. So this is no “formula” as solution.

#6

The account holder and the hosting bank are attributes of an account. Your table stores a lot of repetetive data. Have a look at this:
ask117167.odb (28.2 KB)
Open the document, open the form.
List of accounts and interest rates of the currently selected account stored in two database tables “ACC” and “RATES”.
The report lists interest rates of each account with chart.
It is very easy to import database data into Calc and Writer.

#4

I found it; so the problem was that a simple vlookup function couldn’t work because the exact date value would generally not be in the base table. I found a workaround by looking for the last date in the base table, lower than the date for which I’m looking the intrest for, with the MAXIFS function, and then create a helper field in base table and query table, in which a vlookup function can be used.
illustrate request.ods (23.0 KB)

#5

I made some changes to your file.

  • The helper formula (now in column F), don’t includes the date.
  • The second helper formula (in column M) was simplified to =MAXIFS(D:D;F:F;I2&J2&K2;D:D;"<"&L2).
  • The formulas in columns M and N can be united, so the second helper column is not needed.
    illustrate request_LeroyG.ods (35.0 KB)
1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.