За оне од вас који су добро упућени у Екцел^(Excel) , највероватније сте веома упознати са функцијом ВЛООКУП^(VLOOKUP) . Функција ВЛООКУП^(VLOOKUP) се користи за проналажење вредности у другој ћелији на основу неког подударног текста у истом реду.

Ако сте још увек нови у функцији ВЛООКУП^(VLOOKUP) , можете погледати мој претходни пост о томе како да користите ВЛООКУП у Екцел-у^{(how to use VLOOKUP in Excel)} .

Колико год моћан, ВЛООКУП^(VLOOKUP) има ограничење на то како одговарајућа референтна табела треба да буде структурирана да би формула функционисала.

Овај чланак ће вам показати ограничење где се ВЛООКУП^(VLOOKUP) не може користити и увести другу функцију у Екцел^(Excel) -у под називом ИНДЕКС-МАТЦХ^{(INDEX-MATCH)} која може да реши проблем.

ИНДЕКС УПОТРЕБА Екцел Пример

Користећи следећи пример Екцел табеле , имамо листу имена власника аутомобила и имена аутомобила. У овом примеру, покушаћемо да преузмемо  ИД аутомобила^{(Car ID)} на основу модела аутомобила^{(Car Model)} наведеног под више власника као што је приказано у наставку:

На посебном листу под називом ЦарТипе^(CarType) , имамо једноставну базу података аутомобила са  ИД^(ID) -ом , моделом аутомобила^{(Car Model)} и бојом^(Color) .

Са овим подешавањем табеле,  функција ВЛООКУП^(VLOOKUP) може да ради само ако се подаци које желимо да преузмемо налазе у колони десно од онога што покушавамо да ускладимо ( поље Модел аутомобила^{(Car Model )} ).

Другим речима, са овом структуром табеле, пошто покушавамо да је ускладимо на основу модела аутомобила^{(Car Model)} , једина информација коју можемо да добијемо је боја^{(Color )} (не ИД^(ID) јер се колона ИД^(ID)  налази лево од  колоне модела аутомобила^{(Car Model )} .)

То је зато што са ВЛООКУП-ом^(VLOOKUP) , вредност тражења мора да се појави у првој колони, а колоне за претрагу морају бити десно. Ниједан од тих услова није испуњен у нашем примеру.

Добра вест је да ће ИНДЕКС-МАТЦХ^{(INDEX-MATCH)}  моћи да нам помогне у томе. У пракси, ово је заправо комбиновање две Екцел^(Excel) функције које могу да раде појединачно: функције ИНДЕКС и функције ^(INDEX)МАТЦХ^(MATCH) .

Међутим, за потребе овог чланка, говорићемо само о комбинацији ова два са циљем реплицирања функције ВЛООКУП-а^(VLOOKUP) .

Формула у почетку може изгледати мало дугачка и застрашујућа. Међутим, када га једном употребите неколико пута, научићете синтаксу напамет.

Ово је пуна формула у нашем примеру:

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

Ево поделе за сваки одељак

=INDEX( – “=” означава почетак формуле у ћелији, а ИНДЕКС^(INDEX) је први део Екцел^(Excel) функције коју користимо.

CarType!$A$2:$A$5 – колоне на листу ЦарТипе^(CarType)  у којима се налазе подаци које желимо да преузмемо. У овом примеру, ИД^(ID)  сваког модела аутомобила.^{(Car Model.)}

МАТЦХ(^{(MATCH( )} – Други део Екцел^(Excel) функције који користимо.

Б4^(B4) – Ћелија која садржи текст за претрагу који користимо ( модел аутомобила^{(Car Model)} ) .

CarType!$B$2:$B$5 – Колоне на листу ЦарТипе^(CarType)   са подацима које ћемо користити за подударање са текстом за претрагу.

0)) – Да би се назначило да се текст за претрагу мора тачно поклапати са текстом у одговарајућој колони (тј CarType!$B$2:$B$5 ). Ако није пронађено тачно подударање, формула враћа #N/A .

Напомена: запамтите двоструку заграду за затварање на крају ове функције “))” и зарезе између аргумената.^{(Note: remember the double closing bracket at the end of this function “))” and the commas between the arguments.)}

Лично сам се удаљио од ВЛООКУП-^(VLOOKUP) а и сада користим ИНДЕКС-МАТЦХ јер је способан да уради више од ВЛООКУП-а^(VLOOKUP) .

Функције ИНДЕКС-МАТЦХ^{(INDEX-MATCH)} такође имају друге предности у поређењу са ВЛООКУП-ом^(VLOOKUP) :

1. Брже калкулације^{(Faster Calculations)}

Када радимо са великим скуповима података где само израчунавање може да потраје дуго због многих функција ВЛООКУП-^(VLOOKUP) а, видећете да када све те формуле замените са ИНДЕКС-МАТЦХ^{(INDEX-MATCH)} , укупна калкулација ће бити брже израчуната.

1. Нема потребе за бројањем релативних колона^{(No Need to Count Relative Columns)}

Ако наша референтна табела има кључни текст који желимо да претражимо у колони Ц^(C) , а подаци које треба да добијемо су у колони АК^{( AQ)} , мораћемо да знамо/пребројимо колико колона има између колоне Ц и колоне АК када користимо ВЛООКУП^(VLOOKUP) .

Помоћу функција  ИНДЕКС-МАТЦХ^{(INDEX-MATCH)} можемо директно изабрати колону индекса (тј. колону АК) где треба да добијемо податке и изаберемо колону за подударање (тј. колону Ц).

1. Изгледа компликованије^{(It Looks More Complicated)}

ВЛООКУП^(VLOOKUP) је прилично уобичајен данас, али мало њих зна за заједничко коришћење функција ИНДЕКС-МАТЦХ.

Дужи низ у функцији ИНДЕКС-МАТЦХ^{(INDEX-MATCH)} помаже да изгледате као стручњак за руковање сложеним и напредним Екцел^(Excel) функцијама. Уживати!

When to Use Index-Match Instead of VLOOKUP in Excel

For those of you who are wеll-versed in Excel, you are most likely very familiar 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

• Како да поправите #Н/А грешке у Екцел формулама као што је ВЛООКУП

• Како избрисати празне линије у Екцелу

• Како користити Екцел-ову функцију говорних ћелија

• Како уметнути Екцел радни лист у Ворд документ

• Како користити Екцел-ову анализу шта ако

• Како користити Фласх Филл Ин Екцел

• Како уклонити дупле редове у Екцелу

• Како премештати колоне у Екцелу

• Како пратити зависне особе у Екцелу

• Како сортирати по датуму у Екцелу

• 10 Екцел савета и трикова за 2019

• Како спојити ћелије, колоне и редове у Екцел-у

• Како спојити податке у више Екцел датотека

• Како користити ПМТ функцију у Екцелу

• Сачувајте референце ћелија када копирате формулу у Екцел-у

• Напредни ВБА водич за МС Екцел

• 4 начина да претворите Екцел у Гоогле табеле

• Групирајте редове и колоне у Екцел радном листу

• Како користити апсолутне референце у Екцел-у

• Како брзо уметнути више редова у Екцел