checking if text matches entries in a custom dictionary

[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?

 

[Mike Scobie]

Hi Ben,

See if this is what you're looking for:

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

Mike

 

[Ron Rosenfeld]

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.

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

 

[ben ritchie]

-----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

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


--ron

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?

 

[Anon]

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?

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

 

[ben ritchie]

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!

 

[ben]

cool mate, seemed to have sorted it now

thanks for all your helP!