Проверить списки на не достающие позиции с помощью функций ЕСЛИ, СЧЕТЕСЛИ, СТРОКА, ИНДЕКС и НАИМЕНЬШИЙ
Проверить списки на не достающие позиции с помощью функций ЕСЛИ, СЧЕТЕСЛИ, СТРОКА, ИНДЕКС и НАИМЕНЬШИЙ
=ЕСЛИ(СЧЁТЕСЛИ($C$5:$C$10;D5)=0;СТРОКА(D5);»есть в списке 1″)
 
=ИНДЕКС($D$5:$D$10;НАИМЕНЬШИЙ($E$5:$E$10;G5)-4)

Задача: Найти недостающие записи при сравнении двух списков. 

Для решения задачи будут использоваться функции:

ЕСЛИ(), СЧЕТЕСЛИ(), СТРОКА(), ИНДЕКС() и НАИМЕНЬШИЙ()

Логика решения:

В первую очередь определяем количество не совпадений в столбцах C и D  используя функцию СЧЕТЕСЛИ() все что не найдено будет с значением 0.

=СЧЁТЕСЛИ($C$5:$C$10;D5

все не найденные позиции как раз нам и нужны

Второе действие надо определить строку отсутствующей (то-есть с 0) записи в списке 

Для этого используем логическую функцию ЕСЛИ() и функцию  СТРОКА()

Логическое выражение  

СЧЁТЕСЛИ($C$5:$C$10;D5)=0

Нам надо найти все 0 и проставить номера строк  не найденных элементов из списка, при истинности логического выражения мы задаем действие которое определяет строку не найденного элемента. При истине ссылка на ячейку с функцией СТРОКА(D7) .

=СТРОКА(D7)

 

да и если ЛОЖЬ тогда пишем «есть в списке 1» 

Полностью формула выглядит так

=ЕСЛИ(СЧЁТЕСЛИ($C$5:$C$10;D5)=0;СТРОКА(D5);»есть в списке 1″)

Для формирования нужного нам списка отсутствующих элементов мы будем использовать замечательные функции НАИМЕНЬШИЙ() и 

ИНДЕКС()

Так как у нас есть номера строк или цифры в столбике E то мы можем использовать диапазон $E$5:$E$10 таблички или списка найти номер строки наименьшего числа с индексу из столбца G (номер строки определяется от первой строки)

G5- это номер индекса который нам надо вывести то есть от первого до последнего нужного элемента 1 до 10.

(таким образом  можно создавать ТОП продаж или ещё что то интересное) 

да и надо обязательна вычесть количество строк (4) до первой строки списка для  правильного подсчета.

НАИМЕНЬШИЙ($E$5:$E$10;G5)-4

После определения строки нужной нам позиции мы используем функцию ИНДЕКС() для вывода не номера строки а наименование не достающего элемента.

Указываем диапазон $D$5:$D$10 для поиска и номер строки НАИМЕНЬШИЙ($E$5:$E$10;G5)-4  элемента для формирования нового списка недостающих элементов.

Полностью формула выглядит так:

=ИНДЕКС($D$5:$D$10;НАИМЕНЬШИЙ($E$5:$E$10;G5)-4)

 

Вы можете скачать файл с готовым решением.