Hi Everyone,

Ive been searching everywhere just to solve this one. i hope vba is not the only solution.

here it goes, what i have is a 1-100 entries of name (a2-a101). i have encoded a data validation customized formula on cell a102. here is the formula:

=ISNA(VLOOKUP(LEFT(B1190,4),LEFT(B$1:B1189,4),1,FALSE))

it always make a warning if i encode a name on the cell a102 with the same first 4 characters. eg:

cell a19
Clement Ivanov

cell a102
Clementinator

after i hit "enter" on cell 102, a message i encoded on the data validation appears and it works nicely.

here is the catch. what i cannot figure out is how can i make the data validation gives a warning ONLY if same FIRST AND LAST 4 characters were encoded.

Sample:
(wont show any warning)
cell a19:
Clement Ivanov

cell a102:
Clementinator

(will show data validation warning)
cell a19:
Clement Ivanov

cell a102:
Clem anov

in a nutshell, i want to combine left and right in the data validation +vlookup +isna. again, i hope vba is not the only answer.

Thanks in advance!