checking if text matches entries in a custom dictionary

  • Thread starter Thread starter Ben Ritchie
  • Start date Start date
B

Ben Ritchie

Is there any way that I can write a formula to return
TRUE if a text entry in an adjacent cell matches exactly
any one of my entries into a 'dictionary' of allowed
items.
Here is an example of some of the items in my item
dictionary,
"Forks"
"Spoons"
"Plates"
"Frying pans"
"Knives"
These would not be allowed (would like to get a result of
FALSE if they were entered in),
"Fork"
"spoon"
"pan"
"Frying Forks"
"Frying Pans"
"Giraffe"

Therefore the formula should be case sensitive and non-
wildcard. Now in reality my dictionary is a list in one
column on sheet 1 in the workbook and has an excess of
100 entries so it needs to be referenced in some way
(e.g. $A$1:$A$103) rather than writing out each of the
allowed phrases separately in the formula (this would not
fit into the formula entry box). The cells I am checking
(to see if the text in them complies exactly with any
entry in the dictionary) are in column C on the active
sheet (sheet 2) and so I would start by applying the
formula to C1 and then copying it down the whole column.

So far I have tried putting in the following formulae
into D1,

=FIND(Sheet1!$A$1:$A$103,C1)

I thought FIND would be good to use since it doesn't
allow wildcard and is case-sensitive.

=ISERR(SEARCH(Sheet1!$A$1:$A$103,C1))

I think I really want a formula that would do something
like this,

=FIND(Sheet1!$A$1 or Sheet1!$A$2 or Sheet1!$A$3 or Sheet1!
$A$4 or Sheet1!$A$5 or Sheet1!$A$6.,C1))

Generally they seem to either not work or only search for
the first entry in the dictionary (which is cell A1 on
Sheet 1).

Can anyone help?
 
Hi Ben,

See if this is what you're looking for:

=ISERROR(MATCH(B1,$A$1:$A$103,0))

Mike
 
Is there any way that I can write a formula to return
TRUE if a text entry in an adjacent cell matches exactly
any one of my entries into a 'dictionary' of allowed
items.
Click to expand...

1. Name your dictionary Dictionary
Select your dictionary column.
Insert/Name/Define Dictionary.

2. With your word to check in D1, *array-enter* the following formula:

=OR(EXACT(D1,Dictionary)=TRUE)

(To array-enter a formula, hold down <ctrl><shift> while hitting <enter>.

3. The formula will return FALSE if it is not in the dictionary; otherwise it
will return TRUE.


--ron
 
-----Original Message-----
1. Name your dictionary Dictionary
Select your dictionary column.
Insert/Name/Define Dictionary.

2. With your word to check in D1, *array-enter* the following formula:

=OR(EXACT(D1,Dictionary)=TRUE)

(To array-enter a formula, hold down <ctrl><shift> while
Click to expand...
hitting said:
3. The formula will return FALSE if it is not in the dictionary; otherwise it
will return TRUE.


--ron
Click to expand...

Thanks ron,

I have been trying this method but for some reason when
it looks up the word in the dictionary it only compares
it to the word in the dictionary that is on the same row
on the other sheet?
 
ben ritchie said:
Thanks ron,

I have been trying this method but for some reason when
it looks up the word in the dictionary it only compares
it to the word in the dictionary that is on the same row
on the other sheet?
Click to expand...

Then you didn't array-enter it.
BTW, the "=TRUE" bit isn't needed:
=OR(EXACT(D1,dictionary))
is just as good.
 
argh

whenever I array enter it i now get #NUM!
i'm pretty sure i have been array entering it because it
puts the formula in {}
im not really sure what is going wrong

thanks a lot for your help anyway, it is a quality idea I
would have never thought of!
 
Back
Top