Kombinace funkcí INDEX a MATCH v Excelu (10 snadných příkladů)

Excel má spoustu funkcí - asi 450+ z nich.

A mnohé z nich jsou prostě úžasné. Množství práce, kterou můžete zvládnout pomocí několika vzorců, mě stále překvapuje (i po použití Excelu 10 a více let).

A mezi všemi těmito úžasnými funkcemi vyniká kombinace funkcí INDEX MATCH.

Jsem velkým fanouškem kombinace INDEX MATCH a mnohokrát jsem to dal docela jasně najevo.

Dokonce jsem napsal článek o Index Match Vs VLOOKUP, který vyvolal trochu diskuse (v sekci komentářů si můžete prohlédnout ohňostroj).

A dnes píšu tento článek výhradně zaměřený na Index Match, abych vám ukázal několik jednoduchých a pokročilých scénářů, kde můžete použít toto výkonné kombinované vzorce a práci dokončit.

Poznámka: V Excelu existují další vzorce vyhledávání - například VLOOKUP a HLOOKUP a ty jsou skvělé. Mnoho lidí považuje VLOOKUP za jednodušší (a to je také pravda). Věřím, že INDEX MATCH je v mnoha případech lepší volbou. Ale protože to lidem připadá obtížné, používá se méně. Snažím se to tedy zjednodušit pomocí tohoto tutoriálu.

Než vám tedy ukážu, jak kombinace INDEX MATCH mění svět analytiků a datových vědců, dovolte mi, abych vám nejprve představil jednotlivé části - funkce INDEX a MATCH.

Funkce INDEX: Najde hodnotu na základě souřadnic

Nejjednodušší způsob, jak pochopit, jak funkce indexu funguje, je chápat ji jako satelit GPS.

Jakmile satelitu řeknete souřadnice zeměpisné šířky a délky, bude přesně vědět, kam jít a najít toto místo.

Přestože navzdory ohromujícímu počtu lat-long kombinací bude satelit přesně vědět, kde hledat.

Rychle jsem hledal své pracovní místo a toto jsem dostal.

Každopádně dost geografie.

Stejně jako satelit potřebuje souřadnice zeměpisné šířky a délky, funkce INDEX v aplikaci Excel bude potřebovat číslo řádku a sloupce, aby věděla, o kterou buňku jde.

A to je funkce Excel INDEX v kostce.

Dovolte mi, abych to pro vás definoval jednoduchými slovy.

Funkce INDEX použije číslo řádku a číslo sloupce k vyhledání buňky v daném rozsahu a vrácení hodnoty v ní.

INDEX je sám o sobě velmi jednoduchou funkcí bez nástroje. Koneckonců ve většině případů pravděpodobně neznáte čísla řádků a sloupců.

Ale…

Skutečnost, že jej můžete použít s dalšími funkcemi (nápověda: MATCH), které dokážou najít číslo řádku a číslo sloupce, činí z INDEXu extrémně výkonnou funkci Excelu.

Níže je syntaxe funkce INDEX:

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

• pole - A rozsah buněk nebo konstanta pole.
• row_num - číslo řádku, ze kterého má být načtena hodnota.
• [col_num] - číslo sloupce, ze kterého má být načtena hodnota. Ačkoli se jedná o volitelný argument, ale pokud není zadán row_num, je třeba jej zadat.
• [area_num] - (Volitelné) Pokud je argument pole tvořen více rozsahy, bude toto číslo použito k výběru odkazu ze všech rozsahů.

Funkce INDEX má 2 syntaxe (pouze FYI).

První se používá ve většině případů. Druhý se používá pouze v pokročilých případech (například při třístranném vyhledávání), kterým se budeme věnovat v jednom z příkladů dále v tomto kurzu.

Pokud jste v této funkci nováčkem, pamatujte si první syntaxi.

Níže je video, které vysvětluje, jak používat funkci INDEX

Funkce MATCH: Vyhledá pozici baed na vyhledávací hodnotě

Když se vrátím k mému předchozímu příkladu zeměpisné délky a šířky, MATCH je funkce, která dokáže najít tyto pozice (ve světě tabulek Excel).

V jednoduchém jazyce může funkce Excel MATCH najít polohu buňky v rozsahu.

A na základě čeho by zjistila polohu buňky?

Na základě vyhledávací hodnoty.

Pokud máte například níže uvedený seznam a chcete v něm najít pozici jména „Mark“, můžete použít funkci MATCH.

Funkce vrací 3, protože to je pozice buňky s názvem Mark v ní.

Funkce MATCH začne shora dolů vyhledávat hodnotu (což je „Mark“) v zadaném rozsahu (což je v tomto případě A1: A9). Jakmile najde jméno, vrátí pozici v tomto konkrétním rozsahu.

Níže je syntaxe funkce MATCH v aplikaci Excel.

= MATCH (lookup_value, lookup_array, [match_type])

• lookup_value - Hodnota, pro kterou hledáte shodu v lookup_array.
• lookup_array - Rozsah buněk, ve kterých hledáte hodnotu lookup_value.
• [typ_ shody] - (Volitelné) Toto určuje, jak by měl Excel hledat odpovídající hodnotu. Může mít tři hodnoty -1, 0 nebo 1.

Porozumění argumentu typu shody ve funkci MATCH

O funkci MATCH potřebujete vědět ještě jednu věc, a to o tom, jak prochází daty a zjišťuje polohu buňky.

Třetí argument funkce MATCH může být 0, 1 nebo -1.

Níže je vysvětleno, jak tyto argumenty fungují:

• 0 - vyhledá přesnou shodu hodnoty. Pokud je nalezena přesná shoda, funkce MATCH vrátí polohu buňky. Jinak vrátí chybu.
• 1 - vyhledá největší hodnotu, která je menší nebo rovna vyhledávací hodnotě. Aby to fungovalo, je třeba rozsah dat řadit vzestupně.
• -1 - vyhledá nejmenší hodnotu, která je větší nebo rovna vyhledávací hodnotě. Aby to fungovalo, je třeba rozsah dat seřadit sestupně.

Níže je video, které vysvětluje, jak používat funkci MATCH (spolu s argumentem typu shody)

Abych to shrnul a vyjádřil jednoduchými slovy:

• INDEX potřebuje pozici buňky (číslo řádku a sloupce) a udává hodnotu buňky.
• MATCH najde pozici pomocí vyhledávací hodnoty.

Pojďme je spojit a vytvořit Powerhouse (INDEX + MATCH)

Nyní, když máte základní znalosti o tom, jak funkce INDEX a MATCH fungují samostatně, pojďme tyto dvě věci spojit a seznámit se se všemi úžasnými věcmi, které dokáže.

Abych to lépe pochopil, mám několik příkladů, které používají kombinaci INDEX MATCH.

Začnu jednoduchým příkladem a poté vám ukážu i několik pokročilých případů použití.

Kliknutím sem stáhnete ukázkový soubor

Příklad 1: Jednoduché vyhledávání pomocí kombinace INDEX MATCH

Pojďme udělat jednoduché vyhledávání pomocí INDEX/MATCH.

Níže je tabulka, kde mám známky pro deset studentů.

Z této tabulky chci najít značky pro Jima.

Níže je vzorec, který to snadno provede:

= INDEX ($ A $ 2: $ B $ 11, MATCH („Jim“, $ A $ 2: $ A $ 11,0), 2)

Pokud si myslíte, že to lze snadno provést pomocí funkce VLOOKUP, máte pravdu! Toto není nejlepší využití úžasnosti INDEX MATCH. Navzdory skutečnosti, že jsem fanouškem INDEX MATCH, je to trochu obtížnější než VLOOKUP. Pokud je načítání dat ze sloupce napravo vše, co chcete udělat, doporučuji použít VLOOKUP.

Důvod, proč jsem ukázal tento příklad, který lze také snadno provést pomocí VLOOKUP, je ukázat vám, jak INDEX MATCH funguje v jednoduchém nastavení.

Nyní mi dovolte ukázat výhodu INDEX MATCH.

Předpokládejme, že máte stejná data, ale místo toho, abyste je měli ve sloupcích, máte je v řádcích (jak je uvedeno níže).

Víte co, stále můžete použít kombinaci INDEX MATCH k získání Jimových známek.

Níže je uveden vzorec, který vám poskytne výsledek:

= INDEX ($ B $ 1: $ K $ 2,2, MATCH („Jim“, $ B $ 1: $ K $ 1,0))

Všimněte si, že musíte změnit rozsah a přepnout části řádků/sloupců, aby tento vzorec fungoval i pro horizontální data.

To nelze provést pomocí VLOOKUP, ale stále to můžete snadno provést pomocí HLOOKUP.

Kombinace INDEX MATCH může snadno zpracovávat horizontální i vertikální data.

Kliknutím sem stáhnete ukázkový soubor

Příklad 2: Vyhledání doleva

Je to běžnější, než si myslíte.

Mnohokrát může být vyžadováno načtení dat ze sloupce, který je nalevo od sloupce, který má hodnotu vyhledávání.

Něco, jak je uvedeno níže:

Chcete -li zjistit Michaelovy tržby, budete muset provést vyhledávání vlevo.

Pokud přemýšlíte o VLOOKUP, dovolte mi, abych se zastavil.

VLOOKUP není určen k vyhledávání a načítání hodnot vlevo.

Stále to můžete dělat pomocí VLOOKUP?

Ano můžeš!

To se ale může změnit na dlouhý a ošklivý vzorec.

Pokud tedy chcete provést vyhledávání a načíst data ze sloupců vlevo, je lepší použít kombinaci INDEX MATCH.

Níže je vzorec, který získá Michaelovo prodejní číslo:

= INDEX ($ A $ 2: $ C $ 11, MATCH („Michael“, C2: C11,0), 2)

Další bod zde pro INDEX MATCH. VLOOKUP může načíst data pouze ze sloupců, které jsou napravo od sloupce, který má vyhledávací hodnotu.

Příklad 3: Obousměrné vyhledávání

Doposud jsme viděli příklady, kdy jsme chtěli načíst data ze sloupce sousedícího se sloupcem, který má hodnotu vyhledávání.

V reálném životě však data často pokrývají více sloupců.

INDEX MATCH snadno zvládne obousměrné vyhledávání.

Níže je uveden soubor dat známek studenta ze tří různých předmětů.

Pokud chcete rychle načíst známky studenta ve všech třech předmětech, můžete to udělat pomocí INDEX MATCH.

Níže uvedený vzorec vám poskytne známky pro Jima pro všechny tři předměty (zkopírujte a vložte do jedné buňky a přetažením vyplňte další buňky nebo zkopírujte a vložte do jiných buněk).

= INDEX ($ B $ 2: $ D $ 11, MATCH ($ F $ 3, $ A $ 2: $ A $ 11,0), MATCH (G $ 2, $ B $ 1: $ D $ 1,0))

Dovolte mi také rychle vysvětlit tento vzorec.

Vzorec INDEX používá jako rozsah B2: D11.

První MATCH používá jméno (Jim v buňce F3) a načte jeho pozici ve sloupci jmen (A2: A11). Tím se stane číslo řádku, ze kterého je třeba načíst data.

Druhý vzorec MATCH používá název subjektu (v buňce G2) k získání pozice konkrétního jména subjektu v B1: D1. Například matematika je 1, fyzika je 2 a chemie je 3.

Protože jsou tyto pozice MATCH vloženy do funkce INDEX, vrací skóre na základě jména studenta a názvu předmětu.

Tento vzorec je dynamický, což znamená, že pokud změníte jméno studenta nebo název předmětu, bude stále fungovat a načíst správná data.

Jedna skvělá věc na používání INDEX/MATCH je, že i když zaměníte jména subjektů, bude vám i nadále poskytovat správný výsledek.

Příklad 4: Vyhledávací hodnota z více sloupců/kritérií

Předpokládejme, že máte datovou sadu, jak je uvedeno níže, a chcete načíst značky pro „Mark Long“.

Protože jsou data ve dvou sloupcích, nemohu Marka vyhledat a data získat.

Pokud to udělám tímto způsobem, získám údaje o značkách pro Mark Frost a ne pro Mark Long (protože funkce MATCH mi poskytne výsledek pro značku MARK, kterou splňuje).

Jedním ze způsobů, jak toho dosáhnout, je vytvořit pomocný sloupec a zkombinovat názvy. Jakmile máte pomocný sloupec, můžete použít VLOOKUP a získat data značek.

Pokud vás zajímá, jak to udělat, přečtěte si tento návod k použití VLOOKUP s více kritérii.

Ale s kombinací INDEX/MATCH nepotřebujete pomocný sloupec. Můžete vytvořit vzorec, který zpracovává více kritérií v samotném vzorci.

Níže uvedený vzorec poskytne výsledek.

= INDEX ($ C $ 2: $ C $ 11, MATCH ($ E $ 3 & "|" & $ F $ 3, $ A $ 2: A11 & "|" & $ B $ 2: $ B $ 11,0))

Dovolte mi rychle vysvětlit, co tento vzorec dělá.

Část MATCH vzorce kombinuje vyhledávací hodnotu (Mark a Long) a také celé vyhledávací pole. Když je jako vyhledávací pole použito $ A $ 2: A11 & ”|” & $ B $ 2: $ B $ 11, ve skutečnosti kontroluje vyhledávací hodnotu oproti kombinovanému řetězci jména a příjmení (oddělený symbolem potrubí).

Tím je zajištěno, že získáte správný výsledek bez použití pomocných sloupců.

Tento druh vyhledávání (kde je více sloupců/kritérií) můžete provést také pomocí VLOOKUP, ale musíte použít pomocný sloupec. Kombinace INDEX MATCH to lehce usnadňuje bez pomocných sloupců.

Příklad 5: Získejte hodnoty z celého řádku/sloupce

Ve výše uvedených příkladech jsme použili funkci INDEX k získání hodnoty z konkrétní buňky. Zadáte číslo řádku a sloupce a vrátí hodnotu v konkrétní buňce.

Ale můžete udělat víc.

Můžete také použít funkci INDEX k získání hodnot z celého řádku nebo sloupce.

A jak to může být užitečné, ptáte se!

Předpokládejme, že chcete znát celkové skóre Jima ve všech třech předmětech.

Pomocí funkce INDEX můžete nejprve získat všechny Jimovy známky a poté pomocí funkce SUMA získat součet.

Podívejme se, jak to udělat.

Níže mám skóre všech studentů ve třech předmětech.

Níže uvedený vzorec mi poskytne celkové skóre Jima ve všech třech předmětech.

= SUM (INDEX ($ B $ 2: $ D $ 11, MATCH ($ F $ 4, $ A $ 2: $ A $ 11,0), 0))

Dovolte mi vysvětlit, jak tento vzorec funguje.

Zde jde o to použít 0 jako číslo sloupce.

Když použijete 0 jako číslo sloupce ve funkci INDEX, vrátí všechny hodnoty řádku. Podobně pokud jako číslo řádku použijete 0, vrátí všechny hodnoty ve sloupci.

Níže uvedená část vzorce tedy vrací pole hodnot - {97, 70, 73}

INDEX ($ B $ 2: $ D $ 11, MATCH ($ F $ 4, $ A $ 2: $ A $ 11,0), 0)

Pokud zadáte výše uvedený vzorec do buňky v aplikaci Excel a stisknete klávesu Enter, zobrazí se #HODNOTA! chyba. Důvodem je, že nevrací jedinou hodnotu, ale řadu hodnot.

Ale nebojte se, řada hodnot stále existuje. Můžete to zkontrolovat výběrem vzorce a stisknutím klávesy F9. Ukáže vám výsledek vzorce, kterým je v tomto případě pole tří hodnot - {97, 70, 73}

Nyní, když zabalíte tento vzorec INDEX do funkce SUMA, získáte součet všech bodů, které Jim zaznamenal.

Můžete je také použít k získání nejvyšších, nejnižších a průměrných známek Jima.

Stejně jako jsme to udělali pro studenta, můžete to udělat i pro předmět. Pokud například chcete průměrné skóre v předmětu, můžete ve vzorci INDEX ponechat číslo řádku jako 0 a poskytne vám všechny hodnoty sloupců tohoto předmětu.

Kliknutím sem stáhnete ukázkový soubor

Příklad 6: Najděte studentovu známku (přibližná technika shody)

Dosud jsme použili vzorec MATCH k získání přesné shody vyhledávací hodnoty.

Můžete jej však také použít k přibližnému shodu.

Co to sakra je Přibližný zápas?

Nech mě to vysvětlit.

Když hledáte věci, jako jsou jména nebo ID, hledáte přesnou shodu. Někdy však potřebujete znát rozsah, ve kterém leží vaše vyhledávací hodnoty. To je obvykle případ čísel.

Jako třídní učitel můžete například chtít vědět, jaké je hodnocení každého studenta v předmětu, a o hodnocení se rozhoduje na základě skóre.

Níže je uveden příklad, kde chci známku pro všechny studenty a o hodnocení se rozhoduje podle tabulky vpravo.

Pokud tedy student získá méně než 33, je známka F a pokud získá méně než 50, ale více než 33, je to E atd.

Níže je vzorec, který to udělá.

= INDEX ($ F $ 3: $ F $ 8, MATCH (B2, $ E $ 3: $ E $ 8,1), 1)

Dovolte mi vysvětlit, jak tento vzorec funguje.

Ve funkci MATCH jsme jako argument [match_type] použili 1. Tento argument vrátí největší hodnotu, která je menší nebo rovna vyhledávací hodnotě.

To znamená, že vzorec MATCH prochází rozsahem značek, a jakmile najde rozsah značek, který je stejný nebo menší než hodnota vyhledávacích značek, zastaví se tam a vrátí svou pozici.

Pokud je tedy hodnota značky vyhledávání 20, funkce MATCH vrátí 1 a pokud je 85, vrátí 5.

A funkce INDEX používá tuto hodnotu polohy k získání známky.

DŮLEŽITÉ: Aby to fungovalo, je třeba data řadit vzestupně. Pokud tomu tak není, můžete získat špatné výsledky.

Výše uvedené lze provést také pomocí níže uvedeného vzorce VLOOKUP:

= VLOOKUP (B2, $ E $ 3: $ F $ 8,2, PRAVDA)

Funkce MATCH však může jít o krok dále, pokud jde o přibližnou shodu.

Můžete také mít sestupná data a použít kombinaci INDEX MATCH k nalezení výsledku. Pokud například změním pořadí tabulky známek (jak je uvedeno níže), stále mohu najít známky studentů.

K tomu stačí změnit argument [match_type] na -1.

Níže je vzorec, který jsem použil:

= INDEX ($ F $ 3: $ F $ 8, MATCH (B2, $ E $ 3: $ E $ 8, -1), 1)

VLOOKUP může také provádět přibližnou shodu, ale pouze pokud jsou data seřazeny vzestupně (ale nefunguje, pokud jsou data seřazeny sestupně).

Příklad 7: Vyhledávání rozlišující malá a velká písmena

Zatím všechna vyhledávání, která jsme provedli, nerozlišovala malá a velká písmena.

To znamená, že nezáleží na tom, zda byla vyhledávací hodnota Jim nebo JIM nebo jim. Získáte stejný výsledek.

Ale co když chcete, aby vyhledávání rozlišovalo velká a malá písmena.

To je obvykle případ, kdy máte velké datové sady a možnost opakování nebo odlišná jména/ID (s jediným rozdílem)

Předpokládejme například, že mám následující datovou sadu studentů, kde jsou dva studenti se jménem Jim (jediný rozdíl je v tom, že jeden je zadán jako Jim a druhý jako Jim).

Všimněte si, že existují dva studenti se stejným jménem - Jim (buňka A2 a A5).

Protože normální vyhledávání by nefungovalo, musíte vyhledat malá a velká písmena.

Níže je vzorec, který vám poskytne správný výsledek. Protože se jedná o maticový vzorec, musíte použít Ctrl + Shift + Enter.

= INDEX ($ B $ 2: $ B $ 11, MATCH (TRUE, EXACT (D3, A2: A11), 0), 1)

Dovolte mi vysvětlit, jak tento vzorec funguje.

Funkce EXACT kontroluje přesnou shodu vyhledávací hodnoty (což je v tomto případě „jim“). Prochází všechna jména a vrací FALSE, pokud to není shoda, a TRUE, pokud je to shoda.

Takže výstup funkce EXACT v tomto příkladu je - {FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}

Všimněte si, že existuje pouze jedna PRAVDA, což je, když funkce EXACT nalezla dokonalou shodu.

Funkce MATCH pak najde pozici TRUE v poli vráceném funkcí EXACT, což je v tomto případě 4.

Jakmile máme pozici, funkce INDEX ji použije k nalezení značek.

Příklad 8: Najděte nejbližší zápas

Pojďme teď trochu pokročilý.

Předpokládejme, že máte datovou sadu, kde chcete najít osobu, která má pracovní zkušenosti nejblíže požadované praxi (uvedené v buňce D2).

Zatímco vyhledávací vzorce k tomu nejsou vytvořeny, můžete to zkombinovat s dalšími funkcemi (například MIN a ABS).

Níže je vzorec, který vyhledá osobu se zkušenostmi nejblíže požadované a vrátí jméno osoby. Pamatujte, že zážitek musí být nejbližší (což může být buď méně, nebo více).

= INDEX ($ A $ 2: $ A $ 15, MATCH (MIN (ABS (D2-B2: B15)), ABS (D2- $ B $ 2: $ B $ 15), 0))

Protože se jedná o maticový vzorec, musíte použít Ctrl + Shift + Enter.

Trikem v tomto vzorci je změnit vyhledávací hodnotu a vyhledávací pole tak, aby našel minimální rozdíl zkušeností v požadovaných a skutečných hodnotách.

Než vysvětlím vzorec, pojďme pochopit, jak byste to udělali ručně.

Projdete každou buňku ve sloupci B a najdete rozdíl ve zkušenostech mezi tím, co je požadováno, a tím, které má člověk. Jakmile máte všechny rozdíly, najdete ten, který je minimální, a načtěte jméno této osoby.

Přesně to děláme s tímto vzorcem.

Nech mě to vysvětlit.

Vyhledávací hodnota ve vzorci MATCH je MIN (ABS (D2-B2: B15)).

Tato část vám dává minimální rozdíl mezi danou zkušeností (což je 2,5 roku) a všemi ostatními zkušenostmi. V tomto případě vrátí 0,3

Všimněte si, že jsem použil ABS, abych se ujistil, že hledám nejbližší (což může být více nebo méně než daná zkušenost).

Nyní se tato minimální hodnota stává naší vyhledávací hodnotou.

Vyhledávací pole ve funkci MATCH je ABS (D2- $ B $ 2: $ B $ 15).

To nám dává řadu čísel, od kterých bylo odečteno 2,5 (požadovaná zkušenost).

Nyní tedy máme vyhledávací hodnotu (0,3) a vyhledávací pole ({6,8; 0,8; 19,5; 21,8; 14,5; 11,2; 0,3; 9,2; 2; 9,8; 14,8; 0,4; 23,8; 2,9})

Funkce MATCH najde v tomto poli pozici 0,3, což je také pozice jména osoby, která má nejbližší zkušenost.

Toto číslo pozice pak používá funkce INDEX k vrácení jména osoby.

Související čtení: Najděte nejbližší shodu v aplikaci Excel (příklady pomocí vyhledávacích vzorců)

Kliknutím sem stáhnete ukázkový soubor

Příklad 9: Použijte INDEX MATCH se zástupnými znaky

Pokud chcete vyhledat hodnotu v případě částečné shody, musíte použít zástupné znaky.

Níže je například uveden soubor údajů o názvu společnosti a jejich tržních kapitalizacích a chcete chtít získat tržní kapitalizaci. data pro tři společnosti napravo.

Protože se nejedná o přesné shody, nemůžete v tomto případě provádět pravidelné vyhledávání.

Správná data však stále můžete získat pomocí hvězdičky (*), což je zástupný znak.

Níže je vzorec, který vám poskytne data porovnáním názvů společností z hlavního sloupce a načtení hodnoty tržní kapitalizace.

= INDEX ($ B $ 2: $ B $ 10, MATCH (D2 & ”*”, $ A $ 2: $ A $ 10,0), 1)

Dovolte mi vysvětlit, jak tento vzorec funguje.

Protože neexistuje přesná shoda vyhledávacích hodnot, použil jsem D2 & ”*” jako vyhledávací hodnota ve funkci MATCH.

Hvězdička je zástupný znak, který představuje libovolný počet znaků. To znamená, že Apple* ve vzorci by znamenal jakýkoli textový řetězec, který začíná slovem Apple a může za ním mít libovolný počet znaků.

Takže když Jablko* se použije jako vyhledávací hodnota a vzorec MATCH ji vyhledá ve sloupci A, vrátí pozici „Apple Inc.“, protože začíná slovem Apple.

Můžete také použít zástupné znaky k vyhledání textových řetězců, kde je hodnota vyhledávání mezi nimi. Pokud například použijete jako vyhledávací hodnotu * Apple *, najde libovolný řetězec se slovem apple kdekoli v něm.

Poznámka: Tato technika funguje dobře, pokud máte pouze jednu instanci shody. Pokud ale máte více instancí shody (například Apple Inc a Apple Corporation, pak by funkce MATCH vrátila pozici pouze první instance párování.

Příklad 10: Trojcestné vyhledávání

Jedná se o pokročilé použití INDEX MATCH, ale přesto se jím budu zabývat, abych vám ukázal sílu této kombinace.

Pamatujte, že jsem řekl, že funkce INDEX má dvě syntaxe:

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

Zatím jsme ve všech našich příkladech použili pouze ten první.

Ale pro třísměrné vyhledávání musíte použít druhou syntaxi.

Nejprve vysvětlím, co znamená trojcestný vzhled.

Při obousměrném vyhledávání používáme vzorec INDEX MATCH k získání známek, když máme jméno studenta a jméno předmětu. Například načítání značek Jim v matematice je obousměrné vyhledávání.

Trojcestný vzhled by tomu dodal další rozměr. Předpokládejme například, že máte datovou sadu, jak je uvedeno níže, a chcete znát skóre Jima v matematice v polovině období zkoušky, pak by to bylo třícestné vyhledávání.

Níže je vzorec, který poskytne výsledek.

= INDEX (($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23), MATCH ($ F $ 5, $ A $ 3: $ A $ 7,0), MATCH (G $ 4 , $ B $ 2: $ D $ 2,0), (IF (G $ 3 = „Unit Test“, 1, IF (G $ 3 = „Mid Term“, 2,3))))

Výše uvedený vzorec zkontroloval tři věci - jméno studenta, předmět a zkoušku. Poté, co najde správnou hodnotu, vrátí ji do buňky.

Vysvětlím, jak tento vzorec funguje, rozdělením vzorce na části.

• pole - ($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23): Místo použití jediného pole jsem v tomto případě použil tři pole v závorkách.
• row_num - MATCH ($ F $ 5, $ A $ 3: $ A $ 7,0): Funkce MATCH se používá k nalezení pozice jména studenta v buňce $ F $ 5 v seznamu jména studenta.
• col_num - MATCH (G $ 4, $ B $ 2: $ D $ 2,0): Funkce MATCH se používá k nalezení polohy názvu subjektu v buňce $ B $ 2 v seznamu názvu subjektu.
• [area_num] - IF (G $ 3 = ”Unit Test”, 1, IF (G $ 3 = “Mid Term”, 2,3)): Hodnota čísla oblasti sděluje funkci INDEX, které ze tří polí použít k načtení hodnoty. Pokud je zkouška Unit Term, funkce IF by vrátila 1 a funkce INDEX by použila první pole k načtení hodnoty. Pokud je zkouška v polovině období, vzorec IF vrátí 2, jinak vrátí 3.

Toto je pokročilý příklad použití INDEX MATCH a je nepravděpodobné, že byste našli situaci, kdy ji budete muset použít. Ale stále je dobré vědět, co vzorce Excelu dokážou.

Kliknutím sem stáhnete ukázkový soubor

Proč je INDEX/MATCH lepší než VLOOKUP?

Nebo je to?

Ano, je - ve většině případů.

Za chvíli představím svůj případ.

Ale než to udělám, řeknu to - VLOOKUP je nesmírně užitečná funkce a mám ji rád. V Excelu dokáže spoustu věcí a sám je každou chvíli používám. To však neznamená, že nemůže být nic lepšího, a INDEX/MATCH (s větší flexibilitou a funkčností) je lepší.

Pokud tedy chcete provést nějaké základní vyhledávání, je lepší použít VLOOKUP.

INDEX/MATCH je VLOOKUP na steroidech. A jakmile se naučíte INDEX/MATCH, můžete vždy dávat přednost jeho používání (zejména kvůli flexibilitě, kterou má).

Aniž bych to příliš natáhl, dovolte mi, abych vám rychle vysvětlil důvody, proč je INDEX/MATCH lepší než VLOOKUP.

INDEX/MATCH se může dívat doleva (stejně jako napravo) od vyhledávací hodnoty

Pokryl jsem to v jednom z výše uvedených příkladů.

Pokud máte hodnotu nalevo od vyhledávací hodnoty, nemůžete to pomocí VLOOKUP udělat

Alespoň ne pouze pomocí VLOOKUP.

Ano, můžete zkombinovat VLOOKUP s jinými vzorci a udělat to, ale je to komplikované a nepořádné.

INDEX/MATCH je naopak určen k vyhledávání všude (ať už vlevo, vpravo, nahoru nebo dolů)

INDEX/MATCH může pracovat se svislými a vodorovnými rozsahy

Opět platí, že při plném respektu k VLOOKUP to není dělané.

Koneckonců, V ve VLOOKUP znamená vertikální.

VLOOKUP může procházet pouze daty, která jsou svislá, zatímco INDEX/MATCH může procházet daty svisle i vodorovně.

Samozřejmě existuje funkce HLOOKUP, která se stará o horizontální vyhledávání, ale není to VLOOKUP, pak … ano?

Líbí se mi skutečnost, že kombinace INDEX MATCH je dostatečně flexibilní pro práci s vertikálními i horizontálními daty.

VLOOKUP nemůže pracovat se sestupnými daty

Pokud jde o přibližnou shodu, VLOOKUP a INDEX/MATCH jsou na stejné úrovni.

Ale INDEX MATCH bere smysl, protože dokáže zpracovat i data sestupně.

Ukazuji to v jednom z příkladů v tomto tutoriálu, kde musíme podle klasifikační tabulky najít známku studentů. Pokud je tabulka seřazena sestupně, VLOOKUP by nefungoval (ale INDEX MATCH ano).

INDEX/MATCH může být o něco rychlejší

Budu pravdivý. Tento test jsem sám neprovedl.

Spoléhám na moudrost excelského mistra - Charley Kyda.

Rozdíl v rychlosti ve VLOOKUP a INDEX/MATCH je sotva znatelný, pokud máte malé datové sady. Pokud však máte tisíce řádků a mnoho sloupců, může to být rozhodující faktor.

Charley Kyd ve svém článku uvádí:

"V nejhorším případě je metoda INDEX-MATCH zhruba tak rychlá jako VLOOKUP; v nejlepším případě je to mnohem rychlejší. “

INDEX/MATCH je nezávislý na aktuální pozici sloupce

Pokud máte sadu dat, jak je ukázáno níže, když načítáte skóre Jima ve fyzice, můžete to udělat pomocí VLOOKUP.

A za tímto účelem můžete ve VLOOKUP zadat číslo sloupce jako 3.

Všechno je v pořádku.

Ale co když odstraním sloupec Matematika.

V takovém případě se vzorec VLOOKUP rozbije.

Proč? - Protože bylo napevno použito třetí sloupec, a když mezitím odstraním sloupec, stane se třetí sloupec druhým sloupcem.

V tomto případě je lepší použít INDEX/MATCH, protože pomocí MATCH můžete číslo sloupce dynamizovat. Místo čísla sloupce tedy zkontroluje název subjektu a pomocí něj vrátí číslo sloupce.

Určitě to můžete udělat kombinací VLOOKUP s MATCH, ale pokud stejně kombinujete, proč to neudělat s INDEXem, který je mnohem flexibilnější.

Při použití INDEX/MATCH můžete bezpečně vkládat/mazat sloupce do vaší datové sady.

Navzdory všem těmto faktorům existuje důvod, proč je VLOOKUP tak populární.

A to je velký důvod.

VLOOKUP se používá snadněji

VLOOKUP trvá pouze maximálně čtyři argumenty. Pokud si kolem těchto čtyř můžete omotat hlavu, můžete vyrazit.

A protože většinu základních vyhledávacích případů řeší také VLOOKUP, rychle se stala nejoblíbenější funkcí Excelu.

Říkám tomu funkce King of Excel.

INDEX/MATCH je na druhou stranu trochu náročnější na používání. Když to začnete používat, můžete se zaseknout, ale pro začátečníka je VLOOKUP mnohem jednodušší na vysvětlení a učení.

A nejedná se o hru s nulovým součtem.

Pokud jste tedy ve světě vyhledávání noví a nevíte, jak používat VLOOKUP, naučte se to lépe.

Mám podrobného průvodce používáním VLOOKUP v aplikaci Excel (se spoustou příkladů)

Mým záměrem v tomto článku není postavit proti sobě dvě úžasné funkce. Chtěl jsem vám ukázat sílu kombinace INDEX MATCH a všechny skvělé věci, které dokáže.

Doufám, že jste našli tento článek užitečný.

Dejte mi vědět své myšlenky v sekci komentáře a v případě, že v tomto tutoriálu najdete nějakou chybu, dejte mi prosím vědět.