Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is it possible to match a cell's contents from a list ?
Is it possible to create a formula that looks up values in a list and
verifies whether they appear in a cell ? For example: A1="This is a short text entry" B1 contains a formula that looks up multiple values elsewhere to see if they appear in A1 and returns "True" if a match is found. The list contains the words "This" and "entry". - Ronald K. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is it possible to match a cell's contents from a list ?
On Mon, 11 Apr 2011 16:09:58 -0700 (PDT), kittronald wrote:
Is it possible to create a formula that looks up values in a list and verifies whether they appear in a cell ? For example: A1="This is a short text entry" B1 contains a formula that looks up multiple values elsewhere to see if they appear in A1 and returns "True" if a match is found. The list contains the words "This" and "entry". - Ronald K. If WordList represents a contiguous (no blanks) list of the words to check, then, with your data in A1: This formula must be **array-entered**: =OR(ISNUMBER(SEARCH(WordList,A1))) ---------------------------------------- To **array-enter** a formula, after entering the formula into the cell or formula bar, hold down <ctrl<shift while hitting <enter. If you did this correctly, Excel will place braces {...} around the formula. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is it possible to match a cell's contents from a list ?
Suppose your two words are in M1 and M2. Put this array* formula in
B1: =SUM(--ISNUMBER(SEARCH(M$1:M$2,A1)))0 * An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) rather than the usual <enter. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - you do not type these yourself. If you subsequently edit the formula you must use CSE again. You can accommodate more words by increasing the range M1:M2 to suit your list. Copy the formula down if you have other phrases in column A to test out. Hope this helps. Pete On Apr 12, 12:09*am, kittronald wrote: Is it possible to create a formula that looks up values in a list and verifies whether they appear in a cell ? For example: A1="This is a short text entry" B1 contains a formula that looks up multiple values elsewhere to see if they appear in A1 and returns "True" if a match is found. The list contains the words "This" and "entry". - Ronald K. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is it possible to match a cell's contents from a list ?
Ron and Pete,
Thanks guys, that does the trick. Is there a way to do that without using an array entered formula ? - Ronald K. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is it possible to match a cell's contents from a list ?
On Tue, 12 Apr 2011 13:49:03 -0700 (PDT), kittronald wrote:
Ron and Pete, Thanks guys, that does the trick. Is there a way to do that without using an array entered formula ? - Ronald K. You could use: =SUMPRODUCT(--ISNUMBER(SEARCH(WordList,A1)))0 But why? What's wrong with array-entered formulas? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is it possible to match a cell's contents from a list ?
Ron,
That'll do it. My computer is a bit slow with array entered formulas and given the number of cells this would run in, the calculation time would be quite mind numbing. Thanks again. - Ronald K. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is it possible to match a cell's contents from a list ?
On Wed, 13 Apr 2011 15:57:06 -0700 (PDT), kittronald wrote:
Ron, That'll do it. My computer is a bit slow with array entered formulas and given the number of cells this would run in, the calculation time would be quite mind numbing. Thanks again. - Ronald K. If speed is a significant issue, you could also try: =SUMPRODUCT(COUNTIF(A1,"=*"&WordList&"*"))0 With just two functions, vs 3 in the previous, it might execute faster. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is it possible to match a cell's contents from a list ?
Ron,
Just realized you replied to my last post. I switched to your last formula and it's very fast. Thanks again. - Ronald K. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is it possible to match a cell's contents from a list ?
On Fri, 29 Apr 2011 15:54:58 -0700 (PDT), kittronald wrote:
Ron, Just realized you replied to my last post. I switched to your last formula and it's very fast. Thanks again. - Ronald K. Glad to help. Thanks for the feedback. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using cell's contents as an index to a row ...? | Excel Worksheet Functions | |||
Using cell's contents as an index to a row ... ? | Excel Worksheet Functions | |||
link to a cell's contents | Excel Worksheet Functions | |||
link to a cell's contents | Excel Worksheet Functions | |||
Rotate a box with a cell's contents | Excel Discussion (Misc queries) |