Unexpected #N/A error from INDEX-MATCH combo

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

animeshhere@gmail.com - 27 Jul 2007 11:57 GMT

Dear All,
In our group, we manage people related data with a set of excel files.
Normal files extract the people related information (DOB, Emp ID etc.)
from 2 master files using the name as input to an INDEX-MATCH
combination. Problem is, it is working only in 99% cases. In some
stray occasions, the result returns #N/A, even though the requested
data exists in the master files.

I tried tracing using the formula audit toolbar, but it was of no
help. It went upto the last level of MATCH when it actually refers to
the cell (e.g. $T$51) of a master file. Although $T$51 in the master
file has a value, it (MATCH) returns #N/A!

If any of you have any idea, please let me know. You may post a reply
here or drop a PM.

Thanks a lot.

Reply to this Message

Toppers - 27 Jul 2007 12:16 GMT

Check data for additional blanks: use TRIM function to remove. Retype an
entry and see if MATCH still fails.

> Dear All,
> In our group, we manage people related data with a set of excel files.
[quoted text clipped - 13 lines]
>
> Thanks a lot.

Reply to this Message

animeshhere@gmail.com - 27 Jul 2007 13:13 GMT

> Check data for additional blanks: use TRIM function to remove. Retype an
> entry and see if MATCH still fails.

Thanks Toppers. Let me elaborate my case:

Master files are A & B:
A.xls
name1 emp-id1
name2 emp-id2
....

B.xls
name1(from A) emp-id1(from A) group1
name2(from A) emp-id2(from A) group2
.......

C.xls
name1(from A) group1(from B)
name2(from A) group2(from B)
......

In C, the entry is selected using a dropdown list DV'ed from name
extracted from A.
The result in C should extract data (Group) from B (with matching
name).

But, for some set of data (Group), I get #N/A in file C instead of the
group.

However, when I open B.xls, groups get updated!!

I am really puzzled.

Reply to this Message