Selected Answer

The purpose of VLOOKUP is to look up a value in one column and return a corresponding value from another. Like a telephone book where you look up a name and return the number written next to it. To check if a value exists in a column you would better use MATCH. The formula below checks if a match is returned and displays "END". If there is no match MATCH will return a #N/A error for which you can check using the ISNA() function.

`=IF(ISNA(MATCH(TRIM(A2),'END Match'!A:A,0)),"","END")`

However, you could achieve the same result with VLOOKUP. Here is the formula.

`=IF(LEN(IFERROR(VLOOKUP(TRIM(A2),'END Match'!A:A,1,FALSE),"")),"END")`

The basic *VLOOKUP(TRIM(A2),'END Match'!A:A,1,FALSE)* is embedded in an ISERROR function which converts the result to "" if no match is found. But if VLOOKUP does return a value (from column 1 of the lookup range) it would be text, not a number. Therefore you can't use *IF(VLOOKUP(TRIM(A2),'END Match'!A:A,1,0),"END","")* which asks if the result is a number different from zero. Wel, it isn' at all and that is the likely source of the Type error you received. To refresh, this syntax is equivalent to *IF(VLOOKUP(TRIM(A2),'END Match'!A:A,1,0)***<>0**,"END","") where the <>0 can be omitted because it is implied.

In your workbook the result is never a number. Best case it is a string that evaluates to a number. Therefore my formula tests the length of the result. Of course, the length is always a number. Therefore the <>0 can be omitted. Depending upon the result of the Len() function, either "END" or "" is displayed. The formula is longer because, as I said above, VLOOKUP isn't really intended for this kind of use.