How to Return the First or Last Match in an Array

http://support.microsoft.com/kb/214069

You can use the LOOKUP() function to search for a value within an array of sorted data and return the corresponding value contained in that position within another array. If the lookup value is repeated within the array, it returns the last match encountered. This behavior is true for the VLOOKUP(), HLOOKUP(), and LOOKUP() functions. 

To find the first value instead of the last value in an array, use the INDEX() and MATCH() functions.

=IF(INDEX('CMS LILO DATA'!$AA$2:$AA$500, MATCH(A4, 'CMS LILO DATA'!$A$2:$A$500,0),1)=1,1,"")


=IF(
        INDEX('Sheet1'!$A$2:$A$500, MATCH(A4, 'Sheet2'!$A$2:$A$500,0),1)
           =1,1,"")