Index/Match Random (?) N/A Error

[Essay]

I have been scratching my head at this one. I am no novice user of excel and I've come up with nothing.

I have a basic index/match function throwing an N/A error, seemingly randomly.

This may be difficult to conceptualize without *actually* having the workbook but I will do my best to illustrate.

The following table is being used for the matching and indexing (this is where my results are from).

[TABLE="width: 377"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Code[/TD]
[TD]Title[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1.0000[/TD]
[TD]Agriculture, General.
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1.0000[/TD]
[TD]Agriculture, General.[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1.0101[/TD]
[TD]Agricultural Business and Management, General.[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1.0101[/TD]
[TD]Agricultural Business and Management, General.[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1.0102[/TD]
[TD]Agricultural Business and Management, General.[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1.0102[/TD]
[TD]Agricultural Business and Management, General.[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD]All codes are formatted as numbers.

The following are the formulas corresponding with the following table, that are used to return the results

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]=INDEX(B:B,MATCH(D2,A:A,0))[/TD]
[TD]=INDEX(B:B,MATCH(E2,A:A,0))[/TD]
[TD]=INDEX(B:B,MATCH(F2,A:A,0))[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1.0000[/TD]
[TD]1.0101[/TD]
[TD]1.0202[/TD]
[/TR]
</tbody>[/TABLE]

However, THESE are the results:

[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Agriculture, General.
[/TD]
[TD]Agricultural Business and Management, General.
[/TD]
[TD]#N/A
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1.0000[/TD]
[TD]1.0101[/TD]
[TD]1.0102[/TD]
[/TR]
</tbody>[/TABLE]


I have tried using value(), trimmed all spaces, the list goes on. I have found that the only solution is to find the offending code on the indexed table (that would be A6 in this scenario), locate the cell in which the code lives, ACTIVATE the cell by clicking into it, and CONFIRM the cell by pressing enter. ????? If this was a smaller data set, I would bite the bullet and do it manually (or w/VBA) but I'm not interested in doing it to 250,000+ cells.

Any of you geniuses have a guess as to what is causing this?

Thanks in advance!

 

[etaf]

sounds like the value is entered as text rather than a number

Also why are they on the table twice ?

if you use
=value() to change the text into a number

I'm not sure if the values in the table are text or the value in the table are text

 

[JohnGSnyder01]

I am not sure if this is it, but I don't see match for the value in cell F2 in the Column A values you show. Could this be causing the problem?

 

[Essay]

I am not sure if this is it, but I don't see match for the value in cell F2 in the Column A values you show. Could this be causing the problem?

Whoops! That's a typo. I'll edit.

 

[etaf]

still a typo??? in F2

 

[Essay]

still a typo??? in F2

Yes. I says I may not edit my posts ?

F2 is not supposed to be 1.0202.
It should be 1.0102

 

[Andrew Poulsom]

There is nothing random about #N/A errors. If a match isn't found that's what is returned. Note that formatting a cell doesn't change what it contains, only its appearance. That said, Excel takes note of the format when you enter data. That's why changing the format and re-entering the data changes the data type.

If you format all your data to correspond with the data type of the lookup value, Text To Columns should convert it for you.

 

[Essay]

The error is occurring randomly (no pattern) throughout the data. I know errors are not random. The data was imported directly from a SQL database as a UTF8 encoded decimal(65,30) data type. If the data was truly formatted otherwise (trailing spaces, special characters, alpha, etc.) the database would minimally suggest changing the type or collation.) The issue is on the excel side. I am not sure how confirming a cell would change the data type but I know excel has many automatic and behind-the-scenes operations. If anyone knows the conversion it is doing, that would be greatly appreciated.

 

[etaf]

create a new column in the data table and use
=value(a1)
and copy down

that will change the text to numbers
Or
text to columns
or
=A1*1

and then try the lookup using the new column instead

see if you now get errors