Search for occurence of any item in table
 

I have data in cells d2:ac1000. I have another table of items I am looking
for in a1:a7. For each row of my data, I'd like to know if there's any
matches to items in the table. I know I could sum a bunch of Countifs, as
in:

=countif(d2:ac1000,a1)+countif(d2:ac1000,a2)+...

Is there a simpler formula I could use that would support expansion of the
table more easily?

Thanks,
Fred.

Search for occurence of any item in table
 

Try this:

=SUMPRODUCT(COUNTIF(D2:AC1000,A1:A7))

--
Biff
Microsoft Excel MVP


"Fred Smith" wrote in message
...
I have data in cells d2:ac1000. I have another table of items I am looking
for in a1:a7. For each row of my data, I'd like to know if there's any
matches to items in the table. I know I could sum a bunch of Countifs, as
in:

=countif(d2:ac1000,a1)+countif(d2:ac1000,a2)+...

Is there a simpler formula I could use that would support expansion of the
table more easily?

Thanks,
Fred.

Search for occurence of any item in table
 

Thanks Biff. Just what I was looking for.

Fred.

"T. Valko" wrote in message
...
Try this:

=SUMPRODUCT(COUNTIF(D2:AC1000,A1:A7))

--
Biff
Microsoft Excel MVP


"Fred Smith" wrote in message
...
I have data in cells d2:ac1000. I have another table of items I am looking
for in a1:a7. For each row of my data, I'd like to know if there's any
matches to items in the table. I know I could sum a bunch of Countifs, as
in:

=countif(d2:ac1000,a1)+countif(d2:ac1000,a2)+...

Is there a simpler formula I could use that would support expansion of
the table more easily?

Thanks,
Fred.

Search for occurence of any item in table
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Fred Smith" wrote in message
...
Thanks Biff. Just what I was looking for.

Fred.

"T. Valko" wrote in message
...
Try this:

=SUMPRODUCT(COUNTIF(D2:AC1000,A1:A7))

--
Biff
Microsoft Excel MVP


"Fred Smith" wrote in message
...
I have data in cells d2:ac1000. I have another table of items I am
looking for in a1:a7. For each row of my data, I'd like to know if
there's any matches to items in the table. I know I could sum a bunch of
Countifs, as in:

=countif(d2:ac1000,a1)+countif(d2:ac1000,a2)+...

Is there a simpler formula I could use that would support expansion of
the table more easily?

Thanks,
Fred.