Jak používat funkci INDEX a MATCH v aplikaci Excel

index match combined f335f7c14de94f27bc0e5c37af3971e0

Co vědět

Funkci INDEX lze použít samostatně, ale vnořením funkce MATCH uvnitř vytvoří pokročilé vyhledávání.
Tato vnořená funkce je flexibilnější než VLOOKUP a může přinášet výsledky rychleji.

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

Jaké jsou funkce INDEX a MATCH?

INDEX a MATCH jsou vyhledávací funkce aplikace Excel. Přestože se jedná o dvě zcela oddělené funkce, které lze použít samostatně, lze je také kombinovat a vytvářet pokročilé vzorce. Funkce INDEX vrací hodnotu nebo odkaz na hodnotu z určitého výběru. Lze jej například použít k nalezení hodnoty ve druhém řádku datové sady nebo v pátém řádku a třetím sloupci. Zatímco INDEX lze velmi dobře použít samostatně, vnoření MATCH ve vzorci je o něco užitečnější. Funkce MATCH vyhledá zadanou položku v rozsahu buněk a poté vrátí relativní polohu položky v rozsahu. Dalo by se například použít k určení, že konkrétní název je třetí položkou v seznamu jmen.

Syntaxe a argumenty INDEX a MATCH

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

= INDEX(pole, řádek_číslo, [column_num])

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 nutné.
řádek_číslo je řádek v poli, ze kterého se má vrátit hodnota, například 2 nebo 18. Je vyžadováno, pokud column_num je přítomen.
column_num je sloupec v poli, ze kterého se má vrátit hodnota, například 1 nebo 9. Je to volitelné.

= ZÁPAS(lookup_value, lookup_array, [match_type])

lookup_value je hodnota, ve které se chcete shodovat lookup_array. Může to být číslo, text nebo logická hodnota, která je zadána ručně nebo na kterou se odkazuje prostřednictvím odkazu na buňku. To je nutné.
lookup_array je rozsah buněk, které je třeba prohlédnout. Může to být jeden řádek nebo jeden sloupec, například A2: D2 nebo G1: G45. To je nutné.
typ_ shody může být -1, 0, nebo 1. Specifikuje jak lookup_value odpovídá hodnotám v lookup_array (viz. níže). 1 je výchozí hodnota, pokud je tento argument vynechán.

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

Použití 1 nebo -1 pro časy, kdy potřebujete provést přibližné vyhledávání na stupnici, například když pracujete s čísly a když jsou aproximace v pořádku. Pamatujte však, že pokud neurčíte typ_ shody, 1 bude výchozí, což může zkreslit výsledky, pokud opravdu chcete přesnou shodu.

Příklad vzorců INDEX a MATCH

Než se podíváme na to, jak kombinovat 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, aby našel hodnotu ve druhém sloupci a druhém řádku, což je Stacy.
= INDEX (A1: B1,1) prohledá A1: B1, aby našel hodnotu v prvním sloupci, což je Jon.
= INDEX (2: 2,1) prohledá vše ve druhém řádku a vyhledá hodnotu v prvním sloupci, což je Tim.
= INDEX (B1: B2,1) prohledá B1: B2 a vyhledá hodnotu v prvním řádku, což je Amy.

PŘÍPADY ZÁPASU

= 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 to není v tabulce nalezeno, MATCH najde další největší hodnota, která je menší nebo rovna 14, což v tomto případě je 13, který je na svém místě 1 z lookup_array.
= MATCH (14, D1: D2, -1) je identický 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.

INDEX-MATCH Příklady

Zde jsou dva příklady, kde můžeme kombinovat INDEX a MATCH do jednoho vzorce:

Odkaz na buňku najdete v tabulce

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

Tento příklad vnořuje vzorec MATCH do vzorce INDEX. Cílem je identifikovat barvu položky pomocí čísla položky. Pokud se podíváte na obrázek, v řádcích „Oddělené“ můžete vidět, jak by se vzorce psaly samy, ale protože je vnořujeme, děje se toto:

MATCH (F1, A2: A5) hledá F1 hodnotu (8795) v datové sadě A2: A5. Pokud sloupec odpočítáme, vidíme, že je 2, tak 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í přepsat takto od 2 je to, co našel MATCH: INDEX (B2: B5, 2, [column_num]).
Od té doby column_num je volitelný, můžeme jej odebrat a ponechat toto: INDEX (B2: B5,2).
Takže teď je to jako normální vzorec INDEX, kde najdeme hodnotu druhé položky v B2: B5, který je Červené.

Vyhledejte podle záhlaví řádků a sloupců

= INDEX (B2: E13, MATCH (G1, A2: A13,0), MATCH (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. To je opravdu podobné výše uvedenému příkladu, ale v INDEXu je vnořen další vzorec MATCH.

MATCH (G1, A2: A13,0) je první položkou vyřešenou v tomto vzorci. Hledá se G1 (slovo „květen“) v A2: A13 získat konkrétní hodnotu. Tady to nevidíme, 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. Tohle se vyřeší 3.
Nyní můžeme přepsat vzorec INDEX takto a vizualizovat, co se děje: = INDEX (B2: E13,5,3). To vypadá v celé tabulce, B2: E13, pro pátý řádek a třetí sloupec, který se vrátí 180 dolarů.

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 nerozlišuje velká a malá písmena, takže velká a malá písmena jsou při shodě s textovými hodnotami považována za stejná.
MATCH se vrací #N/A z více důvodů: pokud typ_ shody je 0 a lookup_value není nalezen, pokud typ_ shody je -1 a lookup_array není v sestupném pořadí, pokud typ_ shody je 1 a lookup_array není ve vzestupném pořadí, a pokud lookup_array není jeden řádek nebo sloupec.
V souboru můžete použít zástupný znak lookup_value argument kdyby typ_ shody je 0 a lookup_value je textový řetězec. Otazník odpovídá libovolnému jednotlivému znaku a hvězdička odpovídá libovolnému sledu znaků (např. = MATCH („Jo*“, 1: 1,0)). Chcete -li použít MATCH k vyhledání skutečného otazníku nebo hvězdičky, zadejte ~ jako první.
INDEX vrací #REF! -li řádek_číslo a column_num neukazujte na buňku v poli.

Související funkce Excelu

Funkce MATCH je podobná funkci LOOKUP, ale funkce MATCH vrací pozice položky místo samotné položky. VLOOKUP je další vyhledávací funkce, kterou můžete použít v Excelu, ale na rozdíl od MATCH, který pro pokročilé vyhledávání vyžaduje INDEX, vzorce VLOOKUP potřebují pouze tuto jednu funkci. Více od Lifewire

Jak vytvořit vzorec pro vyhledávání v Excelu s více kritérii
Jak používat funkci LOOKUP v Excelu
INDEX-MATCH vs. VLOOKUP v aplikaci Excel
Jak používat funkci Excel INDEX
Jak použít funkci MATCH aplikace Excel k nalezení hodnoty
Jak používat funkci LOOKUP aplikace Excel k vyhledání informací
Jak používat funkci IF v aplikaci Excel
Jak používat funkci XLOOKUP v aplikaci Excel

Jak zkombinovat funkce ROUND a SUM v Excelu
Jak vytvořit vzorec pro vyhledávání vlevo v aplikaci Excel pomocí VLOOKUP
Jak používat ARRAYFORMULA v Tabulkách Google
Jak vytvořit databázi v aplikaci Excel
Rychle vyhledejte více polí dat pomocí funkce VLOOKUP aplikace Excel
Jak používat absolutní odkaz na buňku v aplikaci Excel
Najděte v aplikaci Excel největší záporné nebo kladné číslo
Jak zkombinovat dva sloupce v aplikaci Excel

Vaše práva na ochranu osobních údajů

Společnost Lifewire a naši partneři třetích stran používají soubory cookie a zpracovávají osobní údaje, jako jsou jedinečné identifikátory, na základě vašeho souhlasu s ukládáním a/nebo přístupem k informacím na zařízení, zobrazováním personalizovaných reklam a pro měření obsahu, přehled publika a vývoj produktů. Chcete -li změnit nebo odvolat své možnosti souhlasu pro Lifewire.com, včetně vašeho práva vznést námitku v případě použití oprávněného zájmu, klikněte níže. Svá nastavení můžete kdykoli aktualizovat pomocí odkazu „Ochrana osobních údajů EU“ v dolní části jakékoli stránky. Tyto možnosti budou globálně signalizovány našim partnerům a nebudou mít vliv na údaje o prohlížení. Seznam partnerů (prodejců)

My a naši partneři zpracováváme údaje za účelem:

Aktivně skenujte charakteristiky zařízení pro identifikaci. Použijte přesná data o geolokaci. Ukládejte a/nebo přistupujte k informacím na zařízení. Vyberte personalizovaný obsah. Vytvořte si přizpůsobený profil obsahu. Změřte výkon reklam. Vyberte základní reklamy. Vytvořte si přizpůsobený reklamní profil. Vyberte personalizované reklamy. Aplikujte průzkum trhu a generujte poznatky o publiku. Měření výkonu obsahu. Vyvíjet a zlepšovat produkty. Seznam partnerů (prodejců)