IF, MATCH, LOOKUP problem

[rudekid]

Been scratching my head on this for a while so if anyone could help tha
would be cool.

I have a lookup, (actually MATCH) function that tells me if the correc
spelling of a country can be found against each and every record in
set of data from an array of countries I've created. It looks lik
this:

=SUM(IF(ISERROR(MATCH(Countries,ImportData!$D3:$Z3,0)),0,1))

Because the data may be in a different format each time, I need t
check in a number of columns along the same row (D3:Z3) as the countr
could appear anywhere.

The problem I have is that I've realised sometimes the country is spel
correctly but it's in the wrong place, i.e. not in the country field.

So...what I need to do is something along the lines of:

IF column number returned by finding COUNTRY column header = colum
number returned by finding (COUNTRY ARRAY LOOKUP) do nothing, otherwis
return result from second argument.

I can do the first bit but can't do the second because I can't work ou
how to match an array with an array and return the column number o
intersection. Can anybody help?

thank

 

[Dave Peterson]

I'm not sure if this does what you want, but you may want to try it:

=SUM(IF(COUNTIF(ImportData!$D3:$Z3,countries),1,0))

This would be equivalent:
=SUMPRODUCT(--(ImportData!$D3:$Z3,countries)>0))

and this would give the total number of correct spellings found:
=SUMPRODUCT(--ImportData!$D3:$Z3,countries))

(multiples will count more than once in that last formula)