Just as a follow-up, this worked like a charm. BUT....
due to the number of cells referenced in the other spreadsheet, along with
all of the other VLOOKUPS, it took a LONG time to save, or when working on
several hundred rows of input data to process.

JUST FYI, I replaced the references to columns by using Defined Range Names,
and in one test, it cup processing time from 1 min 20 sec to 4 seconds!
Literally 25 times faster, so I learned a valuable lesson that when you have
a lot of rows referencing a large range, using Defined Range Names can save
time in a dramatic fashion.

I mention it for those, like me, who had not used them often, to highlight
how much they can improve speed and performance

Thanks again for the input, it was greatly appreciated.
--
BB


"FloridaHockeyGuy" wrote:

Thanks Peo, with a little tweaking (have to nest it within an IF statement) I
think that just might work! Thanks a bunch, I think you have just saved me
from swallowing a bunch of Advil!!!
--
BB


"Peo Sjoblom" wrote:

It's certainly possible



=SUMPRODUCT(--(EXACT('[Products.xlsm]Sheet1'!$A:$A,A2)))


works for me assuming the values in A are text



for previous Excel versions it would be


=SUMPRODUCT(--(EXACT('[Products.xls]Sheet1'!$A$1:$A$10000,A2)))


since they cannot handle the whole column using array formulas

--


Regards,


Peo Sjoblom

"FloridaHockeyGuy" wrote in
message ...
I am using Countif to see if a value (in this case, the value in cell A2)
is
present in a column (A) of data;
=COUNTIF('[Products.xlsm]Sheet1'!$A:$A,A2)

but the problem is that the column is full of numbers, or combinations of
numbers and text, as they are item numbers. The column is formatted as
text
due to this, as well as some item numbers has a preceding 0. For example,
there will item 123 and also an item 0123.

I am using this Countif in a column in one spreadsheet to look up the
values
in another, and depending on whether the answer is 0 or 1, my next column
is
a VLookup function that gets the value from the next column (if the
Countif
<0), or else if puts an alternate value.

Here is the problem. If A2 = 123, and if the column does NOT have 123,
but
it DOES have 0123, the result is returned as 1. Of course, then my lookup
returns an N/A since it looks for 123 and it is not in the list.

Is there any way I can do use Countif to only find EXACT matches, or would
somebody have a suggestion on how to get around this.

I thank you greatly in advance for your responses.
--
BB