Excel INDEX MATCH: Základní a pokročilé vyhledávání

V Excelu je často častou nutností přesné načítání konkrétních dat. Zatímco funkce INDEX a MATCH mají každá své vlastní silné stránky, jejich kombinace odemyká výkonnou sadu nástrojů pro vyhledávání dat. Společně usnadňují řadu vyhledávacích možností, od základních horizontálních a vertikálních vyhledávání až po pokročilejší funkce, jako je obousměrné vyhledávání, vyhledávání rozlišující malá a velká písmena a vyhledávání podle více kritérií. Párování INDEX a MATCH nabízí vylepšené možnosti ve srovnání s VLOOKUP a umožňuje širší škálu možností vyhledávání dat. V tomto tutoriálu se pojďme ponořit do hloubky možností, kterých mohou společně dosáhnout.

• Funkce INDEX
• Funkce MATCH
• Kombinujte INDEX a MATCH

• Obousměrné vyhledávání
• Levé vyhledávání
• Vyhledávání rozlišující malá a velká písmena
• Nejbližší zápas
• Vyhledávání podle více kritérií
• Vyhledávání ve více sloupcích
• Vyhledejte první neprázdnou hodnotu
• Vyhledejte první číselnou hodnotu
• Vyhledejte přidružení MAX nebo MIN
• Tip: Upravte si své vlastní chybové zprávy #N/A

Jak používat INDEX a MATCH v Excelu

Než použijeme funkce INDEX a MATCH, ujistíme se, že víme, jak nám mohou INDEX a MATCH pomoci nejprve vyhledat hodnoty.

Jak používat funkci INDEX v Excelu

Jedno INDEX funkce v Excelu vrátí hodnotu na daném místě v určitém rozsahu. Syntaxe funkce INDEX je následující:

• řada (povinné) odkazuje na rozsah, ze kterého chcete vrátit hodnotu.
• řádek_číslo (povinné, pokud číslo_sloupce je přítomen) odkazuje na číslo řádku pole.
• číslo_sloupce (volitelné, ale povinné, pokud řádek_číslo je vynechán) odkazuje na číslo sloupce pole.

Například vědět skóre Jeffase 6studenta na seznamu, můžete použít funkci INDEX takto:

=INDEX(C2:C11,6)

√ Poznámka: Rozsah C2: C11 je tam, kde je uvedeno skóre, zatímco číslo 6 zjistí skóre zkoušky 6student.

Tady si uděláme malý test. Pro formuli =INDEX(A1:C1,2;XNUMX), jakou hodnotu vrátí? --- Ano, vrátí se Datum narozeníse 2nd hodnota v daném řádku.

Nyní bychom měli vědět, že funkce INDEX dokáže perfektně pracovat s horizontálními nebo vertikálními rozsahy. Ale co když potřebujeme, aby vrátil hodnotu ve větším rozsahu s několika řádky a sloupci? V tomto případě bychom měli použít jak číslo řádku, tak číslo sloupce. Například zjistit Jeffovo skóre v rozsahu tabulky místo jednoho sloupce můžeme najít jeho skóre pomocí a řada číslo 6 a sloupec číslo 3 v buňky přes A2 až C11 takhle:

=INDEX(A2:C11,6,3)

Pro opravdu velkou databázi s více řádky a sloupci nám však určitě nevyhovuje aplikovat vzorec s přesným číslem řádku a sloupce. A právě tehdy bychom měli kombinovat použití funkce MATCH.

Jak používat funkci MATCH v Excelu

Funkce MATCH v Excelu vrací číselnou hodnotu, umístění konkrétní položky v daném rozsahu. Syntaxe funkce MATCH je následující:

• lookup_value (povinné) odkazuje na hodnotu, která se má shodovat v vyhledávací_pole.
• vyhledávací_pole (povinné) označuje rozsah buněk, ve kterých chcete hledat MATCH.
• typ_shody (volitelný): 1, 0 or -1.
  • 1 (výchozí), MATCH najde největší hodnotu, která je menší nebo rovna lookup_value. Hodnoty v vyhledávací_pole musí být řazeny vzestupně.
  • 0, MATCH najde první hodnotu, která se přesně rovná lookup_value. Hodnoty v vyhledávací_pole může být v libovolném pořadí. (V případech, kdy je typ shody nastaven na 0, můžete použít zástupné znaky.)
  • -1, MATCH najde nejmenší hodnotu, která je větší nebo rovna lookup_value. Hodnoty v vyhledávací_pole musí být uspořádány sestupně.

Například vědět pozici Věry v seznamu jmen, můžete použít vzorec MATCH takto:

=MATCH("Vera",A2:A11,0)

√ Poznámka: Výsledek „4“ znamená, že jméno „Vera“ je na 4. pozici seznamu.

Nyní, když víme o základním použití funkcí INDEX a MATCH v Excelu, vyhrňme si rukávy a připravme se na kombinaci obou funkcí.

Jak kombinovat INDEX a MATCH v Excelu

Podívejte se prosím na níže uvedený příklad, abyste zjistili, jak můžeme kombinovat funkce INDEX a MATCH:

Najít Evelynino skóre, s vědomím, že výsledky zkoušek jsou v 3rd sloupec, můžeme použijte funkci MATCH k automatickému určení pozice řádku aniž byste to museli ručně počítat. Následně můžeme použít funkci INDEX k načtení hodnotu na průsečíku identifikovaného řádku a 3. sloupce:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),3)

Protože vzorec může vypadat trochu složitě, pojďme si projít každou jeho část.

Jedno INDEX vzorec obsahuje tři argumenty:

• řádek_číslo: MATCH("Evelyn";A2:A11,0;XNUMX) poskytuje INDEX pozici řádku hodnoty "Evelyn" v dosahu A2: A11, který je 5.
• číslo_sloupce: 3 specifikuje 3rd sloupec pro INDEX k vyhledání skóre v poli.
• řada: A2: C11 pokyn INDEX, aby vrátil odpovídající hodnotu v průsečíku zadaného řádku a sloupce v rozsahu od A2 až C11. Nakonec se dočkáme výsledku 90.

Ve výše uvedeném vzorci jsme použili pevně zakódovanou hodnotu, "Evelyn". V praxi jsou však pevně zakódované hodnoty nepraktické, protože by vyžadovaly úpravu pokaždé, když hledáme jiná data, jako je skóre jiného studenta. V takových scénářích můžeme použít odkazy na buňky k vytvoření dynamických vzorců. Například v tomto případě budu změňte "Evelyn" na F2:

=INDEX(A2:C11,MATCH(F2,A2:A11,0),3)

Příklady vzorců INDEX a MATCH

V této části budeme hovořit o různých okolnostech použití funkcí INDEX a MATCH ke splnění různých potřeb.

INDEX a MATCH pro použití obousměrného vyhledávání

V předchozím příkladu jsme znali číslo sloupce a použili jsme vzorec MATCH k nalezení čísla řádku. Co když si ale nejsme jisti ani číslem sloupce?

V takových případech můžeme provést obousměrné vyhledávání, známé také jako maticové vyhledávání, pomocí dvou funkcí MATCH: jedna k nalezení čísla řádku a druhá k určení čísla sloupce. Například vědět Evelynino skóre, měli bychom použít vzorec:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),MATCH("Score",A1:C1,0))

INDEX a MATCH použijete levé vyhledávání

Nyní se podívejme na scénář, kde potřebujete určit Evelyninu třídu. Možná jste si všimli, že sloupec třídy je umístěn nalevo od sloupce s názvem, což je situace, která přesahuje možnosti jiné výkonné vyhledávací funkce aplikace Excel, VLOOKUP.

Ve skutečnosti je schopnost provádět vyhledávání na levé straně jedním z aspektů, kde kombinace INDEX a MATCH převyšuje VLOOKUP.

Najít Evelynina třída, použijte k tomu následující vzorec hledejte Evelyn v B2:B11 si získat odpovídající hodnotu z A2:A11.

=INDEX(A2:A11,MATCH("Evelyn",B2:B11,0))

INDEX a MATCH pro použití vyhledávání s rozlišením velkých a malých písmen

Funkce MATCH ze své podstaty nerozlišuje velká a malá písmena. Pokud však požadujete, aby váš vzorec rozlišoval mezi velkými a malými písmeny, můžete jej vylepšit začleněním PŘESNÝ funkce. Zkombinováním funkce MATCH s EXACT ve vzorci INDEX pak můžete efektivně provádět vyhledávání rozlišující malá a velká písmena, jak je znázorněno níže:

• řada odkazuje na rozsah, ze kterého chcete vrátit hodnotu.
• lookup_value odkazuje na hodnotu, která se má shodovat, s ohledem na případ znaků, v vyhledávací_pole.
• vyhledávací_pole odkazuje na rozsah buněk, se kterými chcete MATCH porovnat lookup_value.

Například vědět JIMMYho výsledek zkoušky, použijte následující vzorec:

=INDEX(C2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0))

√ Poznámka: Toto je maticový vzorec, který vyžaduje zadání pomocí Ctrl + směna + vstoupit, kromě Excelu 365, Excelu 2021 a novějších verzí.

INDEX a MATCH najít nejbližší shodu

V Excelu se můžete setkat se situacemi, kdy potřebujete najít nejbližší nebo nejbližší shodu s konkrétní hodnotou v rámci datové sady. V takových scénářích může být použití kombinace funkcí INDEX a MATCH spolu s funkcemi ABS a MIN neuvěřitelně užitečné.

• řada odkazuje na rozsah, ze kterého chcete vrátit hodnotu.
• vyhledávací_pole odkazuje na rozsah hodnot, ke kterému chcete najít nejbližší shodu lookup_value.
• lookup_value odkazuje na hodnotu, která má najít nejbližší shodu.

Například zjistit jehož skóre se nejvíce blíží 85, použijte k tomu následující vzorec vyhledejte nejbližší skóre k 85 v C2:C11 si získat odpovídající hodnotu z A2:A11.

=INDEX(A2:A11,MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0))

√ Poznámka: Toto je maticový vzorec, který vyžaduje zadání pomocí Ctrl + směna + vstoupit, kromě Excelu 365, Excelu 2021 a novějších verzí.

INDEX a MATCH pro použití vyhledávání s více kritérii

Chcete-li najít hodnotu, která splňuje více podmínek, které vyžadují hledání ve dvou nebo více sloupcích, použijte následující vzorec. Vzorec vám umožňuje provádět vyhledávání podle více kritérií zadáním různých podmínek v různých sloupcích, což vám pomůže najít požadovanou hodnotu, která splňuje všechna zadaná kritéria.

√ Poznámka: Toto je maticový vzorec, který vyžaduje zadání pomocí Ctrl + směna + vstoupit. V řádku vzorců se pak zobrazí dvojice složených závorek.

• řada odkazuje na rozsah, ze kterého chcete vrátit hodnotu.
• (lookup_value=lookup_array) představuje jedinou podmínku. Tato podmínka kontroluje, zda konkrétní lookup_value odpovídá hodnotám v vyhledávací_pole.

Chcete-li například najít skóre třídy A's Coco, jehož datum narození je 7, můžete použít následující vzorec:

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0))

INDEX a MATCH pro použití vyhledávání ve více sloupcích

Představte si situaci, kdy máte co do činění s více datovými sloupci. První sloupec funguje jako klíč pro klasifikaci dat v ostatních sloupcích. Chcete-li určit kategorii nebo klasifikaci pro konkrétní položku, budete muset provést vyhledávání napříč datovými sloupci a přiřadit je k příslušnému klíči v referenčním sloupci.

Například v tabulce níže, jak můžeme přiřadit studenta Shawna k jeho odpovídající třídě pomocí INDEX a MATCH? Dobře, můžete toho dosáhnout pomocí vzorce, ale vzorec je poměrně rozsáhlý a může být náročné na pochopení, natož na zapamatování a psaní.

=IFERROR(INDEX($A$2:$A$4,MATCH(IF(SUM(MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0)))>0,1,-1),MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0))^0,0)), "")

To je tam kde Kutools pro Excel's Index a shoda ve více sloupcích funkce přijde vhod. Zjednodušuje proces a umožňuje rychlé a snadné přiřazování konkrétních položek k odpovídajícím kategoriím. Chcete-li odemknout tento mocný nástroj a bez námahy sladit Shawna s jeho třídou, jednoduše stáhněte a nainstalujte doplněk Kutools pro Excela poté postupujte následovně:

1. Vyberte cílovou buňku, kde chcete zobrazit odpovídající třídu.
2. Na Kutools klepněte na kartu Pomocník vzorců > Vyhledávání a reference > Index a shoda ve více sloupcích.
3. 
3. Ve vyskakovacím dialogovém okně proveďte následující:
   1. Klikněte na 1 vedle tlačítka Look__col vyberte sloupec obsahující klíčové informace, které chcete vrátit, tj. názvy tříd. (Zde můžete vybrat pouze jeden sloupec.)
   2. Klikněte na 2 vedle tlačítka Table_rng vyberte buňky tak, aby odpovídaly hodnotám ve vybraném Look__col, tj. jména studentů.
   3. Klikněte na 3 vedle tlačítka Vyhledávací_hodnota vyberte buňku obsahující jméno studenta, které chcete porovnat s jeho třídou, v tomto případě Shawn.
   4. klikněte OK.
   5. 

Výsledek

Kutools automaticky vygeneroval vzorec a v cílové buňce okamžitě uvidíte název třídy Shawna.

INDEX a MATCH pro vyhledání první neprázdné hodnoty

Chcete-li načíst první neprázdnou hodnotu, ignorující chyby, ze sloupce nebo řádku, můžete použít vzorec založený na funkcích INDEX a MATCH. Pokud však nechcete ignorovat chyby ze svého rozsahu, přidejte funkci ISBLANK.

• Získejte první neprázdnou hodnotu ve sloupci nebo řádku s ignorováním chyb:

• =INDEX(B4:B15,MATCH(TRUE,INDEX((B4:B15<>0),0),0))

• Získejte první neprázdnou hodnotu ve sloupci nebo řádku včetně chyb:

• =INDEX(B4:B15,MATCH(FALSE,ISBLANK(B4:B15),0))

  

INDEX a MATCH pro vyhledání první číselné hodnoty

Chcete-li získat první číselnou hodnotu ze sloupce nebo řádku, použijte vzorec založený na funkcích INDEX, MATCH a ISNUMBER.

=INDEX(B4:B15,MATCH(TRUE,ISNUMBER(B4:B15),0))

INDEX a MATCH pro vyhledání MAX nebo MIN asociací

Pokud potřebujete získat hodnotu spojenou s maximální nebo minimální hodnotou v rozsahu, můžete použít funkci MAX nebo MIN spolu s funkcemi INDEX a MATCH.

• INDEX a MATCH pro načtení hodnoty spojené s maximální hodnotou:
• =INDEX(array, MATCH(MAX(lookup_array), lookup_array, 0))
• INDEX a MATCH pro získání hodnoty spojené s minimální hodnotou:
• =INDEX(array, MATCH(MIN(lookup_array), lookup_array, 0))
• Ve výše uvedených vzorcích jsou dva argumenty:
• • řada odkazuje na rozsah, ze kterého chcete vrátit související informace.
  • vyhledávací_pole představuje soubor hodnot, které mají být zkoumány nebo hledány pro konkrétní kritéria, tj. maximální nebo minimální hodnoty.

Například, pokud chcete určit kdo má nejvyšší skóre, použijte následující vzorec:

=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11,0))

Tip: Upravte si své vlastní chybové zprávy #N/A

Při práci s funkcemi INDEX a MATCH aplikace Excel se můžete setkat s chybou #N/A, když neexistuje žádný odpovídající výsledek. Například v tabulce níže se při pokusu o nalezení skóre studentky jménem Samantha objeví chyba #N/A, protože není přítomna v datové sadě.

Chcete-li, aby byly vaše tabulky uživatelsky přívětivější, můžete si tuto chybovou zprávu přizpůsobit tak, že svůj vzorec INDEX MATCH zabalíte do funkce IFNA:

=IFNA(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

Nahoře je veškerý relevantní obsah související s funkcemi INDEX a MATCH v Excelu. Doufám, že vám bude návod užitečný. Pokud chcete prozkoumat další tipy a triky pro Excel, prosím klikněte zde pro přístup k naší rozsáhlé sbírce více než tisíců výukových programů.