Help reaching the last match

[NADIE]

Hello,

The problem I’m facing is to get the last matching value from a table in a different file. Example and tests:

File 1 Table1:

A ____ B _____-- C
A1 1/1/20 text1
B1 2/1/20 text2
C1 3/1/20 text3
A1 4/1/20 text4
B1 5/1/20 text5
C1 6/1/20 text6

What I need:
File 2 Table 1

A ----- B — C

A1 4/1/20 text4

In MS excel i achieved that with a simple: =LOOKUP(2;1/(cell values))

But in LO I tried this and VLOOK but only to get the first result of A1 ( A1 1/1/20 text1),
The only formula working is an array with DESREF: [=DESREF(B$2;MAX(($A$2:$A$7=(valuecell)*ROW($B$2:$B$7))-2;0)

But this only work with data on the same file, when I waant to take it from another file seems like its not working…

Help please.

If more details are needed, dont worry to ask.

[erAck]

You seem to be using a Spanish UI (judging from your mention of DESREF for OFFSET). The LOOKUP() function in Spanish is BUSCAR(), and using that should work the same as in Excel =BUSCAR(2;1/(cell values)). However, some aspects of the LOOKUP() function to make that work are implemented only as of LibreOffice 6.2, so if your version is older and it doesn’t work that’s why. VLOOKUP()/BUSCARV() works differently and not in this context.

Btw, note that all (cell values) in this usage must be greater than 0.5 (or rather all 1/(cell values) must be smaller than 2) otherwise the search does not return the last element.

[NADIE]

First of all, thanks for your fast answer.

As you said, I’m using a Spanish UI (forgot to translate that DESREF/OFFSET). I’m working with a LibreOffice version 6.1.X at some machines and 5.4.7 at mine (not upgradable into a foreseable future). I tried the =LOOKUP(2;1/(cell values)) at other machines (those with higher LO versions) and there it returns a value, but not the desired one. It returns the same value as LOOKUPV, the first one.

I tried the OFFSET formula at the computers with the higher versions and it works properly, taking the info from the other files. To whom who may be interested in the array formula:

=OFFSET('filepath.$B$2;MAX(('Filepath.$A$2:$A$7=valuecell)*ROW('filepath.$B$2:$B$7))-2;0)

Again, thanks for your advice.

[erAck]

If LOOKUP() (or VLOOKUP() for that matter) returns a different value than expected then it’s either because the range-lookup finds a value >= the lookup value, or the lookup-range is not strictly sorted ascending.