VLOOKUP с MATCH | Създайте гъвкава формула с VLOOKUP MATCH

Формулата Vlookup работи само когато масивът на таблицата във формулата не се променя, но ако в таблицата е вмъкната нова колона или колона е изтрита, формулата дава неправилен резултат или отразява грешка, за да направи формулата без грешки в такива динамични ситуации използваме функцията за съвпадение, за да съчетаем действително индекса на данните и да върнем действителния резултат.

Комбинирайте VLOOKUP с Match

Формулата vlookup е най-често използваната функция, която се използва за търсене и връщане на една и съща стойност в посочения индекс на колона или стойността от различен индекс на колона с препратка към съответстващата стойност от първата колона. Основното предизвикателство, пред което е изправено при използване на vlookup, е, че индексът на колоната, който трябва да бъде посочен, е статичен и няма динамична функционалност. Особено когато работите по множество критерии, което изисква ръчно да промените индекса на референтната колона. По този начин тази необходимост е удовлетворена с помощта на формулата „СЪВПАДАНЕ“, за да имате по-добро сцепление или контрол на често променящия се индекс на колона във формулата VLOOKUP.

VLookup и формула за съвпадение

# 1 - Формула VLOOKUP

Формулата на функцията VLOOKUP в Excel

Тук всички аргументи, които трябва да се въведат, са задължителни.

  • Lookup_value - Тук трябва да се въведе референтна клетка или текст с двойни кавички, които да бъдат идентифицирани в диапазона на колоните.
  • Табличен масив -   Този аргумент изисква да се въведе диапазонът на таблицата, където трябва да се търси Lookup_value и данните, които трябва да бъдат извлечени, се намират в конкретния диапазон на колони.
  • Col_index_num - В този аргумент трябва да се въведе индексният номер на колоната или броят на колоната от референтната първа колона, от която трябва да се изтегли съответната стойност от същата позиция като стойността, търсена в първата колона.
  • [Range_lookup] - Този аргумент ще даде две опции.
  • TRUE - Приблизително съвпадение: - Аргументът може да бъде въведен като TRUE или числово „1“, което връща приблизителното съвпадение, съответстващо на референтната колона или първата колона. Освен това стойностите в първата колона на масива на таблицата трябва да бъдат сортирани във възходящ ред.
  • FALSE - Точно съвпадение: - Тук аргументът, който трябва да се въведе, може да бъде FALSE или числово „0“. Тази опция ще върне само точното съвпадение на стойността, съответстваща на идентифицирането, от позицията в обхвата на първата колона. Ако не се търси стойността от първата колона, ще се появи съобщение за грешка „# N / A“.

# 2 - Формула на мача

Функцията Match връща позицията на клетката на стойността, въведена за дадения масив от таблица.

Всички аргументи в синтаксиса са задължителни.

  • Lookup_value - Тук въведеният аргумент може да бъде или препратка към клетката на стойността, или текстов низ с двойни кавички, чиято позиция на клетката трябва да бъде изтеглена.
  • Lookup_array - трябва да се въведе диапазонът на масива за таблицата, чиято стойност или съдържание на клетка е желателно да бъде идентифицирано.
  • [тип съвпадение] - Този аргумент предоставя три опции, както е обяснено по-долу.
  • „1-по-малко от“ - Тук аргументът, който трябва да се въведе, е цифров „1“, който ще върне стойността, която е по-малка или равна на справочната стойност. А също така масивът за търсене трябва да бъде сортиран във възходящ ред.
  • „0-точно съвпадение“ - Тук аргументът, който трябва да се въведе, трябва да бъде цифров „0“. Тази опция ще върне точното положение на съответната стойност за търсене. Масивът за търсене обаче може да бъде във всякакъв ред.
  • „-1-по-голямо от“ -  Аргументът, който трябва да се въведе, трябва да бъде цифров „-1“. Третата опция намира най-малката стойност, която е по-голяма или равна на справочната стойност. Тук поръчката за търсещия масив трябва да бъде поставена в низходящ ред.

# 3 - VLOOKUP с формула MATCH

= VLOOKUP (lookup_value, масив_таблица, MATCH (lookup_value, lookup_array, [match_type]), [обхват търсене])

Как да използвам VLOOKUP с формула за съвпадение в Excel?

Примерът по-долу ще ви помогне да разберете функционирането на формулата vlookup и match при комбинирането.

Можете да изтеглите този VLookup с Match Excel Template тук - VLookup с Match Excel Template

Обмислете таблицата с данни по-долу, която описва спецификациите на дадено превозно средство, което ще бъде закупено.

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

Стъпка # 1 - Нека приложим формулата vlookup на индивидуално ниво, за да стигнем до резултата.

Резултатът е показан по-долу:

Тук стойността за справка е посочена към $ B9, която е модел „E“, а масивът за търсене е даден като обхват на таблицата с данни с абсолютна стойност „$“, индексът на колоната е посочен към колона „4“, което е броят за колона „Тип“ и търсенето на диапазон получава точно съвпадение.

По този начин се прилага следната формула за връщане на стойността за колона „Гориво“.

Резултатът е показан по-долу:

Тук справочната стойност с абсолютен низ „$“, приложен за справочна стойност и lookup_array помага да се поправи референтната клетка, дори ако формулата се копира в друга клетка. В колоната „Гориво“ трябва да променим индекса на колоната на „5“, тъй като стойността, от която са необходими данните за извличане, се променя.

Стъпка 2 -  Сега нека приложим формулата Match, за да извлечем позицията за дадената справочна стойност.

Резултатът е показан по-долу:

Както се вижда на горната екранна снимка, тук се опитваме да извлечем позицията на колоната от масива на таблицата. В този случай номерът на колоната, който трябва да бъде изтеглен, се нарича клетка C8, която е колона „Тип“, а диапазонът на търсене, който трябва да се търси, е даден като диапазон на заглавките на колоните, а типът на съвпадение има точно съвпадение, като „ 0 ”.

По този начин таблицата по-долу ще даде желания резултат за позиции на колона „Гориво“.

Сега тук колоната за търсене е дадена на клетка D8 и желаният индекс на колона се връща на „5“.

Стъпка # 3 - Сега формулата Match ще се използва във функцията vlookup, за да получи стойността от идентифицираната позиция на колоната.

Резултатът е показан по-долу:

В горната формула функцията за съвпадение се поставя на мястото на параметъра за индекс на колоната на функцията vlookup. Тук функцията за съвпадение ще идентифицира референтната клетка за справочна стойност “C8” и ще върне номера на колоната през дадения масив на таблицата. Тази позиция на колоната ще служи за целта като вход за аргумента за индекс на колона във функцията vlookup. Кое от своя страна ще помогне на vlookup да идентифицира стойността, която трябва да се върне от резултантния номер на индекса на колоната?

По същия начин сме приложили vlookup с формула за съвпадение и за колоната „Гориво“.

Резултатът е показан по-долу:

По този начин можем да приложим тази комбинация функция и за други колони „Тип“ и „Гориво“.

Неща за запомняне

  • VLOOKUP може да се приложи към справочни стойности само в най-предната му лява страна. Всички стойности, които се търсят от дясната страна на таблицата с данни, ще върнат стойността за грешка „# N / A“.
  • Обхватът на масива_на_маси, въведен във втория аргумент, трябва да бъде абсолютна препратка към клетка "$", това ще поддържа фиксирания обхват на масива на таблицата при прилагане на формулата за търсене към други клетки, иначе референтните клетки за обхвата на масива на таблицата ще се преместят към следващата справка.
  • Стойността, въведена в справочната стойност, не трябва да бъде по-малка от най-малката стойност в първата колона на масива на таблицата, иначе функцията ще върне стойността за грешка „# N / A“.
  • Преди да приложите приблизително съвпадение „TRUE“ или „1“ в последния аргумент, винаги не забравяйте да сортирате масива на таблицата във възходящ ред.
  • Функцията за съвпадение връща само позицията на стойността в масива на таблицата vlookup и не връща стойността.
  • В случай, че функцията за съвпадение не е в състояние да идентифицира позицията на справочната стойност в масива на таблицата, тогава формулата връща „# N / A“ в стойността на грешката.
  • Функциите Vlookup и Match съвпадат с малки и малки букви при съвпадение на справочната стойност със съответстващата текстова стойност в масива на таблицата.