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,MAT CH(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?????

roy.okinawa wrote:

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,MAT CH(D1089,NSN09!A:A,0))))

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

Why is this?

Thanks,

Roy

--

Dave Peterson

The first formula with NSN8 works fine. It is when I try to replicate the
formula using NSN09 I get the #N/A error.


"Dave Peterson" wrote:

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?????

roy.okinawa wrote:

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,MAT CH(D1089,NSN09!A:A,0))))

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

Why is this?

Thanks,

Roy

--

Dave Peterson

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

"roy.okinawa" wrote in message
...
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,MAT CH(D1089,NSN09!A:A,0))))

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

Why is this?

Thanks,

Roy

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?

roy.okinawa wrote:

The first formula with NSN8 works fine. It is when I try to replicate the
formula using NSN09 I get the #N/A error.

"Dave Peterson" wrote:

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?????

roy.okinawa wrote:

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,MAT CH(D1089,NSN09!A:A,0))))

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

Why is this?

Thanks,

Roy

--

Dave Peterson


--

Dave Peterson