Za one od vas koji su dobro upoznati s Excelom^(Excel) , vjerojatno ste vrlo upoznati s funkcijom VLOOKUP . Funkcija VLOOKUP koristi se za pronalaženje vrijednosti u drugoj ćeliji na temelju nekog podudarnog teksta unutar istog retka.

Ako ste još uvijek novi u funkciji VLOOKUP , možete pogledati moj prethodni post o tome kako koristiti VLOOKUP u Excelu^{(how to use VLOOKUP in Excel)} .

Koliko god moćan, VLOOKUP ima ograničenje na to kako odgovarajuća referentna tablica treba biti strukturirana da bi formula funkcionirala.

Ovaj članak će vam pokazati ograničenje gdje se VLOOKUP ne može koristiti i predstaviti drugu funkciju u Excelu^(Excel) pod nazivom INDEX-MATCH koja može riješiti problem.

INDEX MATCH Primjer Excel

Koristeći sljedeći primjer Excel proračunske tablice , imamo popis imena vlasnika automobila i naziv automobila. U ovom primjeru pokušat ćemo preuzeti  ID automobila^{(Car ID)} na temelju modela automobila^{(Car Model)} navedenog pod više vlasnika kao što je prikazano u nastavku:

Na zasebnom listu pod nazivom CarType imamo jednostavnu bazu podataka automobila s  ID-om^(ID) , modelom automobila^{(Car Model)} i bojom^(Color) .

S ovim postavljanjem tablice,  funkcija VLOOKUP može raditi samo ako se podaci koje želimo dohvatiti nalaze u stupcu desno od onoga što pokušavamo uskladiti ( polje Model automobila ).^{(Car Model )}

Drugim riječima, s ovom strukturom tablice, budući da je pokušavamo uskladiti na temelju modela automobila^{(Car Model)} , jedina informacija koju možemo dobiti je boja^{(Color )} (ne ID jer se stupac ID  nalazi lijevo od  stupca modela automobila^{(Car Model )} .)

To je zato što se kod VLOOKUP -a vrijednost traženja mora pojaviti u prvom stupcu, a stupci pretraživanja moraju biti s desne strane. Niti jedan od tih uvjeta nije ispunjen u našem primjeru.

Dobra vijest je da će nam INDEX-MATCH  moći pomoći u tome. U praksi, ovo je zapravo kombiniranje dvije funkcije programa Excel^(Excel) koje mogu raditi pojedinačno: funkcije INDEX i funkcije ^(INDEX)MATCH .

Međutim, za potrebe ovog članka, govorit ćemo samo o kombinaciji to dvoje s ciljem repliciranja funkcije VLOOKUP -a .

Formula se u početku može činiti malo dugačkom i zastrašujućom. Međutim, nakon što ga upotrijebite nekoliko puta, naučit ćete sintaksu napamet.

Ovo je potpuna formula u našem primjeru:

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

Ovdje je raščlamba za svaki odjeljak

=INDEX( – “=” označava početak formule u ćeliji, a INDEX je prvi dio Excel funkcije koju koristimo.

CarType!$A$2:$A$5 – stupci na listu CarType  u kojima se nalaze podaci koje želimo dohvatiti. U ovom primjeru, ID  svakog modela automobila.^{(Car Model.)}

MATCH( – Drugi dio Excel funkcije koji koristimo.

B4 – Ćelija koja sadrži tekst za pretraživanje koji koristimo ( Model automobila^{(Car Model)} ) .

CarType!$B$2:$B$5 – Stupci na listu CarType   s podacima koje ćemo koristiti za podudaranje s tekstom za pretraživanje.

0)) – Za označavanje da se tekst za pretraživanje mora točno podudarati s tekstom u odgovarajućem stupcu (tj CarType!$B$2:$B$5 ). Ako se ne pronađe točno podudaranje, formula vraća #N/A .

Napomena: zapamtite dvostruku zagradu za zatvaranje na kraju ove funkcije “))” i zareze između argumenata.^{(Note: remember the double closing bracket at the end of this function “))” and the commas between the arguments.)}

Osobno sam se odmaknuo od VLOOKUP -a i sada koristim INDEX-MATCH jer je sposoban učiniti više od VLOOKUP -a .

Funkcije INDEX-MATCH također imaju druge prednosti u usporedbi s VLOOKUP :

1. Brži izračuni^{(Faster Calculations)}

Kada radimo s velikim skupovima podataka u kojima sam izračun može potrajati dugo zbog mnogih funkcija VLOOKUP -a, otkrit ćete da će se ukupni izračun brže izračunati nakon što zamijenite sve te formule s INDEX-MATCH .

1. Nema potrebe za brojanjem relativnih stupaca^{(No Need to Count Relative Columns)}

Ako naša referentna tablica ima ključni tekst koji želimo tražiti u stupcu C , a podaci koje trebamo dobiti nalaze se u stupcu AQ , morat ćemo znati/prebrojati koliko je stupaca između stupca C i stupca AQ kada koristimo VLOOKUP .

Pomoću  funkcija INDEX-MATCH možemo izravno odabrati indeksni stupac (tj. stupac AQ) gdje trebamo dobiti podatke i odabrati stupac koji će se podudarati (tj. stupac C).

1. Izgleda kompliciranije^{(It Looks More Complicated)}

VLOOKUP je danas prilično uobičajen, ali malo tko zna za zajedničko korištenje funkcija INDEX-MATCH.

Duži niz u funkciji INDEX-MATCH pomaže da izgledate kao stručnjak u rukovanju složenim i naprednim Excel funkcijama. Uživati!

When to Use Index-Match Instead of VLOOKUP in Excel

For those of yoυ who are well-verѕed in Excel, you are most likely very famіliar 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

• Kako ispraviti pogreške #N/A u Excelovim formulama kao što je VLOOKUP

• Kako izbrisati prazne retke u Excelu

• Kako koristiti Excelovu značajku govornih ćelija

• Kako umetnuti Excel radni list u Word dokument

• Kako koristiti Excelovu analizu "što ako".

• Brzo izgovorite brojeve u Wordu i Excelu

• 10 Excel savjeta i trikova za 2019

• Kako napisati IF formulu/izjavu u Excelu

• Kako popraviti redak u Excelu

• Kako premjestiti stupce u Excelu

• Kako sortirati po datumu u Excelu

• Kako pretraživati ​​u Excelu

• 2 načina korištenja Excelove funkcije transponiranja

• Centrirajte svoje podatke radnog lista u Excelu za ispis

• Povezivanje Excela s MySQL

• Grupirajte retke i stupce u Excel radnom listu

• Što je VBA polje u Excelu i kako ga programirati

• Osnovno sortiranje podataka u jednom i više stupaca u Excel proračunskim tablicama

• Kako umetnuti CSV ili TSV u Excel radni list

• Kako napraviti tortni grafikon u Excelu