Pre tých z vás, ktorí sa dobre orientujú v Exceli^(Excel) , pravdepodobne veľmi dobre poznáte funkciu VLOOKUP . Funkcia VLOOKUP sa používa na nájdenie hodnoty v inej bunke na základe zhodného textu v rovnakom riadku.

Ak ste ešte stále novým používateľom funkcie VLOOKUP , môžete si pozrieť môj predchádzajúci príspevok o tom, ako používať funkciu VLOOKUP v Exceli^{(how to use VLOOKUP in Excel)} .

Akokoľvek je funkcia VLOOKUP^(VLOOKUP) výkonná, má obmedzenia týkajúce sa toho, ako musí byť porovnávacia referenčná tabuľka štruktúrovaná, aby vzorec fungoval.

Tento článok vám ukáže obmedzenie, pri ktorom nie je možné použiť funkciu VLOOKUP , a predstaví ďalšiu funkciu v ^(VLOOKUP)Exceli^(Excel) s názvom INDEX-MATCH , ktorá môže problém vyriešiť.

INDEX MATCH Príklad programu Excel

Pomocou nasledujúceho príkladu tabuľky programu Excel máme zoznam mien vlastníkov áut a názvu auta. V tomto príklade sa pokúsime získať  ID auta^{(Car ID)} na základe modelu auta^{(Car Model)} uvedeného pod viacerými vlastníkmi, ako je uvedené nižšie:

Na samostatnom hárku s názvom CarType máme jednoduchú databázu áut s  ID , modelom auta^{(Car Model)} a farbou^(Color) .

S týmto nastavením tabuľky  môže funkcia VLOOKUP fungovať iba vtedy, ak sa údaje, ktoré chceme získať, nachádzajú v stĺpci napravo od toho, čo sa snažíme porovnať ( pole Model auta ).^{(Car Model )}

Inými slovami, s touto štruktúrou tabuľky, keďže sa ju snažíme priradiť na základe modelu auta^{(Car Model)} , jedinou informáciou, ktorú môžeme získať, je farba^{(Color )} (nie ID , pretože stĺpec ID  sa nachádza naľavo od  stĺpca Model auta^{(Car Model )} .)

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

Dobrou správou je, že INDEX-MATCH  nám to pomôže dosiahnuť. V praxi ide vlastne o spojenie dvoch funkcií Excelu^(Excel) , ktoré môžu fungovať samostatne: funkcia INDEX a funkcia ^(INDEX)MATCH .

Pre účely tohto článku sa však budeme baviť iba o kombinácii oboch s cieľom replikovať funkciu VLOOKUP .

Vzorec sa môže na prvý pohľad zdať trochu dlhý a zastrašujúci. Keď ho však použijete niekoľkokrát, syntax sa naučíte naspamäť.

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 Excelu^(Excel) , ktorú používame.

CarType!$A$2:$A$5 – stĺpce na hárku CarType  , v ktorých sa nachádzajú údaje, ktoré by sme chceli získať. V tomto príklade ide o ID  každého modelu auta.^{(Car Model.)}

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

B4 – Bunka, ktorá obsahuje hľadaný text, ktorý používame ( Model auta^{(Car Model)} ) .

CarType!$B$2:$B$5 – Stĺpce na hárku CarType   s údajmi, ktoré použijeme na porovnanie s hľadaným textom.

0)) – Označuje, že hľadaný text sa musí presne zhodovať s textom v zodpovedajúcom stĺpci (napr CarType!$B$2:$B$5 ). Ak sa nenájde presná zhoda, vzorec vráti #N/A .

Poznámka: zapamätajte si dvojitú uzatváraciu zátvorku na konci tejto funkcie „))“ a čiarky medzi argumentmi.^{(Note: remember the double closing bracket at the end of this function “))” and the commas between the arguments.)}

Osobne som sa vzdialil od VLOOKUP a teraz používam INDEX-MATCH, pretože dokáže viac ako VLOOKUP .

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

1. Rýchlejšie výpočty^{(Faster Calculations)}

Keď pracujeme s veľkými množinami údajov, kde samotný výpočet môže trvať dlho kvôli mnohým funkciám VLOOKUP , zistíte, že keď všetky tieto vzorce nahradíte INDEX-MATCH , celkový výpočet sa bude počítať rýchlejšie.

1. Nie je potrebné počítať relatívne stĺpce^{(No Need to Count Relative Columns)}

Ak naša referenčná tabuľka obsahuje kľúčový text, ktorý chceme vyhľadať v stĺpci C , a údaje, ktoré potrebujeme získať, sú v stĺpci AQ , pri používaní ^{( AQ)}funkcie VLOOKUP^(VLOOKUP) budeme musieť vedieť/spočítať, koľko stĺpcov je medzi stĺpcom C a stĺpcom AQ. .

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

1. Vyzerá to komplikovanejšie^{(It Looks More Complicated)}

VLOOKUP je v dnešnej dobe celkom bežný, ale málokto vie o spoločnom používaní funkcií INDEX-MATCH.

Dlhší reťazec vo funkcii INDEX-MATCH pomáha, aby ste vyzerali ako expert na zvládanie zložitých a pokročilých funkcií Excelu^(Excel) . Užite si to!

When to Use Index-Match Instead of VLOOKUP in Excel

For thoѕe of yоu who are well-versed in Excel, you are most likely very familiаr with the VLOOKUP function. The VLOOKUP function is used to find a value in a different cell based on some matching text within the same row.

If you are still new to VLOOKUP function, you can check out my previous post on how to use VLOOKUP in Excel.

As powerful as it is, VLOOKUP has a limitation on how the matching reference table needs to be structured in order for the formula to work.

This article will show you the limitation where VLOOKUP cannot be used and introduce another function in Excel called INDEX-MATCH that can solve the issue.

INDEX MATCH Excel Example

Using the following example Excel spreadsheet, we have a list of car owners name and the car name. In this example, we will be trying to grab the Car ID based on the Car Model listed under multiple owners as shown below:

On a separate sheet called CarType, we have a simple car database with the ID, Car Model and Color.

With this table setup, the VLOOKUP function can only work if the data that we want to retrieve is located on the column to the right of what we are trying to match (Car Model field).

In other words, with this table structure, since we are trying to match it based on the Car Model, the only information that we can get is Color (Not ID as the ID column is located to the left of the Car Model column.)

This is because with VLOOKUP, the lookup value must appear in the first column and the lookup columns have to be to the right. None of those conditions are met in our example.

The good news is, INDEX-MATCH will be able to help us in achieving this. In practice, this is actually combining two Excel functions that can work individually: INDEX function and MATCH function.

However, for the purpose of this article, we will only talk about the combination of the two with the aim of replicating the function of VLOOKUP.

The formula can seem to be a little bit long and intimidating at first. However, once you have used it several times, you will learn the syntax by heart.

This is the full formula in our example:

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

Here is the breakdown for each section

=INDEX( – The “=” indicates the beginning of formula in the cell and INDEX is the first part of the Excel function that we are using.

CarType!$A$2:$A$5 – the columns on sheet CarType where the data we would like to retrieve is contained. In this example, the ID of each Car Model.

MATCH( – The second part of the Excel function that we are using.

B4 – The cell that contain search text that we are using (Car Model).

CarType!$B$2:$B$5 – The columns on sheet CarType  with the data which we will use to match against the search text.

0)) – To indicate that the search text has to exactly match with the text in the matching column (i.e. CarType!$B$2:$B$5). If the exact match is not found, the formula returns #N/A.

Note: remember the double closing bracket at the end of this function “))” and the commas between the arguments.

Personally I have moved away from VLOOKUP and now use INDEX-MATCH as it is capable of doing more than VLOOKUP.

The INDEX-MATCH functions also have other benefits as compared to VLOOKUP:

1. Faster Calculations

When we are working with large datasets where the calculation itself can take a long time due to many VLOOKUP functions, you will find that once you replace all of those formulas with INDEX-MATCH, the overall calculation will be compute faster.

1. No Need to Count Relative Columns

If our reference table is having the key text that we want to search in column C and the data that we need to get is in column AQ, we will need to know/count how many columns are between column C and column AQ when using VLOOKUP.

With the INDEX-MATCH functions, we can directly select the index column (i.e column AQ) where we need to get the data and select the column to be matched (i.e. column C).

1. It Looks More Complicated

VLOOKUP is quite common nowadays, but not many know about using the INDEX-MATCH functions together.

The longer string in INDEX-MATCH function help to make you look like an expert in handling complex and advanced Excel functions. Enjoy!

Related posts

• Ako opraviť chyby #N/A vo vzorcoch programu Excel, ako je VLOOKUP

• Ako odstrániť prázdne riadky v Exceli

• Ako používať funkciu Speak Cells v Exceli

• Ako vložiť pracovný hárok programu Excel do dokumentu programu Word

• Ako používať analýzu What-If v Exceli

• Ako triediť podľa dátumu v Exceli

• Prečo by ste mali používať pomenované rozsahy v Exceli

• Základné jednostĺpcové a viacstĺpcové triedenie údajov v tabuľkách Excel

• Tabuľky Google vs Microsoft Excel – Aké sú rozdiely?

• 3 spôsoby, ako rozdeliť bunku v Exceli

• Vycentrujte údaje pracovného hárka v Exceli na tlač

• Pokročilá príručka VBA pre MS Excel

• Ako presúvať stĺpce v Exceli

• 4 spôsoby použitia značky začiarknutia v Exceli

• Ako používať príkazy If a vnorené If v Exceli

• Ako vytvoriť jednoduchú kontingenčnú tabuľku v Exceli

• Ako zdieľať súbor Excel pre jednoduchú spoluprácu

• Ako vložiť CSV alebo TSV do pracovného hárka programu Excel

• Ako odstrániť duplicitné riadky v Exceli

• Pri kopírovaní vzorca v Exceli zachovajte odkazy na bunky