| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I skip first the first occurence in an index search? | Excel Worksheet Functions | |||
| vlookup formula fails to return data search item is text | Excel Worksheet Functions | |||
| Pivot Table Calculated Item | Excel Discussion (Misc queries) | |||
| Have a Pivot Table Include a "% of an item" | Excel Worksheet Functions | |||
| search multiple worksheets for an item and return the Wsheets name | Excel Worksheet Functions | |||
![Fred Smith[_4_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
