В тази статия ще научим как да използваме функцията INDEX и MATCH за търсене на стойност в Excel.
INDEX & MATCH функция за замяна на vlookup функцията
Обикновено използваме функцията VLOOKUP, за да намерим стойност, познавайки индекса на реда и номера на колоната. За функцията VLOOKUP трябва да имаме критериите за съвпадение на номера на колоната и реда и най -важното, стойността, която да разберем, е отдясно на съответстващата стойност. Но данните не винаги подкрепят това. Много пъти трябва просто да търсим стойности, без да знаем индекса на реда или колоната, а просто да имаме стойности, които да съвпадат. Например: намиране на заплатата на служител с идентификационен номер на служител. В това ще търсим идентификационен номер на служител за индекс на ред и заплата за индекс на колона. Нека научим как можем да направим това търсене с помощта на INDEX & MATCH.
Как да решим проблема?
За да разберем първо формулата, трябва да преразгледаме малко следните функции
- INDEX функция
- MATCH функция
Сега ще направим формула, използвайки горните функции. Функцията за съвпадение ще върне индекса на стойността за търсене1 в полето за заглавие на ред. И друга функция MATCH ще върне индекса на стойността за търсене 2 от полето за заглавие на колона. Номерата на индекса сега ще бъдат въведени във функцията INDEX, за да получат стойностите под стойността за търсене от данните в таблицата.
Обща формула:
| = INDEX (данни, MATCH (lookup_value1, row_headers, 0, MATCH (lookup_value2, column_headers, 0))) |
данни : масив от стойности в таблицата без заглавки
lookup_value1 : стойност за търсене в ред_заглавие.
row_headers : Масив от индекс на редове за търсене.
lookup_value1 : стойност за търсене в колоната_заглавие.
колони_заглавки : колона Индексен масив за търсене.
Пример:
Горните твърдения могат да бъдат трудни за разбиране. Така че нека разберем това, като използваме формулата в пример
Тук имаме списък с резултати, получени от учениците с техния списък с теми. Трябва да намерим резултата за конкретен студент (Гари) и предмет (социални изследвания), както е показано на снимката по -долу.
Стойността Student1 трябва да съвпада с масив Row_header и Subject value2 трябва да съответства на масива Column_header.
Използвайте формулата в клетката J6:
| = INDEX (таблица, MATCH (J5, ред, 0, MATCH (J4, колона, 0))) |
Обяснение:
- Функцията MATCH съвпада със стойността на Student в клетката J4 с масива на заглавката на реда и връща нейната позиция 3 като число.
- Функцията MATCH съвпада със стойността на Subject в клетката J5 с масива на заглавката на колоната и връща нейната позиция 4 като число.
- Функцията INDEX взема индексния номер на ред и колона и търси в табличните данни и връща съответстващата стойност.
- Аргументът тип MATCH е фиксиран на 0. Тъй като формулата ще извлече точното съвпадение.
Тук стойностите на формулата са дадени като препратки към клетки и ред_заглавие, таблица и колона_заглавие, дадени като именувани диапазони.
Както можете да видите в горната снимка, получихме резултата, получен от студента Гари в Предмет Социални проучвания като 36. И това доказва, че формулата работи добре и за съмнения вижте бележките по -долу за разбиране.
Сега ще използваме приблизителното съвпадение с заглавки на редове и заглавки на колони като числа. Приблизителното съвпадение приема само числовите стойности, тъй като няма начин да се приложи към текстовите стойности
Тук имаме цена на стойности според височината и ширината на продукта. Трябва да намерим Цената за конкретна височина (34) и ширина (21), както е показано на снимката по -долу.
Стойността Height1 трябва да съвпада с масива Row_header, а стойността Width2 трябва да съвпада с масива Column_header.
Използвайте формулата в клетката K6:
| = INDEX (данни, MATCH (K4, Height, 1, MATCH (K5, Width, 1))) |
Обяснение:
- Функцията MATCH съвпада със стойността на Height в клетката K4 с масива на заглавката на реда и връща нейната позиция 3 като число.
- Функцията MATCH съвпада със стойността на Width в клетка K5 с масива на заглавката на колоната и връща нейната позиция 2 като число.
- Функцията INDEX взема индексния номер на ред и колона и търси в табличните данни и връща съответстващата стойност.
- Аргументът тип MATCH е фиксиран на 1. Тъй като формулата ще извлече приблизителното съвпадение.
Тук стойностите на формулата са дадени като препратки към клетки и ред_заглавие, данни и колона_заглавие, дадени като именувани диапазони, както е споменато в моментната снимка по -горе.
Можете също така да извършите търсене на точни съвпадения, като използвате функциите INDEX и MATCH в Excel. Научете повече за това как да извършите търсене с чувствителност към регистъра, като използвате функцията INDEX & MATCH в Excel. Можете също да потърсите частичните съвпадения, като използвате заместващи знаци в Excel.
Както можете да видите в горната снимка, получихме Цената, получена по височина (34) & Ширина (21) като 53.10. И това доказва, че формулата работи добре и за съмнения вижте по -долу бележките за повече разбиране.
Бележки:
- Функцията връща грешката #NA, ако аргументът на масива за търсене към функцията MATCH е 2D масив, който е полето на заглавката на данните …
- Функцията съвпада с точната стойност, тъй като аргументът тип съвпадение на функцията MATCH е 0.
- Стойностите за търсене могат да бъдат дадени като препратка към клетка или директно като се използва кавичка (") във формулата като аргументи.
Надявам се, че тази статия за това как да използвате Използвайте функцията INDEX и MATCH за търсене на стойност в Excel е обяснителна. Намерете още статии за изчисляване на стойности и свързани формули на Excel тук. Ако ви харесаха нашите блогове, споделете го с приятелите си във Facebook. Можете също така да ни следвате в Twitter и Facebook. Ще се радваме да чуем от вас, уведомете ни как можем да подобрим, допълним или обновим работата си и да я подобрим. Пишете ни на имейл сайта.
Използвайте INDEX и MATCH за търсене на стойност : Формулата INDEX-MATCH се използва за динамично и точно търсене на стойност в дадена таблица. Това е алтернатива на функцията VLOOKUP и тя преодолява недостатъците на функцията VLOOKUP.
Използвайте VLOOKUP от две или повече таблици за търсене : За търсене от множество таблици можем да използваме подход IFERROR. За да търсите от няколко таблици, грешката се приема като превключвател за следващата таблица. Друг метод може да бъде подход If.
Как да извършите търсене с чувствителност към регистъра в Excel : Функцията VLOOKUP на Excel не е чувствителна към регистъра и ще върне първата съвпадаща стойност от списъка. INDEX-MATCH не прави изключение, но може да бъде променен, за да стане чувствителен към регистър. Да видим как…
Търсене на често срещан текст с критерии в Excel : Търсенето най-често се появява в текст в диапазон, който използваме функцията INDEX-MATCH с MODE. Ето метода.
Популярни статии:
Как да използвате функцията IF в Excel : Изразът IF в Excel проверява условието и връща конкретна стойност, ако условието е TRUE или връща друга конкретна стойност, ако FALSE.
Как да използвате функцията VLOOKUP в Excel : Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате функцията SUMIF в Excel : Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.
Как да използвате функцията COUNTIF в Excel : Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.