0

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?

Dale K
  • 25,246
  • 15
  • 42
  • 71
SQLluv8832
  • 11
  • 4
  • Yes, use MATCH: for `In` use `=ISNUMBER(MATCH(A1,[yourOtherFile]Sheet1!A:A,0))` and for `Not In` use `=ISERROR(MATCH(A1,[yourOtherFile]Sheet1!A:A,0))` – Scott Craner Mar 10 '21 at 23:19
  • @ScottCraner hey thanks, FYI each row in the first column of two excels files are unique, so that's why I want to use something like NOT in of SQL in excel. Can you please write an answer? I will accept it. – SQLluv8832 Mar 10 '21 at 23:24

2 Answers2

1

For IN we use:

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

Then for Not In:

=ISERROR(MATCH(A1,[yourOtherFile]Sheet1!A:A,0))
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
0

Use COUNTIF combined with an IF statement to confirm the count is greater than zero.

=COUNTIF(Dataset_sheet!A:A,A1) > 0, "Yes", "No")

Dale K
  • 25,246
  • 15
  • 42
  • 71
Daniel
  • 1
  • 1
  • One note for future users, this work well with smaller (<10,000) datasets. But if the data set is much larger it will be slow, may error, or even crash Excel. All the `*IF` formula are very costly. – Scott Craner Mar 10 '21 at 23:27
  • It's giving me error when I run this, I copied the first row of second excel file into the same sheet of first excel. – SQLluv8832 Mar 10 '21 at 23:35