Excel MATCH() function - Zero matches blank

Question

Im using the MATCH() function to decice the row number for the INDEX() function. I am using exact match (0) as parameter. This works fine for the majority of the time, however:

Why does it create a match when one cell is blank/empty and the other contains zero?

I could probably make some IF-statement, ignoring zero values, to counter this effect. But it seems silly and I would also like to understand the underlying logic behind this mechanic, for future usage.

Edit: As requested, here is the function of which I speak...

=IFERROR(INDEX('RMED Visual Inspection'!$A:$B; MATCH(A2; 'RMED Visual Inspection'!$A:$A; 0); 2); 0)

The lookup_value is blank. The lookup_array consist of numbers from 0 and up. MATCH() will return the number of the first row in the lookup_array that is 0.

The `lookup_value` is a blank/empty field (nothing entered in it, not even ""). And that `lookup_value` creates a match with the first zero in the `lookup_array` — Jakob Busk Sørensen, Sep 09 '15 at 05:56
Indeed Microsoft would be the best to answer this. However the response time is usually a lot quicker in here, and what people know continues to surpise me, so I decided to give it a shot... — Jakob Busk Sørensen, Sep 10 '15 at 07:39

score 1 · Accepted Answer · answered Sep 10 '15 at 14:43

In Microsoft Excel, when you use a formula that tests for a zero value, you may see unexpected results if the cell is blank. Microsoft Excel interprets a blank cell as zero, and not as empty or blank. Therefore, any cells that are blank are evaluated as zero in the function.

And this is the reason you are getting the number of the first row that is 0.

Check this link for more information.

Another thing which I've noticed is that you are trying to find a row number which you already know using A2 in your Match function.

My suggestion would be to change all the blank cells with 0 or some value if you really want to use your formula. You can do in many different ways.

One way is using Go To Special function to fill blank cells with 0 or other specific value and another option is to use small macro to do so.

You can get more info on this page.

Thanks. I ended uo with a work around using `IF()` to ignore blank cells. But its nice to know that it is just the way the mechanics works, and not something I did wrong :-) — Jakob Busk Sørensen, Sep 11 '15 at 06:18

Excel MATCH() function - Zero matches blank

1 Answers1