Excel find results not in one array from another

Question

Without using VBA, is there any way to find and output the results missing from one array compared to another into cells.

I have two sheets, on Sheet1 is a list of numbers in column B and "N/A" In column J if we are done with them. On Sheet2 is we paste a list of numbers in column B. I need to be able to find the numbers missing from Sheet2 that do not have an "N/A" tag in Sheet1 and output them to column C.

I tried {=INDEX(Sheet1!B:B,MATCH(1,(Sheet1!B:B<>B:B)*(Sheet1!J:J<>"N/A"),0))} but (Sheet1!B:B<>B:B) doesn't seem to be doing what i think it should. I understand that the above formula won't give more than 1 result yet. I would need to add something to eliminate the results already in column C, but one step at a time.

Answer 1

Sheet1!B:B<>B:B compares corresponding cells in two sheets. Unless the numbers appear at the same exact rows in both sheets, your code will not work.

I don't think it is possible to deal with this with array formula, because Match doesn't accept an array as its first argument. But you can enter this in C1 and copy and paste to the whole column:

=IF(ISERROR(MATCH(Sheet1!B1,Sheet2!B:B,0)),IF(Sheet1!J1<>"N/A",Sheet1!B1,""),"")

There will be empty cells though.