Wrong data type value

1
votes

I have been working on a project in windows Excel, but now I have to change it to LibreOffice Calc and the function returns #VALUE!, but works normally in Excel.

Here's the function ( it searches for a value in a certain column and row )

=IFERROR(INDEX($B$3:$B$1400; SMALL(IF(ISNUMBER((SEARCH($F$3;$B$3:$B$1400))*
(SEARCH($G$3;$D$3:$D$1400))); ROW($D$3:$D$1400)-MIN(ROW($D$3:$D$1400))+1;"");ROW(A21)));"")

And here is how my Worksheet aka SpreadSheet in LibreOffice Calc looks like

enter image description here

I read somewhere that the Quotes shouldn't be in there so I tried to Remove/Replace them and got value from one cell:

-When I REMOVED the Quotes "", it returned a Random cells value

-When I REPLACED the Quotes "" with the number 1 it returned the value of the first person, but in the next cell (since it should find every person with a certain name or if left Blank it should find every Person in the file.

1
libreoffice-calc

1 Answers

1
votes

In Excel the SEARCH function will match a blank search cell with all the lookup cells. In Calc, a blank cell as the search criteria will return an error.

To get the "return all if search cell is blank" behavior you'll need to wrap your searches each in an IF, returning a number (so the ISNUMBER function returns TRUE) in the case of the search cells being blank:

IF($F$3<>"";SEARCH($F$3;$B$3:$B$1400);1) * IF($G$3<>"";SEARCH($G$3;$D$3:$D$1400);1)

So your full formula will be entered with Ctrl+Shift+Enter (since this is an array formula) in cell F8 as:

=IFERROR(INDEX($B$3:$B$1400;SMALL(IF(ISNUMBER(IF($F$3<>"";SEARCH($F$3;$B$3:$B$1400);1) * IF($G$3<>"";SEARCH($G$3;$D$3:$D$1400);1)); ROW($D$3:$D$1400) - MIN(ROW($D$3:$D$1400))+1;"");ROW(A1)));"")

Copy and paste down however many cells you need. If you want to paste to a large number of cells, you can highlight them all by typing in the name box (to the left of the formula bar) the range, for example F9:F1405, and pressing Enter.

Note that in LibreOffice versions older than 4.4.4.6 the IFERROR function will not suppress the error (see bug 95226). It should work as you expect in newer versions.

Edit

Both the IFERROR and ISERROR functions pass this particular error through instead of returning TRUE or FALSE (appears to be a bug in current versions of both OpenOffice and LibreOffice - see the "Issues" section of the OpenOffice wiki page https://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_ISERROR_function#Issues).

A potential workaround is to not pass any text value to the SMALL function. (The formula above will pass an empty text string to the SMALL function if no match is found, thus generating an error). Instead of the "" we will pass the last row number in the array - for this to work the bottom-most cell in the range, row 1398 in this case, must be a space (or, it could be empty if it was OK to Tools→Options→OpenOffice Calc→View→UNcheck "Display zero values).

We will also make sure not to tell the SMALL function to look for a rank that is larger than the size of the range it is provided. So like this:

=INDEX($B$3:$B$1400;SMALL(IF(ISNUMBER(IF($F$3<>"";SEARCH($F$3;$B$3:$B$1400);1) * IF($G$3<>"";SEARCH($G$3;$D$3:$D$1400);1)); ROW($D$3:$D$1400) - MIN(ROW($D$3:$D$1400))+1;1398);MIN(ROW(A1),1398)))