Общая формула
(=INDEX(rng1,MATCH(1,($A1=rng2)*(B$1=rng3),0)))
Резюме
Чтобы выполнить поиск по нескольким критериям и перенести результаты в таблицу, можно использовать формулу массива на основе ИНДЕКС и ПОИСКПОЗ. В показанном примере формула в G5:
(=INDEX(amount,MATCH(1,($F5=location)*(G$4=date),0)))
Обратите внимание, что эта формула представляет собой формулу массива и должна вводиться с помощью Ctrl + Shift + Enter.
В этой формуле также используются три именованных диапазона: местоположение = B5: B13, сумма = D5: D13, дата = C5: C13.
Объяснение
Ядром этой формулы является ИНДЕКС, который извлекает значение из именованного диапазона «количество» (B5: B13):
=INDEX(amount,row_num)
где row_num определяется функцией ПОИСКПОЗ и некоторой логической логикой:
MATCH(1,($F5=location)*(G$4=date),0)
В этом фрагменте местоположение в F5 сравнивается со всеми местоположениями, а дата в G4 сравнивается со всеми датами. Результатом в каждом случае является массив значений ИСТИНА и ЛОЖЬ. Когда эти массивы умножаются вместе, математическая операция приводит значения TRUE и FALSE к единице и нулям, так что поисковый массив, переходящий в MATCH, выглядит следующим образом:
(1;0;0;0;0;0;0;0;0)
ПОИСКПОЗ настроено на совпадение 1 как точное совпадение и возвращает позицию в ИНДЕКС как номер строки. Число 1 работает для значения поиска, потому что теперь массив содержит только 1 и 0, как показано выше.
F5 и G4 вводятся как смешанные ссылки, так что формулу можно копировать через таблицу без изменений.
Транспонировать с помощью специальной пасты
Если вам просто нужно переставить таблицу один раз, не забывайте, что вы можете использовать специальную вставку.
