excelmatchvlookupnotin

Equivalent of NOT In or IN in Excel like in SQL, I don't want use vlookup or match

So I have two sets of data (from two different Excel files), the first column of each Excel file are all same type of data, numbers.

I want to use something equivalent of IN or NOT IN in SQL in Excel, since I don't think I want to use match or vlookup. I just want to know if the data in first Excel file (rows that belong to the first column) are in the first column of second Excel file.

Then I want to return possibly as true or false (not sure even I need that).

What is the best way to accomplish in Excel? Should I use VLOOKup since I'm comparing two different Excel files. And if I just copy the first row from second Excel file and put in first Excel file, is there something I can use equivalent of IN or NOT IN?

Iferror or match?

Solution

  • For IN we use:

    =ISNUMBER(MATCH(A1,[yourOtherFile]Sheet1!A:A,0))

    Then for Not In:

    =ISERROR(MATCH(A1,[yourOtherFile]Sheet1!A:A,0))