Issue 8821 - index and match function problem
Summary: index and match function problem
Status: CLOSED NOT_AN_OOO_ISSUE
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOo 1.0.1
Hardware: PC Windows 2000
: P3 Trivial (vote)
Target Milestone: ---
Assignee: john.marmion
QA Contact: issues@sc
URL:
Keywords: oooqa
Depends on:
Blocks:
 
Reported: 2002-10-29 18:20 UTC by mdsale
Modified: 2013-08-07 15:15 UTC (History)
2 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

Attachments
Cell C8 of sheet "cost estimator" is where the problem shows up. (20.50 KB, application/octet-stream)
2002-10-30 02:20 UTC, mdsale 		no flags Details
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this issue.
Description mdsale 2002-10-29 18:20:37 UTC 
I have a spreadsheet with the following table lookup function:

=index(Base,match(Width,Widths,0),match(Length,Lengths,0))

Which I've created using gnumeric, and can read and manipulate properly in
Microsoft Excel when I export it as a .xls file. However, neither openoffice,
nor StarOffice can seem to translate this properly.  It turns into:

=INDEX(Lengths;0;MATCH())
Comment 1 prgmgr 2002-10-30 01:39:00 UTC 
Thank you for using and supporting OOo.

Please attach the MS Excel file that OOo cannot import properly.
Comment 2 mdsale 2002-10-30 02:20:00 UTC 
Created attachment 3422 [details]
Cell C8 of sheet "cost estimator" is where the problem shows up.
Comment 3 prgmgr 2002-10-30 03:11:28 UTC 
Duplicated on Win NT 4.0 SP6a, OOo 643.

To the QA engineers and developers:

Sorry, I really don't understand how the formula works.  To me, it 
doesn't seem like a valid syntax.

When I open "table-bug.xls", I see the following formula in C8 on the 
sheet called "Cost Estimator":

=Base Width INDEX(MATCH(Widths,0),Length,MATCH(Lengths,0))

I think base is referring to a table on the sheet called "tables".

OOo translates the formula to:
=INDEX(Lengths;0;MATCH())
Comment 4 frank 2002-11-28 10:22:54 UTC 
Hi Daniel,

it seems that Base Width are two named ranges, but I don't know how
this formula is working in Excel. So please have look at this.

Frank
Comment 5 daniel.rentz 2003-01-13 17:02:55 UTC 
target->OOo2.0
Comment 6 john.marmion 2003-03-19 11:03:06 UTC 
I will take a look at this.
Comment 7 john.marmion 2003-03-25 16:31:37 UTC 
marking as started.
Comment 8 john.marmion 2003-03-26 14:09:13 UTC 
I believe that the source of this problem is that gnumeric has not 
exported this formula correctly in excel format. If I create an Excel 
doc and use the attached doc and apply stated formula:
=index(Base,match(Width,Widths,0),match(Length,Lengths,0))
and import this to Calc, then it works successfully. 

The attached spreadsheet does not work in Excel. Because the formula 
result is stored in the file, Excel simply displays the result. But 
attempts to re-execute the formula fail and attempts to save the doc 
in Excel will also fail and result in a #VALUE error i.e. an error 
occurs when the wrong type of argument or operand is used. This 
corresponds to what Calc displays: Err:511 i.e. function requires 
more variables. Excel displays the formula as:
=Base Width INDEX(MATCH(Widths,0),Length,MATCH(Lengths,0))

which makes no sense. 

So I would like to propose that we close this as not a bug.
Comment 9 john.marmion 2003-03-27 09:32:06 UTC 
closing as invalid
Comment 10 john.marmion 2003-05-22 14:37:15 UTC 
closed