Repost: Index Match lookup formula returns #N/A error

[AC]

Hello,
I have a file A with a list of item numbers in column A and
corresponding quantities in column B. The item numbers in file A were
imported as a text field, the quantity field type is number.
In file B I have the same information, which I typed in by hand.
Therefore, the item number field and the quantity number field types
are numbers.
I have used the following formula to populate file A with the
information from file B:

{=INDEX('[File A.xls]Sheet1'!$D$4:$D$74,MATCH(('[File
B.xls]Sheet1'!A11),'[File A.xls]Sheet1'!$A$4:$A$74,0))}


The curious thing that is happening is that most of the quantity values

are found and filled into file A properly. However, some of the
quantity values are not returned but the formula rather returns the
#N/A value. I checked to make sure that the item numbers are listed in
both files.


Any idea why this is happening?
All help is greatly appreciated.


Thank you in advance.
Regards,
A. Crawford

 

[AC]

I noted that the the cells returning the #N/A error are referring to
item numbers that have a different format than the ones that do return
the correct quantity number.
For example, a number such as 12345 will return the #N/A error and a
number like 12345-67 will return the correct quantity number. I still
have not a clue why. Can anybody help?

Thanks in advance.
Regards,
A. Crawford

 

[Dave Peterson]

There's a difference in values between the text '12345 and the number 12345.

(Text values can be preceded with an apostrophe or the cell could have been
pre-formatted as Text, then the value entered.)

Debra Dalgleish goes into more detail:
http://contextures.com/xlFunctions02.html#Trouble

(although using =vlookup(), but the idea is the same.)

I noted that the the cells returning the #N/A error are referring to
item numbers that have a different format than the ones that do return
the correct quantity number.
For example, a number such as 12345 will return the #N/A error and a
number like 12345-67 will return the correct quantity number. I still
have not a clue why. Can anybody help?

Thanks in advance.
Regards,
A. Crawford

Hello,
I have a file A with a list of item numbers in column A and
corresponding quantities in column B. The item numbers in file A were
imported as a text field, the quantity field type is number.
In file B I have the same information, which I typed in by hand.
Therefore, the item number field and the quantity number field types
are numbers.
I have used the following formula to populate file A with the
information from file B:

{=INDEX('[File A.xls]Sheet1'!$D$4:$D$74,MATCH(('[File
B.xls]Sheet1'!A11),'[File A.xls]Sheet1'!$A$4:$A$74,0))}


The curious thing that is happening is that most of the quantity values

are found and filled into file A properly. However, some of the
quantity values are not returned but the formula rather returns the
#N/A value. I checked to make sure that the item numbers are listed in
both files.


Any idea why this is happening?
All help is greatly appreciated.


Thank you in advance.
Regards,
A. Crawford