Кога да използваме Index-Match вместо VLOOKUP в Excel

2019

За тези от вас, които са добре запознати с Excel, най-вероятно сте много запознат с функцията VLOOKUP . Функцията VLOOKUP се използва за намиране на стойност в различна клетка на базата на съвпадащ текст в същия ред.

Ако все още сте нов в VLOOKUP функцията, можете да проверите предишния ми пост за това как да използвате VLOOKUP в Excel.

Колкото и мощно да е, VLOOKUP има ограничение за това как трябва да бъде структурирана съответната таблица с референции, за да може формулата да работи.

Тази статия ще ви покаже ограничението, където VLOOKUP не може да се използва и въвеждат друга функция в Excel, наречена INDEX-MATCH, която може да реши проблема.

INDEX MATCH Пример за Excel

Използвайки следната примерна електронна таблица на Excel, имаме списък с имената на собствениците на автомобили и името на автомобила. В този пример ще се опитваме да вземем идентификационния номер на автомобила въз основа на модела на автомобил, посочен в няколко собственика, както е показано по-долу:

На отделен лист, наречен CarType, имаме проста база данни за автомобили с ID, модел на автомобил и цвят .

С тази настройка на таблицата функцията VLOOKUP може да работи само ако данните, които искаме да извлечем, се намира в колоната вдясно от това, което се опитваме да съпоставим (поле Модел на кола ).

С други думи, с тази структура на таблицата, тъй като се опитваме да я съпоставим с модела на автомобила, единствената информация, която можем да получим, е „ Цвят“ (не се идентифицира, тъй като ID колоната се намира вляво от колоната Модел на кола ).

Това е така, защото с VLOOKUP, стойността за търсене трябва да се появи в първата колона и колоните за търсене трябва да са надясно. Нито едно от тези условия не е изпълнено в нашия пример.

Добрата новина е, че INDEX-MATCH ще може да ни помогне да постигнем това. На практика това всъщност съчетава две функции на Excel, които могат да работят индивидуално: INDEX функция и MATCH функция.

За целта на тази статия обаче ще говорим само за комбинацията от двете с цел да се възпроизведе функцията на VLOOKUP .

Първоначално формулата може да изглежда малко по-дълга. Но след като веднъж сте го използвали няколко пъти, ще научите синтаксиса наизуст.

Това е пълната формула в нашия пример:

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

Ето разбивка за всяка секция

= INDEX ( - "=" показва началото на формулата в клетката и INDEX е първата част от функцията на Excel, която използваме.

$ A $ 2: $ A $ 5 - колоните в листа CarType, където се съдържат данните, които бихме искали да извлечем. В този пример ID на всеки модел автомобил.

MATCH ( - Втората част на функцията Excel, която използваме.

B4 - Клетката, която съдържа текст за търсене, който използваме ( Модел на кола ) .

CarType! $ B $ 2: $ B $ 5 - Колоните на листа CarType с данните, които ще използваме, за да съответстваме на текста за търсене.

0)) - За да укажете, че текстът за търсене трябва да съвпада точно с текста в съответната колона (т.е. CarType! $ B $ 2: $ B $ 5 ). Ако точното съвпадение не е намерено, формулата връща # N / A.

Забележка : запомнете двойната затваряща скоба в края на тази функция “)) и запетаите между аргументите.

Лично аз съм се преместил от VLOOKUP и сега използвайте INDEX-MATCH, тъй като е способен да прави повече от VLOOKUP.

Функциите INDEX-MATCH имат и други предимства в сравнение с VLOOKUP :

По-бързи изчисления

Когато работим с големи масиви от данни, в които самото изчисление може да отнеме много време поради много функции на VLOOKUP, ще откриете, че след като замените всички тези формули с INDEX-MATCH, общото изчисление ще се изчисли по-бързо.

Няма нужда да преброявате относителните колони

Ако нашата референтна таблица съдържа ключовия текст, който искаме да търсим в колона С и данните, които трябва да получим, е в колона AQ, ще трябва да знаем / преброим колко колони са между колона С и колона AQ, когато използвате VLOOKUP,

С функциите INDEX-MATCH можем директно да изберем индексната колона (т.е. колона AQ), където трябва да получим данните и да изберем съответната колона (т.е. колона C).