First of all thank you for all the work you're doing on supporting legacy file formats in LibreOffice! I'm working in the digital preservation domain, and the availability of open source software for these formats really helps us to ensure that digital materials in our collection will remain accessible in the future.

## Summary

This week I did some tests with an old Quattro Pro for DOS (5.0) file that uses the @VLOOKUP function. Loading this file in LibreOffice Calc revealed two interrelated issues:

1. A compatibility issue between this function and Calc's corresponding VLOOKUP function.
2. Unexpected behaviour of Calc's handling of static/cached values (not sure what's the proper terminology for this?) of formula results.

I personally don't think it would be feasible to fix 1. However, 2. is something that I think could be addressed, and once implemented this would also mitigate the effects of 1.

Below is a detailed report on these two related issues (BTW I couldn't figure out how to upload multiple attachments here, so instead I'm linking to a Github repo where I put the relevant files).

## LibreOffice version

Initially I used version 6.4.7.2 on Linux Mint (also shown in linked screenshots), afterwards double-checked and confirmed results with latest 25.3.2.3 version on a Windows 11 machine.

## Test file

Here:

<https://github.com/openpreserve/format-corpus/raw/refs/heads/master/office/spreadsheet/wq2/vlookup-compat-demo/TESTLU.WQ2>

I created this in Quattro Pro for DOS 5.0.

## VLOOKUP issue

The top 11 rows contain a 5-column block of data. This is used as a @VLOOKUP data table in row 15.

These data are then queried with Quattro Pro's @VLOOKUP function, using the following formulas in cells B15, C15, D15 and E15, respectively:

```
@VLOOKUP($A$15,$A$2..$E$11,1)
@VLOOKUP($A$15,$A$2..$E$11,2)
@VLOOKUP($A$15,$A$2..$E$11,3)
@VLOOKUP($A$15,$A$2..$E$11,4)
```

Here's what this looks like in Quattro Pro for DOS 5.0 (here running in DOSBox-X):

<https://github.com/openpreserve/format-corpus/blob/master/office/spreadsheet/wq2/vlookup-compat-demo/qp-vlookup-demo.png>

So all values are as expected.

Opening the same file in LibreOffice Calc initially results in:

<https://github.com/openpreserve/format-corpus/blob/master/office/spreadsheet/wq2/vlookup-compat-demo/lo-init.png>

Note how the values in cells D15 and E15 are different from the original rendering in Quattro Pro (the values in B15 and C15 are correct, but more about that later).

When I re-calculate the spreadsheet using "Data/Calculate/Recalculate Hard"[^1], the cell values change to:

<https://github.com/openpreserve/format-corpus/blob/master/office/spreadsheet/wq2/vlookup-compat-demo/lo-recalc.png>

And now the pattern is clear: the values returned by Calc's VLOOKUP function are shifted by exactly one column to the right.

It seems that Quattro Pro's @VLOOKUP function and Calc's VLOOKUP function each treat the data block geometry in slightly different ways: in Quattro Pro, the number of the first column in the data block is 0, whereas it is 1 in LibreOffice Calc. The result is that a Quattro Pro spreadsheet cell that uses the @VLOOKUP function will results in a result that is shifted by exactly one column. Although I haven't explicitly tested this, I'm assuming that other functions such as Quattro Pro's @HLOOKUP function are also affected by this.

## Static/cached values issue

Calc's rendering *before* the recalculate operation makes this behaviour slightly more puzzling, since some of the displayed cell values are in fact correct! After some additional tests, it seems that these correct values are "static" or cached values of the formulas that are stored for these cells in the WQ2 file. In both cases these are numbers. Both "wrong" cells originally returned a text string.

Although I'm not entirely sure on this, my best guess is that on opening, Calc tries to render the static/cached values, without any recalculation. This would be entirely sensible for legacy formats with functions that may not be 100% compatible with Calc. However, it seems that this doesn't work for all data types. In this example, it obviously works as expected for numbers, but not for text strings (I don't know about other data types)[^2]. It seems that Calc then automatically recalculates all cells for which this fails, resulting in a mix of cells that are/aren't recalculated. Since in this case the recalculated cells contain a formula that is incompatible with the equivalent Quattro Pro formula, this then results in the unexpected values.

## Possible solution

Given the sheer number of legacy spreadsheet formats that Calc supports, and the number of functions for each of these formats, I don't think it would be viable (or even useful) to fix the compatibility issue itself, because there are probably countless similar ones. But its effects could be mitigated by addressing the second issue, specifically: 

1. By adding read support for static/cached formula values that are not numbers (text strings, possibly other types as well?)

2. By reconsidering the current recalculation behaviour on opening a file. If no static/cached value can be retrieved, I think Calc shouldn't use a recalculated value as a fallback, but instead just show a `#REF` value. (Of course, after this it's up to the user if they want to manually force a recalculation or not.)

[^1]: Incidentally the Shift-Ctrl-F9 keyboard shortcut doesn't seem to work in the 25.3.2.3 release (it works fine 6.4.7.2)

[^2]: I initially wondered if the static/cached values might be missing from the WQ2 file altogether. Based on some additional tests on a WQ2 with external references I'm pretty sure this isn't the case.