In Excel, How do I look for a value in one cell in a range of cells, and get a true or false statement?

Question

I am trying to compare file-names from one drive to to another drive to check for duplicates. I have created a list of file-names in both drives using command prompt and pasted those list in excel. I want to compare list 1 to list 2 and see if anything in list 1 has a match in list 2. If there is a match I want the cell next to a file-name list 1 to return a TRUE statement, and a FALSE statement if not. Please let me know how to do this. Thanks.

Answer 1

you could use a vlookup function to see if an item from one list matches an item in another list.

for example, if your first list of filenames is in A1 to A50

and your second list is in B1 to B50

over in C1 enter

=VLOOKUP(A1, $B$1:$B$50, 1, FALSE)

this will look up the value in A1 and try to find a match in cells B1:B50 and if it finds a match, return the name from column 1 (B in this case).  The "false" just tells excel to find an exact match.  If it find a match, it'll show the file name.  If it doesn't find a match you'll get a #NA error in the cell.  Now just copy/paste this formula all the way down as far as you need to

(FYI....the $ signs in the B1:B50 range tell excel to "lock" those references and not change them as you copy/paste).

if you really wanted to see "true" or "false" you can combine this with an IF statement like

=IF(ISNA(VLOOKUP(A1, $B$1:$B$50,  1, FALSE)), "FALSE", "TRUE")

so if the vlookup evaluates to #NA, then there's no match so we show FALSE, otherwise there must be a match so we'll show TRUE