Proč jsou INDEX a MATCH lepší než VLOOKUP v Excelu

Již dříve jsme začátečníkům vysvětlili, jak používat základní funkce VLOOKUP (anglicky VLOOKUP, zkratka znamená „funkce vertikálního vyhledávání“). A zkušeným uživatelům bylo ukázáno několik složitějších vzorců.

A v tomto článku se pokusíme poskytnout informace o další metodě práce s vertikálním vyhledáváním.

Možná se ptáte: "Proč je to nutné?". A to je nezbytné pro zobrazení všech možných způsobů vyhledávání. Kromě toho četná omezení funkce VLOOKUP často brání dosažení požadovaného výsledku. V tomto ohledu je INDEX() MATCH() mnohem funkčnější a rozmanitější a má také méně omezení.

Základy INDEX MATCH

Protože účelem této příručky je ukázat, jak dobrá tato funkce je, my Podívejme se na základní informace týkající se principů jeho fungování. A ukážeme příklady a také zvážíme proč, je to lepší než VLOOKUP ().

Syntaxe a použití funkce INDEX

Tato funkce pomáhá při hledání požadované hodnoty mezi určenými oblastmi hledání na základě čísla sloupce nebo řádku. Syntaxe:

=INDEX(pole, číslo řádku, číslo sloupce):

• pole – oblast, ve které bude vyhledávání probíhat;
• číslo řádku – číslo řádku, který má být prohledán v zadaném poli. Pokud je číslo řádku neznámé, musí být uvedeno číslo sloupce;
• číslo sloupce – číslo sloupce, který má být v zadaném poli nalezen. Pokud je hodnota neznámá, je vyžadováno číslo řádku.

Příklad jednoduchého vzorce:

=INDEX(A1:S10,2,3;XNUMX;XNUMX)

Funkce bude vyhledávat v rozsahu od A1 do C10. Čísla ukazují, ze kterého řádku (2) a sloupce (3) zobrazit požadovanou hodnotu. Výsledkem bude buňka C2.

Docela jednoduché, že? Ale když pracujete se skutečnými dokumenty, je nepravděpodobné, že budete mít informace týkající se čísel sloupců nebo buněk. K tomu slouží funkce MATCH().

Syntaxe a použití funkce MATCH

Funkce MATCH() vyhledá požadovanou hodnotu a zobrazí její přibližné číslo v určené oblasti hledání.

Syntaxe searchpos() vypadá takto:

=MATCH(hodnota k vyhledání, pole k vyhledání, typ shody)

• hledaná hodnota – číslo nebo text, který má být nalezen;
• prohledávané pole – oblast, kde bude vyhledávání probíhat;
• typ shody – určuje, zda se má hledat přesná hodnota nebo hodnoty, které jsou jí nejblíže:
  • 1 (nebo není zadána žádná hodnota) – vrátí největší hodnotu, která je rovna nebo menší než zadaná hodnota;
  • 0 – zobrazuje přesnou shodu s hledanou hodnotou. V kombinaci INDEX() MATCH() budete téměř vždy potřebovat přesnou shodu, takže píšeme 0;
  • -1 – Zobrazuje nejmenší hodnotu, která je větší nebo rovna hodnotě zadané ve vzorci. Řazení se provádí v sestupném pořadí.

Například v rozsahu B1:B3 jsou registrovány New York, Paříž, Londýn. Vzorec níže ukáže číslo 3, protože Londýn je třetí na seznamu:

=EXPOSE(Londýn,B1:B3,0;XNUMX)

Jak pracovat s funkcí INDEX MATCH 

Pravděpodobně jste již začali chápat princip, na kterém je postavena společná práce těchto funkcí. Zkrátka tedy INDEX() hledá požadovanou hodnotu mezi určenými řádky a sloupci. A MATCH() ukazuje čísla těchto hodnot:

=INDEX(sloupec, ze kterého je vrácena hodnota, MATCH(hodnota k vyhledávání, sloupec k vyhledávání, 0))

Stále máte problém pochopit, jak to funguje? Možná to lépe vysvětlí příklad. Předpokládejme, že máte seznam světových metropolí a jejich obyvatel:

Abychom zjistili velikost populace určitého hlavního města, například hlavního města Japonska, použijeme následující vzorec:

=INDEX(C2:C10; MATCH(Japonsko; A2:A10,0;XNUMX))

Vysvětlení:

• Funkce MATCH() hledá hodnotu – „Japonsko“ v poli A2:A10 a vrací číslo 3, protože Japonsko je třetí hodnotou v seznamu. 
• Toto číslo jde dočíslo řádku” ve vzorci INDEX() a říká funkci, aby vytiskla hodnotu z tohoto řádku.

Výše uvedený vzorec se tedy stává standardním vzorcem INDEX(C2:C10,3;XNUMX). Vzorec hledá v buňkách C2 až C10 a vrací data ze třetí buňky v tomto rozsahu, tedy C4, protože odpočítávání začíná od druhého řádku.

Nechcete do vzorce předepsat název města? Poté jej napište do libovolné buňky, řekněme F1, a použijte jej jako odkaz ve vzorci MATCH(). A skončíte u dynamického vyhledávacího vzorce:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

Důležité! Počet řádků v řada INDEX() musí být stejný jako počet řádků v uvažované pole v MATCH(), jinak dostanete nesprávný výsledek.

Počkejte chvíli, proč nepoužít vzorec VLOOKUP()?

=VLOOKUP(F1; A2:C10; 3; False)

 Jaký má smysl ztrácet čas snahou přijít na všechny tyto složitosti INDEX MATCH?

V tomto případě je jedno, kterou funkci použít. Toto je pouze příklad k pochopení toho, jak funkce INDEX() a MATCH() spolupracují. Další příklady ukážou, čeho jsou tyto funkce schopny v situacích, kdy je funkce VLOOKUP bezmocná. 

INDEX MATCH nebo VLOOKUP

Při rozhodování, který vyhledávací vzorec použít, se mnozí shodují na tom, že INDEX() a MATCH() jsou mnohem lepší než funkce VLOOKUP. Mnoho lidí však stále používá VLOOKUP(). Za prvé je VLOOKUP() jednodušší a za druhé uživatelé plně nechápou všechny výhody práce s INDEX() a MATCH(). Bez těchto znalostí nebude nikdo souhlasit s tím, že bude trávit čas studiem složitého systému.

Zde jsou hlavní výhody INDEX() a MATCH() oproti VLOOKUP():

• Hledejte zprava doleva. VLOOKUP() nemůže vyhledávat zprava doleva, takže hledané hodnoty musí být vždy ve sloupcích úplně vlevo. Ale INDEX() a MATCH() to zvládnou bez problému. Tento článek vám prozradí, jak to vypadá v praxi: jak najít požadovanou hodnotu na levé straně.

1. Bezpečné přidávání nebo odebírání kolon. Vzorec VLOOKUP() zobrazuje nesprávné výsledky při odebírání nebo přidávání sloupců, protože funkce VLOOKUP() potřebuje přesné číslo sloupce, aby byla úspěšná. Při přidávání nebo odebírání sloupců se přirozeně mění i jejich čísla. 

A ve vzorcích INDEX() a MATCH() je zadán rozsah sloupců, nikoli jednotlivé sloupce. Díky tomu můžete bezpečně přidávat a odebírat sloupce, aniž byste museli vzorec pokaždé aktualizovat.

1. Bez omezení objemu vyhledávání. Při použití funkce VLOOKUP() nesmí celkový počet vyhledávacích kritérií přesáhnout 255 znaků, jinak dostanete #HODNOTA! Pokud tedy vaše data obsahují velký počet znaků, INDEX() a MATCH() jsou nejlepší volbou.
2. Vysoká rychlost zpracování. Pokud jsou vaše stoly relativně malé, je nepravděpodobné, že si všimnete nějakého rozdílu. Pokud však tabulka obsahuje stovky nebo tisíce řádků, a proto existují stovky a tisíce vzorců, INDEX () a MATCH () si poradí mnohem rychleji než VLOOKUP (). Faktem je, že Excel zpracuje pouze sloupce uvedené ve vzorci, místo aby zpracoval celou tabulku. 

Vliv funkce VLOOKUP() na výkon bude zvláště patrný, pokud váš list obsahuje velké množství vzorců, jako je VLOOKUP() a SUM(). K analýze každé hodnoty v poli jsou vyžadovány samostatné kontroly funkcí VLOOKUP(). Excel tedy musí zpracovávat obrovské množství informací a to výrazně zpomaluje práci.

Příklady vzorců 

Na užitečnost těchto funkcí jsme již přišli, takže můžeme přejít k nejzajímavější části: k aplikaci znalostí v praxi.

Vzorec pro vyhledávání zprava doleva

Jak již bylo zmíněno, VLOOKUP nemůže tuto formu vyhledávání provádět. Pokud tedy požadované hodnoty nejsou ve sloupci zcela vlevo, funkce VLOOKUP() nepřinese výsledek. Funkce INDEX() a MATCH() jsou všestrannější a umístění hodnot pro jejich fungování nehraje velkou roli.

Například přidáme sloupec pořadí na levou stranu naší tabulky a pokusíme se zjistit, jaké pořadí z hlediska počtu obyvatel zaujímá hlavní město Naše země.

Do buňky G1 zapíšeme hodnotu, která má být nalezena, a poté pomocí následujícího vzorce vyhledáme v rozsahu C1:C10 a vrátíme odpovídající hodnotu z A2:A10:

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

Výzva. Pokud plánujete použít tento vzorec pro více buněk, ujistěte se, že jste opravili rozsahy pomocí absolutního adresování (např. $A$2: $A$10 a $C$2: 4C$10).

INDEX VÍCE EXPOSED VÍCE EXPOSED  pro vyhledávání ve sloupcích a řádcích

Ve výše uvedených příkladech jsme tyto funkce použili jako náhradu za VLOOKUP() k vrácení hodnot z předdefinovaného rozsahu řádků. Ale co když potřebujete provést maticové nebo oboustranné vyhledávání?

Zní to složitě, ale vzorec pro takové výpočty je podobný standardnímu vzorci INDEX() MATCH() s jediným rozdílem: vzorec MATCH() musí být použit dvakrát. Poprvé pro získání čísla řádku a podruhé pro získání čísla sloupce:

=INDEX(pole, SHODA(svislá hledaná hodnota, hledaný sloupec, 0), HODNOTA(horizontální hledaná hodnota, hledaný řádek, 0))

Podívejme se na níže uvedenou tabulku a zkusme sestavit vzorec INDEX() EXPRESS() EXPRESS() za účelem zobrazení demografických údajů v konkrétní zemi za vybraný rok.

Cílová země je v buňce G1 (vertikální vyhledávání) a cílový rok je v buňce G2 (horizontální vyhledávání). Vzorec bude vypadat takto:

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

Jak tento vzorec funguje

Stejně jako u jiných složitých vzorců je snazší je pochopit, když je rozložíte na jednotlivé rovnice. A pak můžete pochopit, co každá jednotlivá funkce dělá:

• MATCH(G1;A2:A11,0;XNUMX) – hledá hodnotu (G1) v rozsahu A2:A11 a ukazuje číslo této hodnoty, v našem případě je to 2;
• HLEDAT(G2;B1:D1,0;XNUMX) – hledá hodnotu (G2) v rozsahu B1:D1. V tomto případě byl výsledek 3.

Nalezená čísla řádků a sloupců jsou odeslána na odpovídající hodnotu ve vzorci INDEX():

=INDEX(B2:D11,2,3;XNUMX;XNUMX)

V důsledku toho máme hodnotu, která je v buňce na průsečíku 2 řádků a 3 sloupců v rozsahu B2:D11. A vzorec ukazuje požadovanou hodnotu, která je v buňce D3.

Vyhledávání podle více podmínek pomocí INDEX a MATCH

Pokud jste četli našeho průvodce VLOOKUP(), pravděpodobně jste vyzkoušeli více vyhledávacích vzorců. Tento způsob vyhledávání má ale jedno významné omezení – nutnost přidat pomocný sloupec.

Ale dobrá zpráva je, že Pomocí INDEX() a MATCH() můžete hledat více podmínek, aniž byste museli upravovat nebo měnit svůj list.

Zde je obecný vzorec vyhledávání s více podmínkami pro INDEX() MATCH():

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

Poznámka: tento vzorec je nutné použít společně s klávesovou zkratkou CTRL+SHIFT+ENTER.

Předpokládejme, že potřebujete najít hodnotu, kterou hledáte, na základě 2 podmínek: Kupující и Produkt.

To vyžaduje následující vzorec:

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

V tomto vzorci je C2:C10 rozsah, ve kterém bude vyhledávání probíhat, F1 - tato podmínka, A2:A10 — je rozsah pro srovnání stavu, F2 - podmínka 2, V2: V10 – rozsah pro srovnání stavu 2.

Nezapomeňte kombinaci stisknout na konci práce se vzorcem CTRL+SHIFT+ENTER – Excel automaticky uzavře vzorec se složenými závorkami, jak ukazuje příklad:

Pokud pro svou práci nechcete použít maticový vzorec, přidejte do vzorce další INDEX() a stiskněte ENTER, bude to vypadat jako v příkladu:

Jak tyto vzorce fungují

Tento vzorec funguje stejně jako standardní vzorec INDEX() MATCH(). Chcete-li hledat více podmínek, jednoduše vytvoříte více podmínek False a True, které představují správné a nesprávné jednotlivé podmínky. A pak tyto podmínky platí pro všechny odpovídající prvky pole. Vzorec převede argumenty False a True na 0 a 1 a vydá pole, kde 1 jsou odpovídající hodnoty, které byly nalezeny v řetězci. MATCH() najde první hodnotu, která odpovídá 1, a předá ji do vzorce INDEX(). A to zase vrátí již požadovanou hodnotu v určeném řádku z požadovaného sloupce.

Vzorec bez pole závisí na schopnosti INDEX() zpracovat je samostatně. Druhý INDEX() ve vzorci odpovídá falsy (0), takže předá celé pole s těmito hodnotami vzorci MATCH(). 

Toto je poměrně dlouhé vysvětlení logiky tohoto vzorce. Pro více informací si přečtěte článek "INDEX MATCH s více podmínkami".

AVERAGE, MAX a MIN v INDEXU a MATCH

Excel má své vlastní speciální funkce pro nalezení průměrů, maxim a minim. Ale co když chcete získat data z buňky spojené s těmito hodnotami? V tomto případě AVERAGE, MAX a MIN musí být použity ve spojení s INDEX a MATCH.

INDEX MATCH a MAX

Chcete-li najít největší hodnotu ve sloupci D a zobrazit ji ve sloupci C, použijte vzorec: 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

INDEX MATCH a MIN

Chcete-li najít nejmenší hodnotu ve sloupci D a zobrazit ji ve sloupci C, použijte následující vzorec:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

SEARCH INDEX a SERPENT

Chcete-li najít průměrnou hodnotu ve sloupci D a zobrazit tuto hodnotu v C:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

V závislosti na tom, jak jsou vaše data zapsána, je třetí argument funkce MATCH() buď 1, 0 nebo -1:

• pokud jsou sloupce seřazeny vzestupně, nastavte 1 (vzorec pak vypočítá maximální hodnotu, která je menší nebo rovna průměrné hodnotě);
• pokud je řazení sestupně, pak -1 (vzorec vypíše minimální hodnotu, která je větší nebo rovna průměru);
• pokud vyhledávací pole obsahuje hodnotu, která se přesně rovná průměru, nastavte ji na 0. 

 V našem příkladu je populace seřazena v sestupném pořadí, takže vložíme -1. A výsledkem je Tokio, protože hodnota populace (13,189 000) je nejblíže průměrné hodnotě (XNUMX).

VLOOKUP() může také provádět takové výpočty, ale pouze jako maticový vzorec: VLOOKUP s AVERAGE, MIN a MAX.

INDEX MATCH a ESND/IFERROR

Pravděpodobně jste si již všimli, že pokud vzorec nemůže najít požadovanou hodnotu, vyvolá chybu # N / A. Standardní chybovou zprávu můžete nahradit něčím více informativním. Například nastavte argument ve vzorci Ve XNUMX:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

Pokud pomocí tohoto vzorce zadáte údaje, které nejsou v tabulce, formulář vám poskytne zadanou zprávu.

Pokud chcete zachytit všechny chyby, pak kromě Ve XNUMX mohou být použity IFERROR:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), “Něco se pokazilo!”)

Pamatujte však, že maskování chyb tímto způsobem není dobrý nápad, protože standardní chyby hlásí porušení ve vzorci.

Doufáme, že vám náš průvodce používáním funkce INDEX MATCH() pomohl.