DATA FROM ANOTHER WORKSHEET

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have this formula that I am using to pull data data worksheet NSN8. No
problems.

=IF(COUNTIF(NSN8!A:A,D1088)=0,"N/A",IF(COUNTIF(NSN8!A:A,D1088),INDEX(NSN8!C:C,MATCH(D1088,NSN8!A:A,0))))

I have created another worksheet NSN09 and started to pull that data in the
next row:

=IF(COUNTIF(NSN09!A:A,D1089)=0,"N/A",IF(COUNTIF(NSN09!A:A,D1089),INDEX(NSN09!C:C,MATCH(D1089,NSN09!A:A,0))))

Instead of receiving data from worksheet NSN09, I am getting error #N/A.

Why is this?

Thanks,

Roy
 
First, you're checking that countif twice. The second time has to be true
since, you're in the "else" portion of the formula:

=IF(COUNTIF(NSN8!A:A,D1088)=0,"N/A",INDEX(NSN8!C:C,MATCH(D1088,NSN8!A:A,0)))

But I'm guessing that the difference is in the way =countif() works.
=countif(a:a,1) and =countif(a:a,"1") will return the same number.

=Countif() wants a string in that second argument and will treat numbers stored
as text and real numbers the same.

So if you're matching on numbers, then this could be the problem.

I'd eschew the =countif() and use =match() to test for an, er, match.

=IF(iserror(match(d1088,nsn8!a:a,0)),"N/A",
INDEX(NSN8!C:C,MATCH(D1088,NSN8!A:A,0)))

So are you matching up on numbers?????
 
The first formula with NSN8 works fine. It is when I try to replicate the
formula using NSN09 I get the #N/A error.
 
I am getting error #N/A.

Don't know why you're getting that result. Your COUNTIF's should eliminate a
"no match" error condition. However, your COUNTIF's are redundant. You can
eliminate the 2nd one:

=IF(COUNTIF(NSN09!A:A,D1089)=0,"N/A",INDEX(NSN09!C:C,MATCH(D1089,NSN09!A:A,0)))

A possibility is that COUNTIF and MATCH will evaluate things differently.
For example:

D1089 = numeric 10
NSN09 A1 = text 10

COUNTIF will evaluate those as equal but MATCH won't causing the #N/A.

Biff
 
Are you trying to match a number with a string -- or a string with a number?

In either case, did the alternative suggestion work ok?
 
Back
Top