Получить строки, чьи значения не найдены на другом листе

Я пытаюсь сделать следующее. У меня есть Лист 1 и Лист 2.

На третьем листе мне нужно получить строки из листа 1, где ИНСТРУМЕНТ листа 1 не найден в ФИМ листа 2 (например, так как ни один из ИНСТРУМЕНТОВ листа 1 не находится в ФИМ листа 2, лист 3 будет содержать строки листа 1). Как я могу это сделать? Спасибо

Функции поиска, такие как VLOOKUP(), можно использовать для поиска значения и, следовательно, возврата какого-либо результата, или для его отсутствия, и, следовательно, возврата #ERROR!. Очевидно, это обычно используется в “положительном” смысле, чтобы найти какой-либо результат, но его также можно использовать в “отрицательном” смысле, чтобы не искать какой-либо результат, а скорее искать #ERROR!.

Также можно проверить наличие #ERROR! с помощью функции ISERROR(). Иногда можно использовать IFERROR(), но многие ситуации не позволяют использовать механизм IFERROR(), и это одна из них. Здесь будет использована ISERROR() как более сложный NOT(). Тогда, если ИНСТРУМЕНТ не найден, тест IF() будет успешен, так как он ищет такой сбой. TRUE-результат в этом случае – это значение ячейки поиска. Если тест не проходит (ИНСТРУМЕНТ существует там), возвращается “”.

Следующая формула делает это:

=IF(ISERROR(VLOOKUP(A2:A9,$F$2:$F$5,1,FALSE)),A2:A9,"")

Если у вас есть функция SPILL, то только первая ячейка нуждается в формуле. Если нет, вставьте ее в первую ячейку и используйте {CSE}, чтобы выполнить старый метод создания массива.

Обратите внимание, что формула будет выдавать результаты в разных частях диапазона вывода. Для их группировки вам нужно отсортировать их так, чтобы пустые оказались внизу списка, чтобы они не мешали ни легкому использованию списка, ни его внешнему виду, что может быть более важно для вас.

Тем не менее, практически всё, что вы используете для тех, что существуют, как “” в формуле выше, или даже символ, который имеет гораздо больший вес, как UNICODE(160), который является пробелом, все равно будет сортироваться в начале списка. Вы можете подойти к этому многими способами, включая копирование, затем вставку как значения, затем сортировку, но чтобы сделать это с помощью формулы, чтобы вам не нужно было взаимодействовать, вы можете попробовать что-то вроде следующего:

=SUBSTITUTE(SORT(IF(ISERROR(VLOOKUP(A2:A9,$F$2:$F$5,1,FALSE)),A2:A9,"ZZZZZZZZZZ")),"ZZZZZZZZZZ","")

Вы используете строку символов, которая всегда должна сортироваться в конец вашего списка. “ZZZZZZZZZZ” используется в этой формуле. Затем вы SORT(), чтобы заставить все это оказаться в конце, а те, что вы хотите видеть первыми – наверху. Затем вы SUBSTITUTE() для этой строки на пустую, как “”, и все эти “исчезают”… Но на самом деле не исчезают, а лишь визуально.

(Очевидно, вы должны использовать реальные ссылки в формуле, которую выберете или адаптируете.)