'COUNTIF' Function is not preventing '#N/A'

Originally Posted by carlosbourn

I am using the following COUNTIF formula which I am sure is supposed to prevent '#N/A' from appearing (According to http://www.ozgrid.com/Excel/stop-na-vlookup.htm)

=IF(COUNTIF(B10,PORT),"",INDEX(Ports!$A$3:$BD$54,MATCH(Jun08!C10,PORT,0),MATCH(Jun08!D10,Ports!$A$2:$BD$2,1)))

However, #N/A is still appearing in the worksheet when no data has been input.

As this is something we are going to be presenting to a client, I am keen to ensure it doesn't contain #N/A all over it!

Thanks in anticipation

I don't really understand the first part
B10 seems to be the range you test, is PORT the test criteria?

Correct - B10 is the range & PORT is the criteria

(arthurbr - this is based almost entirely on a forumula you helped me with a month or so ago!)

Originally Posted by carlosbourn

Correct - B10 is the range & PORT is the criteria

(arthurbr - this is based almost entirely on a forumula you helped me with a month or so ago!)

Thx for reminding me
But I see thet PORT is also used as 2nd in your MATCH function where it should use an array??

Hmm, I see what you mean but the formula seems to work absolutely fine and I think the formula you used in my sample last time was also set up like this - see below:

=IF(COUNTIF(B2,port),"",INDEX(Ports!$A$3:$F$52,MATCH(Input!C2,port,0),MATCH(Input!D2,Ports!$A$2:$F$2,1)))

Could you post your file?