Verification of cell content

[Thore]

Hello all,

I am looking for a way to check whether a cell contains
an "accepted" value or not. To clarify:

In column A city names are entered. In column X I have a
list of possible cities. In column B I would like to have
a function that shows e.g. "ok" if the entered city name
from column A appears somwhere in column X. Otherwise
column B should show e.g. "wrong entry".

Which function can I use for this?

The reason for this verification is that later column A
will serve as lookup_value and column X as lookup_vector.
The problem is that the following would mess up my results
for this lookup:
"If LOOKUP can't find the lookup_value, it matches the
largest value in lookup_vector that is less than or equal
to lookup_value."


Thanks for any help in advance,
Thore

 

[Leo Heuser]

Hello Thore

Here's one way:

Assuming your list in X2:X8, and cities to
be entered in A2 and down.

In B2 enter this array formula:

=OR(A2=$X$2:$X$8)

The formula must be entered with <Shift><Ctrl><Enter>,
also if edited later.

If the city entered in column A is OK, the corresponding
cell in column B will display "True", if not OK, it will display
"False"

 

[Guest]

To answer your question, the following will return a number showing the position of the City name entered in your list in column X, or #N/A if it isn't there:

B2: =MATCH(A2,X:X,0)

So you can get the result you want with the following:

B2: =IF(ISNA(=MATCH(A2,X:X,0)),"wrong entry","ok")

For your LOOKUP problem, though, if you use VLOOKUP instead of LOOKUP, you can specify an exact match, so that the VLOOKUP formula will return #N/A if the lookup entry is not in the table, which again you can test for exactly as above.

VLOOKUP's syntax is slightly different from LOOKUP. It is 2-dimensional rather than one dimensional, so you need to specify a range including the lookup index and the data to be returned, and the number of the column in the range to be returned (the index will be 1). Details are well explained in the help file.