Your 100 hundred numbers lives in column A. Insert in column B any symbol, for example, an asterisk all the way down next to the numbers. Your 50 numbers reside in C. In column D write the following formula: =vlookup(c1,sheet1!$a$1:$c$100,2,false). This formula will put the symbol in column D for all the numbers in column C that reside also in column A. Breaking the formula down: vlookup stands for vertical lookup. Use my answer that is in cell C1 and compare it in sheet1 (the tab you are in now). Look at everything in that sheet from a1 through c100. Then drop in the symbol that exists in column 2 if C1 resides in A. Then type the word false.

Given your inputs: 100 numbers in A1:A100, 50 numbers in B1:B50, and your request for output in C1:C100, the formula I would enter at C1 is: =IF( NOT( ISNA( VLOOKUP( A1, $B$1:$B$50, 1, FALSE))), VLOOKUP( A1, $B$1:$B$50, 1, FALSE) & ", Source at Address: A" & ROW( A1) & ", Match at: B" & ROW( INDEX( $B$1:$B$50, MATCH( A1, $B$1:$B$50, FALSE), 1)), "nothing") This is a complicated little formula. To break it down: The "IF" looks to see if there is a match between the value in A1 and any of the values in B1:B50, if there isn't, then the final "nothing" is printed. "Nothing" can be replaced with "" to show literally nothing, or any other text or valid entry. If there is a match between A1 and B1:B50, then ... The second part of the formula enters the value at A1 as a "found value", and then goes on to record the source "A1" and the "lookup location". I added text to show "Source at" and "Match at" to help 'comment' the formula, but that text can be done away with if you prefer. Copy the formula at C1 down through C100, and you should have what you have requested. This all assumes that the values in B1:B50 are UNIQUE. If not, then only the "first match" is found and returned.