Excel - return minimum value in one list not present in another

Question

I'm trying to write a formula that would return the minimum value in one list that does not exist in another list.

Unfortunately I cannot post a screenshot, so here is sample date: Column A, Rows 1-10: 189, 208, 228, 231, 235, 252, 253, 264, 274, 278 Column B, Rows 1-10: 235, 253, 264, 252, 278, 286, 274, 310, 317, 330

I would like cell A11 to return the minimum value in the range B1:B10 that is not present in the range A1:A10. Since 235, 253, 264, 252, 278, and 274 appear in column A, the value in cell A11 would be 286. Thanks for your help.

Answer 1

If your B1:B10 doesn't contains empty cells, use this one:

=MIN(IF(ISNA(MATCH(B1:B10,A1:A10,0)),B1:B10))

otherwise:

=MIN(IF((B1:B10<>"")*ISNA(MATCH(B1:B10,A1:A10,0)),B1:B10))

for both formulas press CTRL+SHIFT+ENTER to evauate it.