MATCH Function #N/A Error

[katmarsar]

I am trying to use the MATCH function to identify the relative position of a value within in a range (a single row of multiple columns). The problem is, the range is unsorted and has zero values and consequently the function returns an #N/A error. Is there a better function to use or a workaround?

Thanks.

 

[Asala42]

Post your test data and your formula.

Match should work fine - but the #N/A signifies that it is not finding a match. Sort order shouldn't matter unless you're not looking for a precise match.

 

[katmarsar]

There is a matching value in the range. The only other variable I think is causing problems is the zero values. I'm completely at a loss.

 

[Jonmo1]

Post your formula and a sample set of data.

Perhaps you just need to add the 3rd argument of the match formula as 0
This makes it search for "Exact Match", instead of "Closest Match"

=MATCH(value,range,0)

 

[katmarsar]

It does have the third argument.

 

[Asala42]

One more shot

Post. Your. Data. and. Formula.

(Gah!)



[edit] Zero values in a lookup range shouldn't interfere. If you believe you have an exact match (visually), you may have trailing or leading spaces, numbers being treated as text, or some other minor issue preventing excel from recognizing the match.

 

[katmarsar]

Date in range B2:B11 (0,0,0,0,110,70,60,30,50,20)

Formula = MATCH (C2,B2:B11,-1)

Where Cell C2 is an array formula that evaluates to the number 60

It doesn't seem to matter whether the third argument is -1, 0 or 1, the error is the same.

Thanks

 

[Jonmo1]

Works for me. The 3rd argument should be 0

I would guess that your formula in C2 (or the value in B8) is not returning EXACTLY 60, perhaps it's 60.001 or something like that.
Format C2 and B8 to show 15 decimal places.
Are they the same?

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">60</TD><TD> </TD><TD style="TEXT-ALIGN: right">7</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">0</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">0</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">0</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">110</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">70</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">60</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">30</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">50</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right">20</TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C2</TD><TD>{=60}</TD></TR><TR><TD>E2</TD><TD>=MATCH(C2,B2:B11,0)</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[Asala42]

=MATCH(C2,B2:B11,0)

Should do the trick. Structurally the formula isn't wrong - this works for me on my end with simple test data.

There's a few things that can go wrong:
1) Trailing or leading spaces in the data. The formula is looking for an exact match, but is it comparing the number 60 to the text "60", or "60 ", etc.

If C2 is actually outputting text: "60", you can rewrite to:
=MATCH(C2+0,B2:B11,0)
but this assumes B2:B11 contain numeric data. If B2:B11 is imported, you may want to add zero to each of these cells to force excel to recognize the data as numeric.

2) Circular references. If the above fails, check your bottom bar for the word "Circular". If you have an unintended circular reference, this can mess up all dependent formula cells. You'll need to clear it before you can trust functions on that particular sheet again.

 

[katmarsar]

Thanks for the suggestions, but none of them seem to resolve the problem. It appears 99% likely that the problem is in the range of data, but I cannot figure it out.