MATCH function error only solved by clicking on search cell

Question

Hey guys, I'm having a weird issue regarding updating of formulas

Essentially I'm trying to use a MATCH function, but even though the formula should work, but it returns an #N/A value as per the below. In this example, I'm trying to find where a match for 1.4 is relative to section 1.3

I have found a weird fix where clicking on the cell, and clicking on the formula bar containing 1.4, then clicking off will resolve the issue. I need this to be automatic as it is used with a macro for adding sections. VBA isn't the issue here however as I have tested it in a xlsx file with the same results.

All my calculations are set to automatic as shown below. Does anyone know how to fix this?

Answer 1

Hi Cory,

Sorry to hear that you're experiencing this issue!

I am Bhavik, a Microsoft user like you. Please note, I am not a Microsoft employee and don’t have direct access to your account, but I will do everything I can to help you with your queries. :)

In MATCH function, second parameter, please use Range insted of a single cell.

Like range instead of B18.

Let me know what happens.

Thanks

Bhavik

Answer 2

First, there is nothing wrong with using a single-cell reference in the second parameter. I do it "all the time".

So, MATCH(1.4, B18, 0) is just fine syntax-wise.

The reason why that MATCH failed initially, but worked after merely pressing Enter in the Formula Bar, is because 1.4 was text initially. When you pressed Enter, it was changed to numeric, which is the type of 1.4 in the MATCH expression.

Note that I am talking about data type, not cell format. The format of the cell does not matter; and looks can be deceiving. If you want to confirm the __type__ of the data in B18 initially, use a formula of the form =ISTEXT(B18) .

To fix it moving forward, you have two choices:

1. Change the expression to MATCH("1.4", B18, 0) .

If the first parameter is actually a cell reference, too, you can write something like MATCH(X1 & "", B18, 0) , or ensure that X1 is also __type__ text.

or

2. Change the VBA code so that when it stores 1.4, it is stored as a number, not text.

We probably cannot help you with #2 (except with a lucky guess) unless and until you show us the VBA code -- or enough context for us to understand how 1.4 was stored as text, in the first place.

Wild guess.... Set the cell format to General before you store 1.4 into B18.