Match function incorrectly returns #N/A

English
#1

Here is a copy paste of my malfunctioning match formula

MATCH(W7,‘file:///P:/Calculatons/Eccentric weld tables.xls’#$‘CISC 3-28’.$A$21:$R$21,0)

This formula works fine on row 6 of my sheet (lookup W6), but with a copy paste of the formula from rows 7 to 14, it returns #N/A randomly on some rows.

To trouble shoot the formula I copied it to a free cell. Result: #N/A. If I manually change W7 to 0.3 (the value in cell W7) it returns the correct result.

If I change the search type from 0(exact match) to 1(sorted list representing the correct sort direction) the formula will return 7 as the value (correct) if I have the 0.3 entered manually into the formula. If I change the formula to call cell W7, and manually enter 0.3 in cell W7, the formula will return 6 (incorrect)

This tells me scalc is somehow modifying the value being called into the match formula

What can I do?

#2

“If I manually change W7 to 0.3 (the value in cell W7) it returns the correct result.”
Did you check if the 0.3 in W7 actually is a number in the sense of “not text”? (By value highlighting e.g.)

#3

yes,

I have manually entered ‘=0.3’ OR used the typically calculated value for cell w7 that is trunkated to 1 decimal place.

The cell format was number, to two decimal places. I tried changing the format to ‘number with 1 decimal place’ and number ‘General’. No format change affected the output.

#4

Whilst on the relevant sheet, press Ctrl-F8 - what is the colour of W7?

Ctrl-F8 again to get normal display back.

#5

If the type (whether content or formula result) is NUMBER, the format for display shouldn’t matter.
However, there is a toxic option ‘Precision as shown’ under ‘LibreOffice Calc’ > ‘Calculate’.Make sure that this option is not selected.

#6

@robleyd, the colour of W7 is green. (is seems like all values of formulas are green) The values in my called sheet(xls) are Blue. I saved my called .xls file to .ods and rewrote my formulas to call the .ods. This has had no effect

@Lupp, ‘Precision as shown’ it was default to NOT selected. I tried running with it selected, as the value as shown is correct, but this had no effect. I have this option NOT selected currently

#7

Ok. We now know at least that W/ is containing a formula as opposed to a directly entered value. However, the color in this case doesn’t tell us if the result is of type ‘Text’ or of type 'Number.
What do you get if you enter the formulas =ISTEXT(W7) and =ISNUMBER(W7) into two empty cells?
What formuila does W7 contain?

#8

@Lupp ISTEXT = FALSE, ISNUMBER = TRUE
Also, see my response to m.a.riosv

#9

Maybe it keeps some relation with this bug:

Regular expressions doesn’t work with MATCH and VLOOKUP when searched range is in other file.

#10

I can confirm, I recreated my sheet ‘CISC 3-28’ from my external file, within my current sheet and everything is working.
Is there something I can do to upvote this bug?
Also, I am too new here to mark this answer as applicable.

Also, is there any known workaround?

#11

Please add your comment in it. You need to register on the site first.
Perhaps the issue is with the dot being part of regular expressions, when 0.3 is as text on the cell.

If the answer solves your question please tick the :heavy_check_mark:.

#12

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.