Ask Your Question

match function incorrectly returns #N/A [closed]

asked 2017-02-16 20:31:11 +0200

G_Smiley gravatar image

updated 2020-09-24 21:37:32 +0200

Alex Kemp gravatar image

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?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-24 21:37:08.861077

2 Answers

Sort by » oldest newest most voted

answered 2017-02-17 00:25:51 +0200

m.a.riosv gravatar image
edit flag offensive delete link more


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?

G_Smiley gravatar imageG_Smiley ( 2017-02-17 16:56:10 +0200 )edit

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 ✔.

m.a.riosv gravatar imagem.a.riosv ( 2017-02-17 23:21:17 +0200 )edit

answered 2017-02-16 20:44:42 +0200

Lupp gravatar image

"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.)

edit flag offensive delete link more



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.

G_Smiley gravatar imageG_Smiley ( 2017-02-16 22:07:19 +0200 )edit

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

Ctrl-F8 again to get normal display back.

robleyd gravatar imagerobleyd ( 2017-02-17 00:05:03 +0200 )edit

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.

Lupp gravatar imageLupp ( 2017-02-17 01:57:18 +0200 )edit

@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

G_Smiley gravatar imageG_Smiley ( 2017-02-17 16:19:21 +0200 )edit

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?

Lupp gravatar imageLupp ( 2017-02-17 16:27:27 +0200 )edit

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

G_Smiley gravatar imageG_Smiley ( 2017-02-17 17:00:14 +0200 )edit

Question Tools

1 follower


Asked: 2017-02-16 20:31:11 +0200

Seen: 961 times

Last updated: Sep 24