MATCH function query

[2016LM]

Hi,

I hope this question hasn't already been answered, I promise I did search the forums prior to, but to no avail. I apologise if it has however.

Premise: I have a column starting at cell A1 (with a value of 0 i.e. zero), which increment by 0.1 upto and include cell A4601 e.g.

Cell No: Value:
A1 0
A2 0.1
A3 0.2
.
.
A4601 460


When I use MATCH to locate a number (specified in cell D2, which is rounded to one decimal place; not by using the ROUND function), I encounter an error (i.e. #N/A) when the match-type is 0 (i.e. zero);

MATCH($D$2,$A$1:$A$4601,0)

Please note: for values between 0 and 1.3, no error is encountered only >=1.4.

However, when I change the match_type to 1 (i.e. locate the largest value that is less than or equal to the desired value in D2, as opposed to locating the first cell that is exactly equal to D2) i.e.

MATCH($D$2,$A$1:$A$4601,1)

...it works!

I am at a bit of a loss as to why the MATCH with a match_type of 0 does not work, because all the numbers in column A are unique (i.e. not duplicated).

Any help or advice anyone could provide would be massively appreciated.

Kindest regards,

2016LM

 

[63falcondude]

It sounds like the numbers in column A have EXACTLY one decimal place (e.g. 1.5, 1.6, etc.) while the number in D2 is rounded to one decimal place (e.g. 1.59 showing as 1.6). Since 1.59 is not exactly equal to 1.6, match will not find 1.6 in column A.

Try =MATCH(ROUND($D$2,1),$A$1:$A$4601,0) to see if this is the case.

 

[2016LM]

Thanks for taking the time to review and reply to my query 63falcondude. I tried your suggestion, but still enounter the same error. Even when I type a number (to 1 d.p) into cell D2 (e.g. 2.3 or 1.6), MATCH still returns #N/A, but anything =<1.3 seems to work. I've tried reducing the range MATCH looks at, but, again, the same error persists. I do have a work around (i.e. using a match_type of 1), so have managed to progress my spreadsheet (though, I hasten to add, that I made a standalone spreadsheet, with just the cells outlined above populated, and the error persists), just a bit perplexed why a match_type of zero doesn't work.

Kind regards,

2016LM

 

[Aladin Akyurek]

Do you have a formula in A2 which is copied down?

 

[2016LM]

Hi Aladin, thanks for replying. Apologies, I should've mentioned in my initial post that I had tried populating the cells in column A using both a formula and without (i.e. just raw values), but to no avail. Same error persists.

Kind regards,
2016LM

 

[Peter_SSs]

I can reproduce your situation if I use a formula in column A. I don't know the exact arithmetic behind the MATCH function, but I'm sure therein lies the issue. Some background here for example.

In the example below, the formula in A2 is copied down.
D15 is manually entered.
The EXACT function confirms the two values identical, but the standard MATCH function that you used returns #N/A as you described.
If we extend 63falcondude's idea of rounding to rounding both sections of the MATCH formula (array formula entry required), then it works.

It is somewhat worrying when things like this happen but an electronic calculation has to have some limits and sometimes that causes slight inaccuracies.

Excel 2010 32 bit
	A	B	C	D	E	F	G
1	0
2	0.1
3	0.2
4	0.3
5	0.4
6	0.5
7	0.6
8	0.7
9	0.8
10	0.9
11	1
12	1.1
13	1.2
14	1.3
15	1.4			1.4	TRUE	#N/A	15
16	1.5
MATCH

Cell Formulas
Range	Formula
A2	=A1+0.1
E15	=EXACT(D15,A15)
F15	=MATCH(D15,A$1:A$4601,0)
G15	{=MATCH(ROUND(D15,1),ROUND($A$1:$A$4601,1),0)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Aladin Akyurek]

Hi Aladin, thanks for replying. Apologies, I should've mentioned in my initial post that I had tried populating the cells in column A using both a formula and without (i.e. just raw values), but to no avail. Same error persists.

Kind regards,
2016LM

The question is whether you have a formula in A. If that's the case, try wrap it into a ROUND call with num_digits set to 1. Both match types, exact and approximate, now should work.

 

[2016LM]

Ah right, spot on, thanks for your help Aladin and Peter_SSs; both suggestions work (still getting my head around array formulae, so will probably stick with simply rounding the cell contents). Seems odd that ROUND has to be used in order for an exact match_type to work, but as you intimated Peter_SSs, excel functions will themselves have limits. Anyway, thanks again to you both for taking the time and trouble to assist with my query, your help is very much appreciated.

Kind regards,
2016LM

 

[Aladin Akyurek]

Ah right, spot on, thanks for your help Aladin and Peter_SSs; both suggestions work (still getting my head around array formulae, so will probably stick with simply rounding the cell contents). Seems odd that ROUND has to be used in order for an exact match_type to work, but as you intimated Peter_SSs, excel functions will themselves have limits. Anyway, thanks again to you both for taking the time and trouble to assist with my query, your help is very much appreciated.

Kind regards,
2016LM

Write ups like this one

Rounding And Precision In Excel

might help to understand the issue.