Kedy použiť Index-Match namiesto VLOOKUP v programe Excel

2019

Pre tých z vás, ktorí sú v programe Excel dobre známi, ste s najväčšou pravdepodobnosťou veľmi dobre oboznámení s funkciou VLOOKUP . Funkcia VLOOKUP sa používa na vyhľadanie hodnoty v inej bunke založenej na nejakom zodpovedajúcom texte v rámci toho istého riadka.

Ak ste ešte stále funkciu VLOOKUP, môžete si pozrieť môj predchádzajúci príspevok o tom, ako používať VLOOKUP v programe Excel.

Ako mocný, VLOOKUP má obmedzenie na to, ako je potrebné štruktúrovanú porovnávaciu tabuľku, aby fungoval vzorec.

Tento článok vám ukáže obmedzenie, v ktorom nie je možné použiť VLOOKUP a zaviesť inú funkciu v programe Excel s názvom INDEX-MATCH, ktorá môže vyriešiť problém.

INDEX MATCH Príklad Excel

Pomocou nasledujúceho príkladu tabuľky programu Excel máme zoznam majiteľov automobilov a ich názov. V tomto príklade sa budeme snažiť uchopiť ID vozidiel na základe modelu automobilov uvedených pod viacerými majiteľmi, ako je uvedené nižšie:

Na samostatnom hárku s názvom CarType máme jednoduchú databázu automobilov s ID, modelom auta a farbou .

Pomocou tejto nastavenia tabuľky môže funkcia VLOOKUP fungovať len vtedy, ak sa dáta, ktoré chceme načítať, nachádzajú v stĺpci napravo od toho, čo sa snažíme porovnať (pole Model auta ).

Inými slovami, s touto štruktúrou tabuľky, pretože sa jej snažíme nájsť na základe modelu automobilu, jediné informácie, ktoré môžeme získať, je Farba (Nie ID, pretože stĺpec ID je umiestnený naľavo od stĺpca Model auta ).

Je to preto, že pri VLOOKUP sa hodnota vyhľadávania musí objaviť v prvom stĺpci a vyhľadávacie stĺpce musia byť napravo. Žiadna z týchto podmienok nie je splnená v našom príklade.

Dobrou správou je, že INDEX-MATCH nám môže pomôcť pri dosahovaní tohto cieľa. V praxi to v skutočnosti spája dve funkcie programu Excel, ktoré môžu fungovať individuálne: Funkcia INDEX a funkcia MATCH .

Avšak na účely tohto článku budeme hovoriť len o kombinácii dvoch s cieľom replikovať funkciu VLOOKUP .

Vzorec sa zdá byť trochu dlhý a zastrašujúci. Avšak, ak ste ju niekoľkokrát používali, zoznámite sa so syntakticky srdcom.

Toto je úplný vzorec v našom príklade:

 = INDEX (CarType $ A $ 2 :! $ A $ 5 MATCH (B4, CarType $ B $ 2: $ B $ 5, 0))

Tu je rozpis pre každú sekciu

= INDEX ( - "=" označuje začiatok vzorca v bunke a INDEX je prvá časť funkcie programu Excel, ktorú používame.

CarType! $ A $ 2: $ A $ 5 - stĺpce na hárku CarType, kde sú obsiahnuté dáta, ktoré by sme chceli načítať. V tomto príklade je ID každého modelu vozidla.

MATCH ( - Druhá časť funkcie programu Excel, ktorú používame.

B4 - bunka obsahujúca vyhľadávací text, ktorý používame ( model auta ) .

CarType! $ B $ 2: $ B $ 5 - Stĺpce na hárku CarType s údajmi, ktoré použijeme na porovnanie s textom vyhľadávania.

0)) - Označuje, že vyhľadávací text sa musí presne zhodovať s textom v zodpovedajúcom stĺpci (napr. CarType! $ B $ 2: $ B $ 5 ). Ak presná zhoda nie je nájdená, vzorec vráti # N / A.

Poznámka : pamätajte dvojitý uzáver na konci tejto funkcie "))" a čiarky medzi argumentmi.

Osobne som sa presťahoval z VLOOKUP a teraz používam INDEX-MATCH, pretože je schopný robiť viac ako VLOOKUP.

Funkcie INDEX-MATCH majú aj ďalšie výhody v porovnaní s VLOOKUP :

Rýchlejšie výpočty

Keď pracujeme s veľkými množinami údajov, pri ktorých samotný výpočet môže trvať dlhý čas kvôli mnohým funkciám VLOOKUP, zistíte, že akonáhle nahradíte všetky tieto vzorce s indexom INDEX-MATCH, celkový výpočet sa vypočíta rýchlejšie.

Nie je potrebné počítať relatívne stĺpce

Ak má naša referenčná tabuľka kľúčový text, ktorý chceme vyhľadávať v stĺpci C a údaje, ktoré potrebujeme získať, je v stĺpci AQ, budeme musieť vedieť / započítať, koľko stĺpcov je medzi stĺpcom C a stĺpcom AQ pri použití VLOOKUP,

Pomocou funkcií INDEX-MATCH môžeme priamo vybrať indexový stĺpec (tj stĺpec AQ), kde potrebujeme získať údaje a vybrať stĺpec, ktorý sa má zhodovať (tj stĺpec C).