Глава 17 · 17.4
Търсене по две условия едновременно
VLOOKUP и XLOOKUP търсят по една стойност (вижте VLOOKUP връща #N/A: ето как да го разрешите и XLOOKUP: търсене наляво и със собствено съобщение). На практика често търсите по две неща едновременно: цената на букет с размер L, тарифата на клиент за определена година, наличността на продукт в определен цвят. За това има три добри начина, които работят с обикновено Enter. Четвърти начин, който често се среща в интернет, не работи в Euro-Office без допълнителна стъпка.
Като пример използваме ценовата листа на Bloemisterij Het Zonnetje. Един и същ продукт присъства в нея няколко пъти, с различни размери:
| A | B | C | |
|---|---|---|---|
| 1 | Произведение | Размер | Цена |
| 2 | Букет | S | 17,50 |
| 3 | Букет | M | 24,50 |
| 4 | Букет | L | 34,50 |
| 5 | Траурен венец | M | 65,00 |
| 6 | Траурен венец | L | 89,00 |
| 7 | Булчински букет | M | 79,50 |
Търсеният продукт е в F2 (Букет), размерът е в G2 (L). Търсим 34,50.
Начин 1: XLOOKUP със съединени стойности
=XLOOKUP(F2&"|"&G2;A2:A7&"|"&B2:B7;C2:C7;"не е намерено")
Знакът & съединява текст. F2&"|"&G2 става "Букет|L". A2:A7&"|"&B2:B7 превръща всеки ред от списъка в такава комбинация ("Букет|S", "Букет|M" и т.н.), а XLOOKUP търси в тях. Чертичката | помежду им предотвратява две различни комбинации случайно да дадат един и същ текст. Ако комбинацията не съществува, например траурен венец с размер S, ще получите "не е намерено".

Ако след въвеждането погледнете в лентата за формули, ще видите @ пред A2:A7 и B2:B7. Euro-Office го добавя автоматично; резултатът е правилен.
Начин 2: FILTER за всички съвпадения
=FILTER(C2:C7;(A2:A7=F2)*(B2:B7=G2);"няма")
FILTER връща не само първия, а всички редове, които отговарят на двете условия. Ако една комбинация се среща два пъти, ще видите двете цени една под друга. Затова се уверете, че под формулата има свободно място (вижте Списъци, които се обновяват сами, с FILTER, UNIQUE и SORT). За "или" вместо "и" използвайте знак плюс: (A2:A7="Букет")+(A2:A7="Траурен венец").
Начин 3: SUMIFS, когато комбинацията е уникална
=SUMIFS(C2:C7;A2:A7;F2;B2:B7;G2)
Звучи странно, защото не искате да събирате нищо. Но ако всяка комбинация се среща само веднъж, сборът е просто тази единствена цена. Предимството: SUMIFS е проста, бърза и се поддържа от всяка версия на Excel. Недостатъкът: ако комбинацията не съществува, получавате 0 (€ 0,00) вместо съобщение. А ако комбинацията по погрешка присъства два пъти, получавате сбора от двете цени.
Какво ще намерите в интернет: INDEX и MATCH с умножение
Много сайтове за Excel предлагат =INDEX(C2:C7;MATCH(1;(A2:A7=F2)*(B2:B7=G2);0)). В Euro-Office тази формула с обикновено Enter връща #N/A, дори когато комбинацията съществува. Ако я потвърдите с Ctrl+Shift+Enter, формулата получава фигурни скоби {…} и работи. Това лесно се забравя, затова по-добре използвайте XLOOKUP или FILTER, описани по-горе.
Търсене по ред и колона
Ако ценовата Ви листа е таблица с продуктите един под друг и размерите един до друг (матрица), използвайте INDEX с два пъти MATCH. С продуктите в A11:A13, размерите S, M и L в B10:D10 и цените в B11:D13:
=INDEX(B11:D13;MATCH(F2;A11:A13;0);MATCH(G2;B10:D10;0))
Първата MATCH намира реда на продукта, втората намира колоната на размера. INDEX взема клетката в пресечната точка.
Кой начин да изберете?
| Ситуация | Най-добър избор |
|---|---|
| Един отговор, с ясно съобщение, ако не съществува | XLOOKUP със съединени стойности |
| Файлът трябва да работи и в стара версия на Excel | SUMIFS |
| Възможни са няколко съвпадения | FILTER |
| Ценова таблица с размерите като заглавия на колони | INDEX с два пъти MATCH |
Капани
- #N/A, въпреки че комбинацията наистина е в списъка: често това е интервал след стойност ("Букет ") или число, съхранено като текст. Вижте VLOOKUP връща #N/A: ето как да го разрешите.
- Диапазони с различна дължина: =XLOOKUP(F2;A2:A7;C2:C50) връща #VALUE!. Нека всички диапазони започват и завършват на един и същ ред.
- Цели колони (A:A) в тези формули правят голям файл бавен, защото Euro-Office тогава съединява един милион реда. Използвайте фиксиран диапазон, например A2:A500. Вижте Ускоряване на бавна електронна таблица.
- Колега с Excel 2019 или по-стара версия ще вижда #NAME? при XLOOKUP и FILTER.
