Formula Countif Index Match




I'm using Excel 2003 and this is my first post to the discussion group. I'm
trying to help a coworker and don't have a lot of experience working with

I've searched the discussion groups, etc. and have tried several different
formulas with partial success. I'm not even sure at this point if I'm going
about this the right way.

Scenario: Sheet MnthPlotters contains a master list of plotters (by name) in
Column A. The other sheets in the workbook are named by month. A query is run
each month in Access to get a list of the plotters (by name) that were used.
The list is copied and pasted into the sheet for that month in Column A.

The question I'm trying to solve is whether there is a way to flag/highlight
any new plotter names in ColA on the monthly sheets that need to be added to
the master list of plotters.

Test 1: In ColA on the monthly sheet I made up a plotter name in A2 and
tried the following formula in B2 and copied it down.


Results: B2 said Add Plotter which is correct. However, some of the other
cells in ColB are incorrect because they also said Add Plotter and/or #N/A
and they're in the master list. Not sure if this happens because of

Question: What do I need to adjust in the formula to make this work?

I also tried using a Rept Countif Index Match formula but it had too few
arguments. I gave up on trying to fix it and deleted it.

There must be a better way to do this and I'm open to any suggestions.

Thanks so much for any help you can give me!

Peo Sjoblom

What is a plotter in this regard, a type of printer?
Usually when there is a mismatch there is either different formats
like if a number is imported as text and when matched it will return N/A
because the matching list are numerical numbers. Or as you state there are
hidden characters like trailing and leading spaces.


you could try by changing the above to


and also this part


just to test,. Btw, I would probably write the formula like this


Also while you are at it, find a match that is returned as a mismatch and
compare the cells like



if you get a different number in the 2 cells that you expected to be equal
then the one with largest number has extra characters, then select that cell
and press F2, is there a trailing space then select it and copy it to a new
cell then use this


if it is 32 then there is a space, if it is something else post back




Peo Sjoblom


Hi Peo,

Thanks so much for responding. When I adjusted my formula as you suggested I
received "The formula you typed contains an error" message.

The formula that I ended up using and seems to be working is:

=IF(COUNTIF(MnthPlotters!A:A,A3)=0,"Add Plotter","")

Thanks again and have a great day!

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Vlookup and return sheet name also 2
Index, Match 1
Index, Match help 3
index match help 4
Index Match Error 1
How to expand Index/Match formula 2
COUNTIF for multiple sheets 3
HP plotters noise levels 2