Kombinácia funkcií INDEX a MATCH v Exceli (10 jednoduchých príkladov)

Excel má veľa funkcií - asi 450+ z nich.

A mnohé z nich sú jednoducho úžasné. Množstvo práce, ktorú môžete vykonať s niekoľkými vzorcami, ma stále prekvapuje (aj po použití Excelu 10 a viac rokov).

A medzi všetkými týmito úžasnými funkciami vyniká kombinácia funkcií INDEX MATCH.

Som veľkým fanúšikom kombinácie INDEX MATCH a mnohokrát som to dosť jasne uviedol.

Dokonca som napísal článok o indexovom zápase VLOOKUP, ktorý vyvolal malú diskusiu (ohňostroj môžete skontrolovať v sekcii komentárov).

A dnes píšem tento článok výlučne o indexovom zhode, aby som vám ukázal niekoľko jednoduchých a pokročilých scenárov, v ktorých môžete použiť toto silné kombinované vzorce a prácu dokončiť.

Poznámka: V programe Excel existujú aj iné vzorce na vyhľadávanie - napríklad VLOOKUP a HLOOKUP a sú skvelé. Mnoho ľudí považuje používanie VLOOKUPU za jednoduchšie (a to tiež platí). Verím, že INDEX MATCH je v mnohých prípadoch lepšou voľbou. Ale keďže to ľudia majú ťažké, používa sa to menej. Pokúšam sa to teda zjednodušiť pomocou tohto tutoriálu.

Teraz, než vám ukážem, ako kombinácia INDEX MATCH mení svet analytikov a vedcov údajov, dovoľte mi, aby som vám najskôr predstavil jednotlivé časti - funkcie INDEX a MATCH.

Funkcia INDEX: Nájde hodnotu na základe súradníc

Najjednoduchší spôsob, ako pochopiť, ako funguje funkcia indexu, je chápať ho ako satelit GPS.

Hneď ako satelitu poviete súradnice zemepisnej šírky a dĺžky, bude presne vedieť, kam má ísť, a nájsť toto miesto.

Napriek tomu, že satelit mal ohromujúci počet lat-long kombinácií, vedel by presne, kam sa pozrieť.

Rýchlo som hľadal svoje pracovné miesto a toto som dostal.

Každopádne, dosť bolo geografie.

Rovnako ako satelit potrebuje súradnice zemepisnej šírky a dĺžky, funkcia INDEX v programe Excel bude potrebovať číslo riadka a stĺpca, aby vedela, o akú bunku ide.

A to je funkcia Excel INDEX v skratke.

Dovoľte mi to teda definovať pre vás jednoduchými slovami.

Funkcia INDEX použije číslo riadka a číslo stĺpca na vyhľadanie bunky v danom rozsahu a vrátenie hodnoty v nej.

INDEX je sám o sebe veľmi jednoduchou funkciou bez akéhokoľvek pomocného programu. Koniec koncov, vo väčšine prípadov pravdepodobne nebudete poznať čísla riadkov a stĺpcov.

Ale…

Skutočnosť, že ho môžete používať s inými funkciami (nápoveda: MATCH), ktoré dokážu nájsť číslo riadka a číslo stĺpca, robí z INDEXU mimoriadne výkonnú funkciu programu Excel.

Nasleduje syntax funkcie INDEX:

= INDEX (pole, počet_ riadkov, [stĺpcový_číslo]) = INDEX (maticové, riadkové_číslo, [stĺpcové_číslo], [oblasť_číslo])

• pole - a rozsah buniek alebo konštanta poľa.
• row_num - číslo riadka, z ktorého sa má hodnota načítať.
• [col_num] - číslo stĺpca, z ktorého sa má hodnota načítať. Toto je voliteľný argument, ale ak nie je zadaný riadok_číslo, je potrebné ho zadať.
• [area_num] - (Voliteľné) Ak argument poľa pozostáva z viacerých rozsahov, toto číslo by sa použilo na výber referencie zo všetkých rozsahov.

Funkcia INDEX má 2 syntaxe (iba FYI).

Prvý sa používa vo väčšine prípadov. Druhý sa používa iba v pokročilých prípadoch (napríklad pri trojcestnom vyhľadávaní), ktorým sa budeme venovať v jednom z príkladov ďalej v tomto návode.

Ak ste však s touto funkciou nováčik, zapamätajte si prvú syntax.

Nasleduje video, ktoré vysvetľuje, ako používať funkciu INDEX

Funkcia MATCH: Nájde pozíciu podľa hodnoty vyhľadávania

Keď sa vrátim k svojmu predchádzajúcemu príkladu zemepisnej šírky a šírky, MATCH je funkcia, ktorá dokáže nájsť tieto polohy (vo svete tabuliek programu Excel).

V jednoduchom jazyku môže funkcia Excel MATCH nájsť polohu bunky v rozsahu.

A na základe čoho by našla polohu bunky?

Na základe vyhľadávacej hodnoty.

Ak máte napríklad zoznam uvedený nižšie a chcete v ňom nájsť pozíciu názvu „Mark“, môžete použiť funkciu MATCH.

Funkcia vráti 3, pretože to je pozícia bunky, v ktorej je názov Mark.

Funkcia MATCH začne shora nadol vyhľadávať hodnotu (ktorá je „značka“) v zadanom rozsahu (čo je v tomto prípade A1: A9). Hneď ako nájde názov, vráti pozíciu v tomto konkrétnom rozsahu.

Nasleduje syntax funkcie MATCH v programe Excel.

= MATCH (lookup_value, lookup_array, [match_type])

• lookup_value - Hodnota, pre ktorú hľadáte zhodu v poli lookup_array.
• lookup_array - Rozsah buniek, v ktorých hľadáte hodnotu lookup_value.
• [match_type] - (Voliteľné) Toto určuje, ako by mal Excel hľadať zodpovedajúcu hodnotu. Môže mať tri hodnoty -1, 0 alebo 1.

Pochopenie argumentu typu zhody vo funkcii MATCH

O funkcii MATCH potrebujete vedieť ešte jednu vec, a to o tom, ako prechádza údajmi a nachádza polohu bunky.

Tretí argument funkcie MATCH môže byť 0, 1 alebo -1.

Nasleduje vysvetlenie, ako tieto argumenty fungujú:

• 0 - vyhľadá sa presná zhoda hodnoty. Ak sa nájde presná zhoda, funkcia MATCH vráti polohu bunky. V opačnom prípade vráti chybu.
• 1 - vyhľadá najväčšiu hodnotu, ktorá je menšia alebo rovná vyhľadávanej hodnote. Aby to fungovalo, je potrebné rozsah údajov zoradiť vzostupne.
• -1 - vyhľadá najmenšiu hodnotu, ktorá je väčšia alebo rovná vyhľadávanej hodnote. Aby to fungovalo, je potrebné rozsah údajov zoradiť zostupne.

Nasleduje video, ktoré vysvetľuje, ako používať funkciu MATCH (spolu s argumentom typu zhody)

Aby som to zhrnul a vyjadril jednoduchými slovami:

• INDEX potrebuje pozíciu bunky (číslo riadka a stĺpca) a udáva hodnotu bunky.
• MATCH nájde pozíciu pomocou vyhľadávacej hodnoty.

Poďme ich skombinovať a vytvoriť powerhouse (INDEX + MATCH)

Teraz, keď máte základné znalosti o tom, ako funkcie INDEX a MATCH fungujú jednotlivo, skombinujme tieto dve a naučme sa všetky úžasné veci, ktoré dokáže.

Aby som to lepšie pochopil, mám niekoľko príkladov, ktoré používajú kombináciu INDEX MATCH.

Začnem jednoduchým príkladom a potom vám ukážem aj niekoľko pokročilých prípadov použitia.

Kliknutím sem stiahnete ukážkový súbor

Príklad 1: Jednoduché vyhľadávanie pomocou kombinácie INDEX MATCH Combo

Urobme jednoduché vyhľadávanie pomocou INDEX/MATCH.

Nasleduje tabuľka, kde mám známky pre desať študentov.

Z tejto tabuľky chcem nájsť značky pre Jima.

Nasleduje vzorec, ktorý to môže ľahko vykonať:

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

Ak si teraz myslíte, že sa to dá ľahko vykonať pomocou funkcie VLOOKUP, máte pravdu! Toto nie je najlepšie využitie úžasnosti INDEX MATCH. Napriek tomu, že som fanúšikom INDEX MATCH, je to o niečo ťažšie ako VLOOKUP. Ak vám ide iba o načítanie údajov zo stĺpca napravo, odporúčam vám použiť VLOOKUP.

Dôvod, prečo som ukázal tento príklad, ktorý je tiež možné ľahko vykonať pomocou funkcie VLOOKUP, je ukázať vám, ako INDEX MATCH funguje v jednoduchom nastavení.

Teraz mi ukážem výhodu INDEX MATCH.

Predpokladajme, že máte rovnaké údaje, ale namiesto toho, aby ste ich mali v stĺpcoch, máte ich v riadkoch (ako je uvedené nižšie).

Viete čo, stále môžete použiť kombináciu INDEX MATCH na získanie Jimových známok.

Nasleduje vzorec, ktorý vám poskytne výsledok:

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

Všimnite si toho, že musíte zmeniť rozsah a prepnúť časti riadkov/stĺpcov, aby tento vzorec fungoval aj pre horizontálne údaje.

To sa nedá dosiahnuť pomocou VLOOKUP, ale napriek tomu to môžete ľahko urobiť pomocou HLOOKUP.

Kombinácia INDEX MATCH si ľahko poradí s horizontálnymi aj vertikálnymi údajmi.

Kliknutím sem stiahnete ukážkový súbor

Príklad 2: Vyhľadanie vľavo

Je to bežnejšie, ako si myslíte.

Mnohokrát sa od vás môže vyžadovať načítanie údajov zo stĺpca, ktorý je naľavo od stĺpca s hodnotou vyhľadávania.

Niečo, ako je uvedené nižšie:

Ak chcete zistiť Michaelove tržby, budete musieť urobiť vyhľadávanie vľavo.

Ak uvažujete o VLOOKUP, dovoľte mi zastaviť sa.

VLOOKUP nie je určený na vyhľadávanie a načítanie hodnôt vľavo.

Stále to môžete robiť pomocou VLOOKUP?

Áno môžeš!

To sa však môže zmeniť na dlhý a škaredý vzorec.

Ak teda chcete vykonať vyhľadávanie a načítať údaje zo stĺpcov vľavo, je lepšie použiť kombináciu INDEX MATCH.

Nasleduje vzorec, ktorý získa Michaelovo predajné číslo:

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

Ďalší bod tu pre INDEX MATCH. VLOOKUP môže načítať údaje iba zo stĺpcov, ktoré sú napravo od stĺpca, ktorý má hodnotu vyhľadávania.

Príklad 3: Obojsmerné vyhľadávanie

Doposiaľ sme videli príklady, kde sme chceli načítať údaje zo stĺpca susediaceho so stĺpcom, ktorý má hodnotu vyhľadávania.

V skutočnom živote však údaje často pokrývajú viac stĺpcov.

INDEX MATCH môže ľahko zvládnuť obojsmerné vyhľadávanie.

Nasleduje súbor údajov o známkach študenta z troch rôznych predmetov.

Ak chcete rýchlo získať známky študenta vo všetkých troch predmetoch, môžete to urobiť pomocou INDEX MATCH.

Nasledujúci vzorec vám poskytne známky pre Jima pre všetky tri predmety (skopírujte a prilepte do jednej bunky a ťahaním vyplňte ostatné bunky alebo skopírujte a prilepte do iných buniek).

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

Dovoľte mi tiež rýchlo vysvetliť tento vzorec.

Vzorec INDEX používa ako rozsah B2: D11.

Prvý MATCH používa meno (Jim v bunke F3) a vyhľadá jeho pozíciu v stĺpci mien (A2: A11). Toto sa stane číslom riadku, z ktorého je potrebné načítať údaje.

Druhý vzorec MATCH používa názov subjektu (v bunke G2) na získanie polohy tohto konkrétneho názvu subjektu v B1: D1. Napríklad matematika je 1, fyzika je 2 a chémia je 3.

Pretože tieto pozície MATCH sú vložené do funkcie INDEX, vráti skóre na základe mena študenta a názvu predmetu.

Tento vzorec je dynamický, čo znamená, že ak zmeníte meno študenta alebo názov predmetu, bude fungovať a načítať správne údaje.

Jedna skvelá vec na používaní INDEX/MATCH je, že aj keď si vymeníte názvy predmetov, bude vám to aj naďalej poskytovať správny výsledok.

Príklad 4: Hodnota vyhľadávania z viacerých stĺpcov/kritérií

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete načítať značky pre „Mark Long“.

Pretože sú údaje v dvoch stĺpcoch, nemôžem nájsť Marka a získať údaje.

Ak to urobím takto, získam údaje o značkách pre Mark Frost a nie pre Mark Long (pretože funkcia MATCH mi poskytne výsledok pre značku, ktorú spĺňa).

Jedným zo spôsobov, ako to dosiahnuť, je vytvoriť pomocný stĺpec a skombinovať názvy. Keď máte pomocný stĺpec, môžete použiť VLOOKUP a získať údaje o značkách.

Ak vás zaujíma, ako to urobiť, prečítajte si tento návod na používanie funkcie VLOOKUP s viacerými kritériami.

Ale s kombináciou INDEX/MATCH nepotrebujete pomocný stĺpec. Môžete vytvoriť vzorec, ktorý v samotnom vzorci zvládne viacero kritérií.

Nasledujúci vzorec poskytne výsledok.

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

Dovoľte mi rýchlo vysvetliť, čo tento vzorec robí.

Časť MATCH vzorca kombinuje vyhľadávaciu hodnotu (Mark a Long) a celé vyhľadávacie pole. Keď sa ako vyhľadávacie pole použije $ A $ 2: A11 & ”|” & $ B $ 2: $ B $ 11, v skutočnosti kontroluje vyhľadanú hodnotu oproti kombinovanému reťazcu mena a priezviska (oddeleného symbolom potrubia).

To zaisťuje, že získate správny výsledok bez použitia pomocných stĺpcov.

Tento druh vyhľadávania (kde existuje viac stĺpcov/kritérií) môžete vykonať aj pomocou funkcie VLOOKUP, ale musíte použiť pomocný stĺpec. Kombinácia INDEX MATCH to mierne uľahčuje bez pomocných stĺpcov.

Príklad 5: Získajte hodnoty z celého riadka/stĺpca

Vo vyššie uvedených príkladoch sme použili funkciu INDEX na získanie hodnoty z konkrétnej bunky. Zadajte číslo riadka a stĺpca a vráti hodnotu v konkrétnej bunke.

Môžete však urobiť viac.

Na získanie hodnôt z celého riadka alebo stĺpca môžete použiť aj funkciu INDEX.

Pýtate sa, ako to môže byť užitočné!

Predpokladajme, že chcete poznať celkové skóre Jima vo všetkých troch predmetoch.

Na získanie všetkých Jimových známok môžete použiť funkciu INDEX a potom pomocou súčtu získate súčet.

Pozrime sa, ako to urobiť.

Ďalej uvádzam skóre všetkých študentov v troch predmetoch.

Nasledujúci vzorec mi poskytne celkové skóre Jima vo všetkých troch predmetoch.

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

Vysvetlím, ako tento vzorec funguje.

Ide o to, použiť ako číslo stĺpca 0.

Keď ako funkciu stĺpca vo funkcii INDEX použijete 0, vráti všetky hodnoty riadkov. Podobne, ak ako číslo riadka použijete 0, vráti všetky hodnoty v stĺpci.

Nasledujúca časť vzorca teda vracia pole hodnôt - {97, 70, 73}

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

Ak zadáte vyššie uvedený vzorec do bunky v programe Excel a stlačíte kláves Enter, zobrazí sa #HODNOTA! chyba. Dôvodom je, že nevracia jednu hodnotu, ale pole hodnôt.

Ale nebojte sa, množstvo hodnôt stále existuje. Môžete to skontrolovať výberom vzorca a stlačením klávesu F9. Ukáže vám výsledok vzorca, ktorý je v tomto prípade poľom troch hodnôt - {97, 70, 73}

Ak teraz zabalíte tento vzorec INDEX do funkcie SUMA, poskytne vám súčet všetkých známok, ktoré dosiahol Jim.

To isté môžete použiť aj na získanie najvyšších, najnižších a priemerných známok Jima.

Rovnako ako sme to urobili pre študenta, môžete to urobiť aj pre predmet. Napríklad, ak chcete priemerné skóre v predmete, môžete vo vzorci INDEX ponechať číslo riadku ako 0 a poskytne vám všetky hodnoty stĺpcov tohto predmetu.

Kliknutím sem stiahnete ukážkový súbor

Príklad 6: Nájdite známku študenta (technika približného zápasu)

Doteraz sme na získanie presnej zhody vyhľadávanej hodnoty použili vzorec MATCH.

Môžete ho však použiť aj na približnú zhodu.

Čo je to do pekla približný zápas?

Nechaj ma vysvetliť.

Keď hľadáte veci, ako sú mená alebo ID, hľadáte presnú zhodu. Niekedy však potrebujete vedieť, v akom rozsahu sa nachádzajú vaše vyhľadávacie hodnoty. Obvykle to platí pre čísla.

Ako triedny učiteľ napríklad môžete chcieť vedieť, aký je prospech každého študenta z predmetu, a o známke sa rozhoduje na základe skóre.

Nasleduje príklad, kde chcem známku pre všetkých študentov a o klasifikácii sa rozhoduje podľa tabuľky vpravo.

Ak teda študent dostane menej ako 33, známka je F a ak dostane menej ako 50, ale viac ako 33, je to E a podobne.

Nasleduje vzorec, ktorý to urobí.

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

Vysvetlím, ako tento vzorec funguje.

Vo funkcii MATCH sme použili 1 ako argument [match_type]. Tento argument vráti najväčšiu hodnotu, ktorá je menšia alebo rovná vyhľadávanej hodnote.

To znamená, že vzorec MATCH prechádza rozsahom značiek a akonáhle nájde rozsah značiek, ktorý je rovný alebo menší ako hodnota vyhľadávacích značiek, zastaví sa tam a vráti svoju pozíciu.

Ak je teda hodnota značky vyhľadávania 20, funkcia MATCH vráti 1 a ak je 85, vráti 5.

A funkcia INDEX používa túto hodnotu polohy na získanie známky.

DÔLEŽITÉ: Aby to fungovalo, vaše údaje je potrebné zoradiť vzostupne. Ak nie, môžete získať nesprávne výsledky.

Uvedené skutočnosti je možné vykonať aj pomocou nižšie uvedeného vzorca VLOOKUP:

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

Funkcia MATCH však môže ísť o krok ďalej, pokiaľ ide o približnú zhodu.

Môžete mať aj zostupné údaje a na nájdenie výsledku môžete použiť kombináciu INDEX MATCH. Ak napríklad zmením poradie v tabuľke známok (ako je uvedené nižšie), stále môžem nájsť známky študentov.

Na to stačí zmeniť argument [match_type] na -1.

Nasleduje vzorec, ktorý som použil:

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

VLOOKUP môže tiež vykonávať približnú zhodu, ale iba vtedy, ak sú údaje zoradené vzostupne (ale nefunguje, ak sú údaje zoradené zostupne).

Príklad 7: Vyhľadávanie malých a veľkých písmen

Doteraz všetky vyhľadávania, ktoré sme vykonali, nerozlišovali malé a veľké písmená.

To znamená, že nezáleží na tom, či bola vyhľadávaná hodnota Jim alebo JIM alebo jim. Dosiahnete rovnaký výsledok.

Ale čo keď chcete, aby vyhľadávanie rozlišovalo veľké a malé písmena.

To je zvyčajne prípad, keď máte veľké množiny údajov a možnosť opakovania alebo odlišné názvy/ID (s jediným rozdielom)

Predpokladajme napríklad, že mám nasledujúci súbor študentov, kde sú dvaja študenti s menom Jim (jediný rozdiel je v tom, že jeden je zadaný ako Jim a druhý ako Jim).

Všimnite si toho, že existujú dvaja študenti s rovnakým menom - Jim (bunka A2 a A5).

Pretože normálne vyhľadávanie nefunguje, musíte vyhľadať veľké a malé písmená.

Nasleduje vzorec, ktorý vám poskytne správny výsledok. Pretože ide o vzorec poľa, musíte použiť kombináciu klávesov Ctrl + Shift + Enter.

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

Vysvetlím, ako tento vzorec funguje.

Funkcia EXACT kontroluje presnú zhodu vyhľadávanej hodnoty (ktorá je v tomto prípade „jim“). Prechádza všetkými názvami a vracia hodnotu FALSE, ak nejde o zhodu, a TRUE, ak ide o zhodu.

Výstup funkcie EXACT v tomto prípade je - {FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}

Všimnite si toho, že existuje iba jedna PRAVDA, a to vtedy, keď funkcia EXACT našla perfektnú zhodu.

Funkcia MATCH potom nájde pozíciu TRUE v poli vrátenom funkciou EXACT, ktorá je v tomto prípade 4.

Akonáhle máme pozíciu, funkcia INDEX ju použije na nájdenie značiek.

Príklad 8: Nájdite najbližší zápas

Teraz trochu pokročíme.

Predpokladajme, že máte množinu údajov, v ktorej chcete nájsť osobu, ktorá má pracovné skúsenosti najbližšie k požadovanej praxi (uvedené v bunke D2).

Aj keď vyhľadávacie vzorce na to nie sú, môžete ich skombinovať s inými funkciami (napríklad MIN a ABS).

Nasleduje vzorec, ktorý vyhľadá osobu so skúsenosťami, ktoré sú najbližšie k požadovanej, a vráti meno osoby. Upozorňujeme, že zážitok musí byť najbližší (čo môže byť buď menej, alebo viac).

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

Pretože ide o vzorec poľa, musíte použiť kombináciu klávesov Ctrl + Shift + Enter.

Trikom v tomto vzorci je zmeniť vyhľadávaciu hodnotu a vyhľadávacie pole tak, aby našiel minimálny rozdiel v skúsenostiach v požadovaných a skutočných hodnotách.

Predtým, ako vysvetlím vzorec, pochopme, ako by ste to urobili ručne.

Prejdete každou bunkou v stĺpci B a zistíte rozdiel v skúsenostiach medzi tým, čo je požadované a tým, ktoré má človek. Keď máte všetky rozdiely, nájdete ten, ktorý je minimálny, a načítajte meno tejto osoby.

Presne to robíme s týmto vzorcom.

Nechaj ma vysvetliť.

Vyhľadávacia hodnota vo vzorci MATCH je MIN (ABS (D2-B2: B15)).

Táto časť vám dáva minimálny rozdiel medzi danou praxou (ktorá je 2,5 roka) a všetkými ostatnými skúsenosťami. V tomto prípade vráti 0,3

Všimnite si toho, že som použil ABS, aby som sa uistil, že hľadám najbližšie (čo môže byť viac alebo menej ako daný zážitok).

Teraz sa táto minimálna hodnota stáva našou vyhľadávacou hodnotou.

Pole vyhľadávania vo funkcii MATCH je ABS (D2- $ B $ 2: $ B $ 15).

To nám dáva pole čísel, od ktorých bolo odpočítaných 2,5 (požadovaná skúsenosť).

Teraz teda máme vyhľadávaciu hodnotu (0,3) a vyhľadávacie 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})

Funkcia MATCH nájde v tomto poli pozíciu 0,3, čo je tiež pozícia mena osoby, ktorá má najbližšie skúsenosti.

Toto číslo pozície potom použije funkcia INDEX na vrátenie mena osoby.

Súvisiace čítanie: Nájdite najbližšiu zhodu v Exceli (príklady pomocou vyhľadávacích vzorcov)

Kliknutím sem stiahnete ukážkový súbor

Príklad 9: Použite INDEX MATCH so zástupnými znakmi

Ak chcete vyhľadať hodnotu v prípade čiastočnej zhody, musíte použiť zástupné znaky.

Nasleduje napríklad súbor údajov o názve spoločnosti a ich trhových kapitalizáciách a chcete získať trhovú kapitalizáciu. údaje za tri spoločnosti napravo.

Pretože to nie sú presné zhody, nemôžete v tomto prípade vykonávať pravidelné vyhľadávanie.

Správne údaje však stále môžete získať pomocou hviezdičky (*), ktorá je zástupným znakom.

Nasleduje vzorec, ktorý vám poskytne údaje porovnaním názvov spoločností z hlavného stĺpca a načítaním údajov o trhovej kapitalizácii.

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

Vysvetlím, ako tento vzorec funguje.

Pretože neexistuje presná zhoda vyhľadávacích hodnôt, použil som D2 & ”*” ako vyhľadávacia hodnota vo funkcii MATCH.

Hviezdička je zástupný znak, ktorý predstavuje ľubovoľný počet znakov. To znamená, že Apple* vo vzorci by znamenal ľubovoľný textový reťazec, ktorý začína slovom Apple a môže mať za ním ľubovoľný počet znakov.

Takže keď Apple* sa používa ako hodnota vyhľadávania a vzorec MATCH ho hľadá v stĺpci A, vráti pozíciu „Apple Inc.“, pretože začína slovom Apple.

Na nájdenie textových reťazcov, kde je hodnota vyhľadávania medzi, môžete použiť aj zástupné znaky. Ak napríklad použijete ako vyhľadávaciu hodnotu * Apple *, nájde ľubovoľný reťazec so slovom jablko kdekoľvek v ňom.

Poznámka: Táto technika funguje, ak máte iba jednu inštanciu párovania. Ak však máte viac inštancií párovania (napríklad Apple Inc a Apple Corporation, funkcia MATCH vráti pozíciu iba prvej inštancie párovania.

Príklad 10: Trojcestné vyhľadávanie

Jedná sa o pokročilé použitie INDEX MATCH, ale stále sa ním budem zaoberať, aby som vám ukázal silu tejto kombinácie.

Nezabudnite, že som povedal, že funkcia INDEX má dve syntaxe:

= INDEX (pole, počet_ riadkov, [stĺpcový_číslo]) = INDEX (maticové, riadkové_číslo, [stĺpcové_číslo], [oblasť_číslo])

Doteraz sme vo všetkých našich príkladoch použili iba prvý.

Na trojsmerné vyhľadávanie však musíte použiť druhú syntax.

Najprv vysvetlím, čo znamená trojcestný vzhľad.

Pri obojsmernom vyhľadávaní používame vzorec INDEX MATCH na získanie známok, keď poznáme meno študenta a meno predmetu. Napríklad načítanie značiek Jima v matematike je obojsmerné vyhľadávanie.

Trojcestný vzhľad by tomu dodal ďalší rozmer. Predpokladajme napríklad, že máte množinu údajov, ako je uvedené nižšie, a chcete poznať skóre Jima v matematike na strednodobej skúške, potom by to bolo trojsmerné vyhľadávanie.

Nasleduje vzorec, ktorý poskytne výsledok.

= 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))))

Vyššie uvedený vzorec kontroloval tri veci - meno študenta, predmet a skúšku. Potom, čo nájde správnu hodnotu, vráti ju do bunky.

Vysvetlím, ako tento vzorec funguje, tým, že ho rozložím na časti.

• pole - ($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23): Namiesto použitia jedného poľa som v tomto prípade použil tri polia v zátvorkách.
• row_num - MATCH ($ F $ 5, $ A $ 3: $ A $ 7,0): Funkcia MATCH sa používa na nájdenie polohy mena študenta v bunke $ F $ 5 v zozname mena študenta.
• col_num - MATCH (G $ 4, $ B $ 2: $ D $ 2,0): Funkcia MATCH sa používa na nájdenie polohy názvu subjektu v bunke $ B $ 2 v zozname mien subjektu.
• [area_num] - IF (G $ 3 = ”Unit Test”, 1, IF (G $ 3 = “Mid Term”, 2,3)): Hodnota čísla oblasti hovorí funkcii INDEX, ktoré z troch polí použiť na načítanie hodnoty. Ak je skúškou Unit Term, funkcia IF by vrátila hodnotu 1 a funkcia INDEX by na načítanie hodnoty použila prvé pole. Ak je skúška strednodobá, vzorec IF vráti 2, v opačnom prípade vráti 3.

Toto je pokročilý príklad použitia INDEX MATCH a je nepravdepodobné, že by ste našli situáciu, keď ho budete musieť použiť. Ale stále je dobré vedieť, čo vzorce Excelu dokážu.

Kliknutím sem stiahnete ukážkový súbor

Prečo je INDEX/MATCH lepší ako VLOOKUP?

Alebo je to?

Áno, je to - vo väčšine prípadov.

O chvíľu predstavím svoj prípad.

Ale skôr ako to urobím, dovoľte mi povedať toto - VLOOKUP je mimoriadne užitočná funkcia a milujem ju. V Exceli dokáže veľa vecí a sám to každú chvíľu používam. To však neznamená, že nemôže byť nič lepšie a INDEX/MATCH (s väčšou flexibilitou a funkciami) je lepší.

Ak teda chcete vykonať základné vyhľadávanie, je lepšie použiť VLOOKUP.

INDEX/MATCH je VLOOKUP na steroidoch. Akonáhle sa naučíte INDEX/MATCH, môžete ho vždy uprednostniť (najmä kvôli flexibilite, ktorú má).

Bez toho, aby som to príliš natiahol, vám rýchlo uvediem dôvody, prečo je INDEX/MATCH lepší ako VLOOKUP.

INDEX/MATCH sa môže pozerať vľavo (ako aj vpravo) od vyhľadávanej hodnoty

Pokryl som to v jednom z vyššie uvedených príkladov.

Ak máte hodnotu, ktorá je vľavo od vyhľadávanej hodnoty, nemôžete to urobiť pomocou funkcie VLOOKUP

Aspoň nie iba pomocou VLOOKUP.

Áno, môžete skombinovať VLOOKUP s inými vzorcami a urobiť to, ale je to komplikované a chaotické.

INDEX/MATCH je na druhej strane určený na vyhľadávanie kdekoľvek (či už vľavo, vpravo, hore alebo dole)

INDEX/MATCH môže pracovať s vertikálnymi a horizontálnymi rozsahmi

Opäť, pri plnom rešpektovaní VLOOKUP, nie je na to určené.

Koniec koncov, V vo VLOOKUP znamená vertikála.

VLOOKUP môže prechádzať iba údajmi, ktoré sú zvislé, zatiaľ čo INDEX/MATCH môže prechádzať údajmi vertikálne aj horizontálne.

Samozrejme, existuje funkcia HLOOKUP, ktorá sa stará o horizontálne vyhľadávanie, ale nie je to VLOOKUP, potom … však?

Páči sa mi skutočnosť, že kombinácia INDEX MATCH je dostatočne flexibilná na prácu s vertikálnymi aj horizontálnymi údajmi.

VLOOKUP nemôže pracovať so zostupnými údajmi

Pokiaľ ide o približný zápas, VLOOKUP a INDEX/MATCH sú na rovnakej úrovni.

Ale INDEX MATCH má zmysel, pretože dokáže spracovať aj údaje v zostupnom poradí.

Ukazujem to na jednom z príkladov v tomto tutoriále, kde musíme nájsť známku študentov na základe klasifikačnej tabuľky. Ak je tabuľka zoradená zostupne, VLOOKUP by nefungoval (ale INDEX MATCH by fungoval).

INDEX/MATCH môže byť o niečo rýchlejší

Budem pravdivý. Tento test som sám nevykonal.

Spolieham sa na múdrosť excelentného majstra - Charley Kyda.

Rozdiel v rýchlosti vo VLOOKUP a INDEX/MATCH je sotva badateľný, ak máte malé množiny údajov. Ale ak máte tisíce riadkov a veľa stĺpcov, môže to byť rozhodujúci faktor.

Charley Kyd vo svojom článku uvádza:

"V najhoršom prípade je metóda INDEX-MATCH zhruba taká rýchla ako VLOOKUP; v najlepšom prípade je to oveľa rýchlejšie. “

INDEX/MATCH je nezávislý na aktuálnej pozícii stĺpca

Ak máte súbor údajov, ako je uvedené nižšie, pri získavaní skóre Jima vo fyzike, môžete to urobiť pomocou funkcie VLOOKUP.

A aby ste to urobili, môžete vo VLOOKUP zadať číslo stĺpca ako 3.

Všetko je v poriadku.

Ale čo keď vymažem stĺpec Matematika.

V takom prípade sa vzorec VLOOKUP rozbije.

Prečo? - Pretože bolo naprogramované použitie tretieho stĺpca, a keď medzi nimi odstránim stĺpec, tretí stĺpec sa stane druhým stĺpcom.

V tomto prípade je lepšie použiť INDEX/MATCH, pretože pomocou funkcie MATCH môžete číslo stĺpca dynamizovať. Namiesto čísla stĺpca teda skontroluje názov subjektu a pomocou neho vráti číslo stĺpca.

Určite to môžete urobiť kombináciou VLOOKUP s MATCH, ale ak kombinujete aj tak, prečo to neurobiť s INDEX, ktorý je oveľa flexibilnejší.

Keď používate INDEX/MATCH, môžete bezpečne vkladať/odstraňovať stĺpce do svojej množiny údajov.

Napriek všetkým týmto faktorom existuje dôvod, prečo je VLOOKUP tak populárny.

A to je veľký dôvod.

VLOOKUP sa používa jednoduchšie

VLOOKUP trvá maximálne štyri argumenty. Ak si okolo týchto štyroch môžete omotať hlavu, môžete ísť.

A pretože väčšinu základných vyhľadávacích prípadov rieši aj VLOOKUP, rýchlo sa stala najobľúbenejšou funkciou Excelu.

Hovorím tomu funkcie kráľa Excelu.

INDEX/MATCH je na druhej strane o niečo ťažšie používať. Keď to začnete používať, môže vám to vyjsť, ale pre začiatočníka je VLOOKUP oveľa jednoduchšie vysvetliť a naučiť sa ho.

A toto nie je hra s nulovým súčtom.

Ak ste teda vo svete vyhľadávania noví a neviete, ako používať VLOOKUP, lepšie sa to naučte.

Mám podrobného sprievodcu používaním funkcie VLOOKUP v programe Excel (s mnohými príkladmi)

Mojím cieľom v tomto článku nie je postaviť proti sebe dve úžasné funkcie. Chcel som vám ukázať silu kombinácie INDEX MATCH a všetky skvelé veci, ktoré dokáže.

Dúfam, že ste tento článok považovali za užitočný.

Dajte mi vedieť svoje myšlienky v sekcii komentárov a v prípade, že v tomto návode nájdete akúkoľvek chybu, dajte nám vedieť.