Jak používat funkce INDEX a MATCH v Excelu

Tento článek vysvětluje, jak používat funkce INDEX a MATCH společně ve všech verzích Excelu, včetně Excelu 2019 a Microsoft 365.

Co jsou funkce INDEX a MATCH?

INDEX a MATCH jsou vyhledávání v Excelu funkcí. I když se jedná o dvě zcela samostatné funkce, které lze používat samostatně, lze je také kombinovat a vytvářet pokročilé vzorce.

Funkce INDEX vrací hodnotu nebo odkaz na hodnotu z konkrétního výběru. Může být například použit k nalezení hodnoty ve druhém řádku souboru dat nebo v pátém řádku a třetím sloupci.

Zatímco INDEX lze velmi dobře použít samostatně, vnoření MATCH do vzorce jej dělá o něco užitečnějším. Funkce MATCH hledá zadanou položku v rozsahu buněk a poté vrátí relativní pozici položky v rozsahu. Může být například použit k určení, že konkrétní jméno je třetí položkou v seznamu jmen.

INDEX a MATCH Syntaxe a argumenty

Takto je třeba zapsat obě funkce, aby jim Excel porozuměl:

=INDEX(pole, číslo_řádku, [číslo_sloupce])

• pole je rozsah buněk, které bude vzorec používat. Může to být jeden nebo více řádků a sloupců, například A1:D5. Je to povinné.
• číslo_řádku je řádek v poli, ze kterého se má vrátit hodnota, například 2 nebo 18. Je to nutné, pokud číslo_sloupce je přítomen.
• číslo_sloupce je sloupec v poli, ze kterého se má vrátit hodnota, například 1 nebo 9. Je to volitelné.

= ZÁPAS(vyhledávací_hodnota, vyhledávací_pole, [match_type])

• vyhledávací_hodnota je hodnota, se kterou chcete odpovídat vyhledávací_pole. Může to být číslo, text nebo logická hodnota, která se zadává ručně nebo se na ni odkazuje odkazem na buňku. Toto je vyžadováno.
• vyhledávací_pole je rozsah buněk, kterými se má prohlížet. Může to být jeden řádek nebo jeden sloupec, například A2:D2 nebo G1:G45. Toto je vyžadováno.
• match_type může být -1, 0, nebo 1. Upřesňuje jak vyhledávací_hodnota se shoduje s hodnotami v vyhledávací_pole (viz. níže). 1 je výchozí hodnota, pokud je toto argument je vynechán.

Jaký typ shody použít
Typ shody	Co to dělá	Pravidlo	Příklad
1	Najde největší hodnotu, která je menší nebo rovna vyhledávací_hodnota.	The vyhledávací_pole hodnoty musí být umístěny ve vzestupném pořadí (např. -2, -1, 0, 1, 2; nebo A-Z; nebo NEPRAVDA, PRAVDA.	vyhledávací_hodnota je 25, ale chybí vyhledávací_pole, takže místo toho je vrácena pozice dalšího nejmenšího čísla, například 22.
0	Najde první hodnotu, která se přesně rovná vyhledávací_hodnota.	The vyhledávací_pole hodnoty mohou být v libovolném pořadí.	vyhledávací_hodnota je 25, takže vrátí pozici 25.
-1	Najde nejmenší hodnotu, která je větší nebo rovna vyhledávací_hodnota.	The vyhledávací_pole hodnoty musí být umístěny v sestupném pořadí (např. 2, 1, 0, -1, -2).	vyhledávací_hodnota je 25, ale chybí vyhledávací_pole, takže místo toho je vrácena pozice dalšího největšího čísla, například 34.

Příklad vzorců INDEX a MATCH

Než se podíváme na to, jak zkombinovat INDEX a MATCH do jednoho vzorce, musíme pochopit, jak tyto funkce fungují samy o sobě.

INDEX Příklady

=INDEX(A1:B2;2;2)
=INDEX(A1:B1;1)
=INDEX(2:2;1)
=INDEX(B1:B2;1)

V tomto prvním příkladu existují čtyři vzorce INDEX, které můžeme použít k získání různých hodnot:

• =INDEX(A1:B2;2;2) prohledá A1:B2 a najde hodnotu ve druhém sloupci a druhém řádku, což je Stacy.
• =INDEX(A1:B1;1) prohledá A1:B1 a najde hodnotu v prvním sloupci, což je Jon.
• =INDEX(2:2;1) prohledá vše ve druhém řádku, aby našel hodnotu v prvním sloupci, což je Tim.
• =INDEX(B1:B2;1) prohledá B1:B2 a najde hodnotu v prvním řádku, což je Amy.

Příklady MATCH

=MATCH("Stacy",A2:D2;0)
=MATCH(14;D1:D2)
=MATCH(14;D1:D2;-1)
=MATCH(13;A1:D1;0)

Zde jsou čtyři snadné příklady funkce MATCH:

• =MATCH("Stacy",A2:D2;0) hledá Stacy v dosahu A2:D2 a vrací se 3 jako výsledek.
• =MATCH(14;D1:D2) hledá 14 v dosahu D1:D2, ale protože se v tabulce nenachází, MATCH najde další největší hodnotu, která je menší nebo rovna 14, což je v tomto případě 13, který je na pozici 1 z vyhledávací_pole.
• =MATCH(14;D1:D2;-1) je totožné se vzorcem nad ním, ale protože pole není v sestupném pořadí jako -1 vyžaduje, dostaneme chybu.
• =MATCH(13;A1:D1;0) hledá 13 v prvním řádku listu, který se vrátí 4 protože je to čtvrtá položka v tomto poli.

Příklady INDEX-MATCH

Zde jsou dva příklady, kde můžeme kombinovat INDEX a MATCH v jednom vzorci:

Najděte odkaz na buňku v tabulce

=INDEX(B2:B5,SHODA(F1,A2:A5))

Tento příklad je vnoření vzorce MATCH do vzorce INDEX. Cílem je identifikovat barvu položky pomocí čísla položky.

Když se podíváte na obrázek, můžete v řádcích "Oddělené" vidět, jak by se vzorce zapsaly samy o sobě, ale protože je vnořujeme, děje se toto:

• MATCH(F1;A2:A5) hledá F1 hodnotu (8795) v sadě dat A2:A5. Pokud sloupec odpočítáme, uvidíme, že je 2, takže na to právě přišla funkce MATCH.
• Pole INDEX je B2:B5 protože nakonec hledáme hodnotu v tomto sloupci.
• Funkci INDEX lze nyní takto přepsat 2 je to, co MATCH našel: INDEX(B2:B5; 2; [číslo_sloupce]).
• Od té doby číslo_sloupce je volitelné, můžeme jej odstranit a zbyde toto: INDEX(B2:B5;2).
• Takže teď je to jako normální vzorec INDEX, kde zjišťujeme hodnotu druhé položky B2:B5, který je Červené.

Vyhledávání podle záhlaví řádků a sloupců

=INDEX(B2:E13,SHODA(G1,A2:A13;0),SHODA(G2,B1:E1,0))

V tomto příkladu MATCH a INDEX provádíme obousměrné vyhledávání. Cílem je zjistit, kolik peněz jsme vydělali Zelená položky v Smět. Toto je opravdu podobné výše uvedenému příkladu, ale v INDEXU je vnořen zvláštní vzorec MATCH.

• MATCH(G1;A2:A13;0) je první položka řešená v tomto vzorci. Hledá se G1 (slovo "květen") v A2:A13 získat konkrétní hodnotu. Nevidíme to tady, ale je to tak 5.
• MATCH(G2;B1:E1;0) je druhý vzorec MATCH a je opravdu podobný prvnímu, ale místo toho hledá G2 (slovo "Zelená") v záhlaví sloupců na B1:E1. Tento řeší 3.
• Nyní můžeme přepsat vzorec INDEX takto, abychom si vizualizovali, co se děje: =INDEX(B2:E13;5;3). Tohle vypadá v celé tabulce, B2:E13, pro pátý řádek a třetí sloupec, který se vrátí $180.

Pravidla MATCH a INDEX

Při psaní vzorců s těmito funkcemi je třeba mít na paměti několik věcí:

• MATCH není citlivý na velká písmena, takže velká a malá písmena jsou při porovnávání textových hodnot zpracována stejně.
• MATCH se vrací #N/A z více důvodů: pokud match_type je 0 a vyhledávací_hodnota není nalezen, pokud match_type je -1 a vyhledávací_pole není v sestupném pořadí, pokud match_type je 1 a vyhledávací_pole není ve vzestupném pořadí, a pokud vyhledávací_pole není jeden řádek nebo sloupec.
• Můžete použít zástupný znak v vyhledávací_hodnota argument jestli match_type je 0 a vyhledávací_hodnota je textový řetězec. Otazník odpovídá libovolnému jednotlivému znaku a hvězdička libovolné sekvenci znaků (např. =MATCH("Jo*";1:1,0)). Chcete-li použít MATCH k nalezení skutečného otazníku nebo hvězdičky, zadejte nejprve ~.
• INDEX se vrací #REF! -li číslo_řádku a číslo_sloupce neukazujte na buňku v poli.

Související funkce Excelu

Funkce MATCH je podobná funkci LOOKUP, ale MATCH vrací pozice položky místo položky samotné.

VLOOKUP je další vyhledávací funkce, kterou můžete použít v Excelu, ale na rozdíl od MATCH, která vyžaduje INDEX pro pokročilé vyhledávání, vzorce VLOOKUP potřebují pouze tuto jednu funkci.