excelexcel-formulalookupopenoffice-calcgnumeric

Excel lookup formula not working in OpenOffice Calc

I recently converted a file named template.xlsx into template.ods using the command line utility called gnumeric. All formulas get converted correctly except this one:

=LOOKUP(2,1/(INDIRECT(CONCATENATE("Import!$F$",Q3,":$F$",M3))>=(S3)*VALUE(SUBSTITUTE($S$1,"LOOKUP FACTOR x",""))),INDIRECT(CONCATENATE("Import!$B$",Q3,":$B$",M3)))

This formula is quite long to some extent, but to keep it short I have 2 sheets, one called Import and the other Lookup. I want to return the last value in a specific range (and match its row in another range) that is greater than or equal to the value in S3 multiplied by the LOOKUP FACTOR x, e.g. if is LOOKUP FACTOR x2, the value in S3 is multiplied by 2.

I found that the way OpenOffice Calc accesses a range from another sheet is different to how Excel does, hence I rewrote the formula to:

=LOOKUP(2;1/(INDIRECT(CONCATENATE("$Import.$F$";Q3;":$F$";M3))>=(S3)*VALUE(SUBSTITUTE($S$1;"LOOKUP FACTOR x";"")));INDIRECT(CONCATENATE("$Import.$B$";Q3;":$B$";M3)))

Hereby changing the sheetname from Import!$F$ to $Import.$F$. The same for column $B$.

When I run this formula, OpenOffice returns the error #DIV/0!.

So, if I chunk up the formula into each of its parts:

Whenever I execute them in separate cells, I get the same results in both spreadsheet programs, which means they do WORK individually. This makes me believe the issue is with the LOOKUP.

In the above case, the reason I'm using INDIRECT is because both cells Q3 and M3 reference to the beginning and the end of a range:

Q3 contains:

{=MIN(IF($Import.$A$1:$A$1048576=A3;ROW($Import.$A$1:$A$1048576)-ROW(INDEX($Import.$A$1:$A$1048576;1;1))+1))}

M3 contains about the same but using MAX:

{=MAX(...)}

These will return the index position where an array of array starts and ends. Say that A3 equals to Apple, then Q3 will return the row of Apple's first occurrence and Q3 will return the row of Apple's last occurrence in Column A.

It's worth reiterating that ALL formulas work except the LOOKUP, meaning that the above cell references B3 and M3 return the correct index position (or row). It's also worth mentioning that the formula does work for Excel.

Does anyone know why the LOOKUP formula does not work in OpenOffice?

Can this be done elsewise without LOOKUP?

Solution

  • No idea how it works in Excel, but in Calc, >= simply returns true if the value on the left is greater than or equal to the value on the right. So it sounds like we need to add an IF statement to do what you are asking.

    The following array formula finds the highest row where the condition returns true. Then it grabs the value from that cell.

    =INDIRECT("Import.F"&MAX(IF(INDIRECT(CONCATENATE("$Import.$F$";Q3;":$F$";M3))>=S3*VALUE(SUBSTITUTE($S$1;"LOOKUP FACTOR x";""));ROW(INDIRECT(CONCATENATE("$Import.$F$";Q3;":$F$";M3)));0)))

    Breakdown: