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. |
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. |
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. |
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. |
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? |
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. |
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. |
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. |
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. |
All times are GMT +1. The time now is 02:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com