Home

Match #NA Error

%3CLINGO-SUB%20id%3D%22lingo-sub-815989%22%20slang%3D%22en-US%22%3EMatch%20%23NA%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-815989%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20using%20Excel%202007%20and%20have%20replicated%20this%20error%20in%20Excel%202013.%20On%20three%20separate%20computers%20in%20the%20office%2C%20from%20workbooks%20made%20from%20scratch.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20simplified%3A%3C%2FP%3E%3CP%3EStart%20with%20a%201x2%20table%20with%20value%201.14%20and%20a%20corresponding%20value%20of%20whatever%20we%20want%20to%20lookup.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECreate%20a%20cell%20as%20a%20lookup%20value%20that%20calculates%201.14.%20e.g.%20%3D1%2B14*.01%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECreate%20an%20index%20match%20to%20lookup%20this%20value%20based%20on%20our%20calculated%20value.%3C%2FP%3E%3CP%3E%3DIndex(1x2Table%2C%20Match%20(1%2B14*.01%2C%20Col1ofTable%2C%200)%2C%202%20)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EReturns%20%23NA%20in%20both%20versions%20of%20Excel.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20change%20the%20value%20of%201.14%20to%201.13%20or%201.15%20or%20whatever%20and%20lookup%20that%20value%20based%20on%20a%20calculation%20and%20the%20function%20returns%20whatever%20we%20want%20to%20lookup.%20Vlookup%20seems%20to%20work%20fine%20for%20all%20the%20values%20as%20well.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20is%20going%20on%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20out%20of%20shear%20curiosity%3B%20further%20tinkering%20revealed%20that%20several%20multiples%20of%201.14%20and%20corresponding%20calculated%20lookup%20values%20also%20returned%20the%20same%20%23NA%20error.%20e.g.%202.28%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECells%20formatted%20as%20number%20with%202%20decimals%20or%20general%2C%20doesn't%20seem%20to%20matter%2C%20but%20copy%20pasting%20the%20value%20from%20the%20calculation%20does%20clear%20the%20error%20and%20return%20whatever%20we%20want%20to%20lookup.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-815989%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-816057%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20%23NA%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-816057%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F396232%22%20target%3D%22_blank%22%3E%40brspain%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CP%3EWhat%20is%20going%20on%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3CP%3EIt's%20just%20a%20common%20floating%20point%20error.%3C%2FP%3E%3CP%3EPut%20your%20calculations%20inside%20a%20round%20function%20and%20it%20should%20work.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DPZRI1IfStY0%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EExplanation%20(or%20rant%3F)%20by%20Tom%20Scott%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-816060%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20%23NA%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-816060%22%20slang%3D%22en-US%22%3E%3CP%3EYeah%2C%20I%20should%20have%20mentioned%20that%20I%20did%20that%20and%20that%20fixed%20it%2C%20but%20I%20guess%20my%20question%20was%20more%20along%20the%20lines%20of%20why%20does%20that%20seem%20to%20happen%20with%201.14%20and%20not%201.13%20or%20any%20of%20my%20other%20values.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-816071%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20%23NA%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-816071%22%20slang%3D%22en-US%22%3E%3CP%3EOkay%2C%20so%20I%20watched%20the%20linked%20video%2C%20and%20again%20the%20error%20is%20along%20the%20lines%20of%20what%20I%20was%20thinking%2C%20but%20I%20guess%20I%20was%20under%20the%20impression%20that%20Excel's%20Cell%20Formatting%20fixed%20this%20type%20of%20error.%20Does%20it%20not%3F%20And%20why%20does%20Vlookup%20work%20where%20Match%20fails%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-816100%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20%23NA%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-816100%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F396232%22%20target%3D%22_blank%22%3E%40brspain%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20found%20that%2C%20provided%20the%20calculation%20process%20is%20identical%2C%20the%20discretisation%20error%20will%20be%20the%20same%20and%20the%20match%20will%20work.%26nbsp%3B%20Thus%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3E%3D1%2B14*0.01%20finds%26nbsp%3B%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3E%3D1%2B14*0.01%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3Ebut%20not%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3E1.14%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3E%E2%80%A6%20and%20conversely.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EFor%20any%20comparison%2C%20the%20numbers%20should%20be%20integers%2C%20not%20floating%20point.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3EYou%20might%20be%20interested%20to%20know%20that%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3E1%2B14*0.015625%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3Eand%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3E1.21875%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3Ework%20interchangeably%20because%20dividing%20by%20factors%20of%20two%20does%20not%20generate%20rounding%20error.%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-816106%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20%23NA%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-816106%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F396232%22%20target%3D%22_blank%22%3E%40brspain%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EVLOOKUP%20works%20if%20you%20skip%20optional%204th%20parameter%2C%20i.e.%20use%20approximate%20search.%20With%20it%20equal%20to%20TRUE%20it%20returns%20an%20error.%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECell%20formatting%20is%20only%20formatting%2C%20it%20doesn't%20affect%20binary%20representation%20of%20the%20number%20.%3C%2FP%3E%3C%2FLINGO-BODY%3E
brspain
New Contributor

I am using Excel 2007 and have replicated this error in Excel 2013. On three separate computers in the office, from workbooks made from scratch.

 

So simplified:

Start with a 1x2 table with value 1.14 and a corresponding value of whatever we want to lookup.

 

Create a cell as a lookup value that calculates 1.14. e.g. =1+14*.01

 

Create an index match to lookup this value based on our calculated value.

=Index(1x2Table, Match (1+14*.01, Col1ofTable, 0), 2 )

 

Returns #NA in both versions of Excel. 

 

I can change the value of 1.14 to 1.13 or 1.15 or whatever and lookup that value based on a calculation and the function returns whatever we want to lookup. Vlookup seems to work fine for all the values as well.

 

What is going on?

 

And out of shear curiosity; further tinkering revealed that several multiples of 1.14 and corresponding calculated lookup values also returned the same #NA error. e.g. 2.28

 

Cells formatted as number with 2 decimals or general, doesn't seem to matter, but copy pasting the value from the calculation does clear the error and return whatever we want to lookup.

 

5 Replies

@brspain 


What is going on?

 


It's just a common floating point error.

Put your calculations inside a round function and it should work.

Explanation (or rant?) by Tom Scott

 

Yeah, I should have mentioned that I did that and that fixed it, but I guess my question was more along the lines of why does that seem to happen with 1.14 and not 1.13 or any of my other values.

 

@Detlef Lewin 

Okay, so I watched the linked video, and again the error is along the lines of what I was thinking, but I guess I was under the impression that Excel's Cell Formatting fixed this type of error. Does it not? And why does Vlookup work where Match fails?

 

@Detlef Lewin 

@brspain 

I found that, provided the calculation process is identical, the discretisation error will be the same and the match will work.  Thus 

=1+14*0.01 finds 

=1+14*0.01

but not

1.14

… and conversely.

 

For any comparison, the numbers should be integers, not floating point.

You might be interested to know that

1+14*0.015625

and

1.21875

work interchangeably because dividing by factors of two does not generate rounding error.

@brspain 

VLOOKUP works if you skip optional 4th parameter, i.e. use approximate search. With it equal to TRUE it returns an error. 

Cell formatting is only formatting, it doesn't affect binary representation of the number .

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies