=ПРОСМОТРX(1;(A2:A7=E2)*(B2:B7=F2);C2:C7) возвращает цену из строки, где товар равен E2 и размер равен F2. Каждое сравнение проверяет все строки, их произведение равно 1 только там, где оба условия истинны, и ПРОСМОТРX (по-английски XLOOKUP) ищет эту 1. Ей нужен Excel 2021 или Microsoft 365; вариант с ИНДЕКС и ПОИСКПОЗ ниже работает в любой версии. Таблицы на этой странице показывают английскую запись, но формулы в них можно вводить и по-русски, с точкой с запятой.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Product | Size | Price | |
| 2 | Coffee | Small | $2.50 | Tea | Large | $3.00 | |
| 3 | Coffee | Large | $3.50 | ||||
| 4 | Tea | Small | $2.00 | ||||
| 5 | Tea | Large | $3.00 | ||||
| 6 | Juice | Small | $3.00 | ||||
| 7 | Juice | Large | $4.00 |
=ПРОСМОТРX(1;(A2:A7=E2)*(B2:B7=F2);C2:C7)Tea и Large сходятся в строке 5, поэтому G2 возвращает $3.00. Выберите Juice и Small: снова $3.00, но из другой строки. Добавьте четвёртый аргумент на случай, когда ни одна строка не подходит под оба условия: =ПРОСМОТРX(1;(A2:A7=E2)*(B2:B7=F2);C2:C7;"No such item").
Как работают перемноженные условия
A2:A7=E2 сравнивает каждый товар с E2 и возвращает шесть значений ИСТИНА или ЛОЖЬ. Перемножение двух таких списков превращает ИСТИНА в 1, а ЛОЖЬ в 0, и строка равна 1, только если она равна 1 в обоих. Столбец D показывает этот список, выведенный одной формулой.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Both match | Product | Size | |
| 2 | Coffee | Small | $2.50 | 0 | Tea | Large | |
| 3 | Coffee | Large | $3.50 | 0 | |||
| 4 | Tea | Small | $2.00 | 0 | |||
| 5 | Tea | Large | $3.00 | 1 | |||
| 6 | Juice | Small | $3.00 | 0 | |||
| 7 | Juice | Large | $4.00 | 0 |
=(A2:A7=F2)*(B2:B7=G2)Только D5 равна 1. Измените F2 или G2, и 1 переместится. Каждое дополнительное условие это ещё один множитель *(диапазон=значение), и условия не обязаны быть равенствами: *(C2:C7<3) добавляет «цена меньше 3». Каждый диапазон должен охватывать одни и те же строки (A2:A7, B2:B7, C2:C7): если диапазон результата другого размера, чем условия, ПРОСМОТРX возвращает #ЗНАЧ! (по-английски #VALUE!; таблицы на этой странице показывают ошибки под английскими именами).
ИНДЕКС и ПОИСКПОЗ по нескольким условиям
Для Excel 2019 и старше ПОИСКПОЗ может искать 1 в том же массиве, а ИНДЕКС возвращает цену с этой позиции.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Product | Size | Price | |
| 2 | Coffee | Small | $2.50 | Coffee | Large | $3.50 | |
| 3 | Coffee | Large | $3.50 | ||||
| 4 | Tea | Small | $2.00 | ||||
| 5 | Tea | Large | $3.00 | ||||
| 6 | Juice | Small | $3.00 | ||||
| 7 | Juice | Large | $4.00 |
=ИНДЕКС(C2:C7;ПОИСКПОЗ(1;(A2:A7=E2)*(B2:B7=F2);0))Coffee и Large это позиция 2 массива, и ИНДЕКС возвращает $3.50. В Excel 2019 и старше это формула массива: нажимайте Ctrl+Shift+Enter (Cmd+Shift+Enter на Mac) вместо Enter, и Excel покажет её в фигурных скобках. Обычный Enter там чаще всего даёт #Н/Д или #ЗНАЧ!. В Excel 365 достаточно Enter. Вариант с одним условием описан на странице ИНДЕКС и ПОИСКПОЗ.
Объединить условия в один ключ
Другой способ: превратить два условия в одно, объединив их. ВПР нужны объединённые значения во вспомогательном столбце в начале таблицы (этот вариант показан на странице ВПР). ПРОСМОТРX может объединить диапазоны прямо в формуле, так что вспомогательный столбец не нужен.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Product | Size | Price | |
| 2 | Coffee | Small | $2.50 | Juice | Large | $4.00 | |
| 3 | Coffee | Large | $3.50 | ||||
| 4 | Tea | Small | $2.00 | ||||
| 5 | Tea | Large | $3.00 | ||||
| 6 | Juice | Small | $3.00 | ||||
| 7 | Juice | Large | $4.00 |
=ПРОСМОТРX(E2&"|"&F2;A2:A7&"|"&B2:B7;C2:C7)A2:A7&"|"&B2:B7 строит шесть ключей вроде Juice|Large, и ПРОСМОТРX находит среди них Juice|Large: $4.00. Ставьте разделитель между частями. Без него «AB» и «C» дают при объединении то же «ABC», что «A» и «BC», и поиск может вернуть не ту строку.
Если нужное значение числовое и каждое сочетание встречается один раз, СУММЕСЛИМН (SUMIFS) даёт тот же ответ вообще без массива: =СУММЕСЛИМН(C2:C7;A2:A7;E2;B2:B7;F2). Когда ничего не совпало, она возвращает 0, а не ошибку, и это может скрыть опечатку.
Все совпадения через ФИЛЬТР
ПРОСМОТРX и ИНДЕКС с ПОИСКПОЗ возвращают первую подходящую строку. Когда подходят несколько строк и нужны все, используйте ФИЛЬТР (FILTER) с теми же условиями.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Quarter | Sales | Matches | ||
| 2 | North | Laptop | Q1 | 120 | Q1 | 210 | |
| 3 | North | Phone | Q1 | 210 | Q2 | 225 | |
| 4 | South | Laptop | Q1 | 95 | Q3 | 240 | |
| 5 | North | Phone | Q2 | 225 | |||
| 6 | South | Phone | Q2 | 180 | |||
| 7 | North | Laptop | Q2 | 135 | |||
| 8 | North | Phone | Q3 | 240 |
=ФИЛЬТР(C2:D8;(A2:A8="North")*(B2:B8="Phone"))Три строки подходят под North и Phone, поэтому F2 выводит их кварталы и продажи в F2:G4. Замените A3 на South, и список сократится до двух строк. В русском Excel формула пишется =ФИЛЬТР(C2:D8;(A2:A8="North")*(B2:B8="Phone")). Если ни одна строка не подходит, ФИЛЬТР возвращает #ВЫЧИСЛ! (#CALC!); третий аргумент, например "None", показывает вместо этого текст. Другие возможности описаны на странице ФИЛЬТР.
Практика: три условия
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Quarter | Sales | Region | South | |
| 2 | North | Laptop | Q1 | 120 | Product | Laptop | |
| 3 | North | Laptop | Q2 | 135 | Quarter | Q2 | |
| 4 | North | Phone | Q1 | 210 | Sales | ||
| 5 | South | Laptop | Q1 | 95 | |||
| 6 | South | Phone | Q2 | 180 | |||
| 7 | South | Laptop | Q2 | 110 | |||
| 8 | North | Phone | Q2 | 225 |
Ваша очередь: Верните в G4 продажи для региона из G1, товара из G2 и квартала из G3.
Часто задаваемые вопросы
Как использовать ПРОСМОТРX с несколькими условиями?
Перемножьте по одному сравнению на каждое условие и ищите 1: =ПРОСМОТРX(1;(A2:A7=E2)*(B2:B7=F2);C2:C7). Каждое сравнение даёт ИСТИНА или ЛОЖЬ для каждой строки, произведение равно 1 только там, где все дают ИСТИНА, и ПРОСМОТРX возвращает первую такую строку.
Как сделать ИНДЕКС и ПОИСКПОЗ по двум условиям?
Поместите те же перемноженные условия внутрь ПОИСКПОЗ: =ИНДЕКС(C2:C7;ПОИСКПОЗ(1;(A2:A7=E2)*(B2:B7=F2);0)). В Excel 2019 и старше подтверждайте её через Ctrl+Shift+Enter (Cmd+Shift+Enter на Mac).
Может ли ВПР искать по двум условиям?
Напрямую нет. Добавьте в начало таблицы вспомогательный столбец, который объединяет два значения, например =A2&"|"&B2, и ищите объединённое значение: =ВПР(E2&"|"&F2;helper_table;col;ЛОЖЬ).
Может ли СУММЕСЛИМН заменить поиск по двум условиям?
Да, когда значение числовое и каждое сочетание встречается один раз: =СУММЕСЛИМН(C2:C7;A2:A7;E2;B2:B7;F2). Если ни одна строка не подходит, она возвращает 0 вместо #Н/Д, а если сочетание встречается дважды, складывает значения.
Как искать по условиям через ИЛИ?
Складывайте условия, а не перемножайте: (A2:A7="Tea")+(A2:A7="Juice") равно 1 или больше там, где выполняется любое из них. Ищите значение больше 0, например так: =ПРОСМОТРX(ИСТИНА;((A2:A7="Tea")+(A2:A7="Juice"))>0;C2:C7).
