Общая формула
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete, partial_expanding,0)),0))
Резюме
Чтобы сравнить два списка и извлечь отсутствующие значения из одного списка в другой, вы можете использовать формулу массива, основанную на ИНДЕКС и ПОИСКПОЗ. В показанном примере последнее значение в списке B находится в ячейке D11. Копируемая формула в D12:
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete,$D$5:D11,0)),0))
где «полный» - именованный диапазон B5: B15.
Примечание: это формула массива, и ее нужно вводить с помощью Ctrl + Shift + Enter.
Объяснение
Ядром этой формулы, работающей изнутри наружу, является внутреннее выражение MATCH:
ISNA(MATCH(complete,$D$5:D11,0)
Здесь функция ПОИСКПОЗ используется для сравнения всех «полных» значений с частичным списком. Именованный диапазон «полный» используется для значений поиска, а частичный список используется в качестве массива поиска. Обратите внимание, однако, что частичный список вводится как расширяющийся диапазон, который заканчивается «на одну ячейку выше» ячейки формулы. Это позволяет частичному списку расширяться и включать новые значения, которые появляются под исходным списком.
Результатом MATCH является массив чисел и # N / A ошибок, где числа представляют значения в полном списке, которые существуют в частичном списке; а ошибки представляют собой пропущенные значения:
(1;#N/A;2;3;#N/A;4;5;6;#N/A;7;#N/A)
Функция ISNA используется для преобразования этих результатов в массив значений TRUE и FALSE. В этом массиве TRUE соответствует отсутствующим значениям, а FALSE соответствует существующим значениям:
(FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)
Функция ISNA возвращает этот массив внешнему MATCH как поисковый массив. Функция MATCH всегда возвращает первое найденное совпадение, поэтому match будет возвращать позицию (строку) первого найденного отсутствующего значения. Этот результат возвращается в INDEX как номер строки, а именованный диапазон "завершен" предоставляется в виде массива.
В ячейке D12 первое найденное отсутствующее значение - "киви" в строке 2, поэтому мы имеем:
=INDEX(complete,2) // returns "kiwi"
В D13 «киви» теперь включен в расширяющуюся ссылку, поэтому первое отсутствующее значение - «груша»:
=INDEX(complete,5) // returns "pear"
И так далее. После добавления всех отсутствующих значений формула вернет ошибку # N / A.
