Тази тема описва най-често срещаните причини за VLOOKUP за погрешен резултат за функцията и предоставя предложения за използване на INDEX и MATCH вместо това.
Съвет: Също така вижте картата за бързи справки: Съвети за отстраняване на неизправности с VLOOKUP , които представят често срещаните причини за #NA проблеми в удобен PDF файл. Можете да споделите PDF файла с други хора или да го отпечатате за справка.
Проблем: Търсената стойност не е в първата колона на аргумента масив_на_таблица
Едно ограничение на VLOOKUP е, че може да търси стойности само в най-лявата колона в масива на таблицата. Ако търсената стойност не е в първата колона на масива, ще видите грешката #N/A.
В таблицата по-долу искаме да извлечем продадените бройки от артикула "Зеле".
Грешката #N/A се показва, защото търсената стойност "Зеле" се появява във втората колона (Продукти) на аргумента table_array A2:C10. В този случай Excel го търси в колона A, а не в колона B.
Решение: Може да опитате да решите проблема, като настроите VLOOKUP да препраща към правилната колона. Ако това не е възможно, опитайте да преместите колоните. Това също може да е много непрактично, ако имате големи или сложни електронни таблици, където стойностите на клетките са резултати от други изчисления – или може би има други логически причини, поради които просто не можете да премествате колоните. Решението е да използвате комбинация от функциите INDEX и MATCH, която може да търси стойност в дадена колона, независимо от позицията в таблицата за търсене. Вижте следващия раздел.
Помислете дали да не използвате INDEX/MATCH вместо това
INDEX и MATCH са добри опции за много случаи, в които VLOOKUP не отговаря на вашите нужди. Ключовото предимство на INDEX/MATCH е, че можете да търсите стойност в колона на всяко място в таблицата за справки. INDEX връща стойност от зададена таблица/диапазон – според позицията си. MATCH връща относителната позиция на стойност в таблица/диапазон. Използвайте INDEX и MATCH заедно във формула, за да търсите стойност в таблица/масив, като зададете относителната позиция на стойността в таблицата/масива.
Има няколко предимства от използването на INDEX/MATCH вместо VLOOKUP:
-
При INDEX и MATCH върнатата стойност не трябва да е в същата колона като колоната за справка. Това е различно от VLOOKUP, при което върнатата стойност трябва да е в зададения диапазон. Какво означава това? При VLOOKUP трябва да знаете номера на колоната, която съдържа върнатата стойност. Макар че това може да не изглежда трудно, може да бъде тромаво, когато имате голяма таблица и трябва да преброите броя на колоните. Освен това, ако добавите/премахнете колона в таблицата, трябва да преброите отново и да актуализирате аргумента col_index_num . При INDEX/MATCH не се изисква преброяване, тъй като колоната за търсене е различна от колоната, която съдържа върнатата стойност.
-
С INDEX и MATCH можете да зададете ред или колона в масив – или да зададете и двете. Това означава, че можете да търсите стойности както вертикално, така и хоризонтално.
-
INDEX и MATCH могат да се използват за търсене на стойности във всяка колона. За разлика от VLOOKUP – в която можете да търсите стойност само в първата колона на таблица – INDEX и MATCH ще работят, ако търсената стойност е в първата колона, последната или някъде по средата.
-
INDEX и MATCH предлагат гъвкавостта на динамичните препратки към колоната, която съдържа върнатата стойност. Това означава, че можете да добавяте колони към вашата таблица, без да прекъсвате INDEX и MATCH. От друга страна VLOOKUP се прекъсва, ако трябва да добавите колона към таблицата – тъй като прави статична препратка към таблицата.
-
INDEX и MATCH предлагат по-голяма гъвкавост при съвпаденията. INDEX и MATCH могат да намерят точно съвпадение или стойност, която е по-голяма или по-малка от търсената стойност. VLOOKUP търси само съвпадение, най-близко до дадената стойност (по подразбиране) или точната стойност. VLOOKUP също така приема по подразбиране, че първата колона на масива на таблицата е сортирана по азбучен ред, но ако таблицата не е сортирана по този начин, VLOOKUP ще се върне първото най-близко съвпадение в таблицата, което може да не е това, което търсите.
Синтаксис
За да съставите синтаксис за INDEX/MATCH, трябва да използвате аргумента масив/препратка от функцията INDEX и да вложите синтаксиса на MATCH в него. Това е във формата:
=INDEX(масив или препратка; MATCH(търсена_стойност;масив_за_търсене;[тип_съвпадение])
Нека използваме INDEX/MATCH, за да заместим VLOOKUP от примера по-горе. Синтаксисът ще изглежда така:
=INDEX(C2:C10;MATCH(B13;B2:B10;0))
Казано на обикновен език, това означава следното:
=INDEX(връща стойност от C2:C10, която ще MATCH(Зеле, което е някъде в масива B2:B10, в което върнатата стойност е първата стойност, съответстваща на "Зеле"))
Формулата търси първата стойност в C2:C10, която съответства на "Зеле" (в B7), и връща стойността в C7 (100), което е първата стойност, която съответства на "Зеле".
Проблем: Не е намерено точно съвпадение
Когато аргументът range_lookup е FALSE и VLOOKUP не може да намери точно съвпадение във вашите данни, връща грешката #N/A.
Решение: Ако сте сигурни, че съответните данни съществуват в електронната таблица и VLOOKUP не ги намира, отделете време, за да проверите дали клетките, към които препращате, не съдържат скрити интервали или непечатаеми знаци. Също така се уверете, че клетките следват правилния тип данни. Например клетките с числа трябва да са форматирани като число, а не като текст.
Също така помислете дали да не използвате функцията CLEAN или TRIM , за да изчистите данните в клетките.
Проблем: Търсената стойност е по-малка от най-малката стойност в масива
Ако аргументът range_lookup е зададен на TRUE – а търсената стойност е по-малка от най-малката стойност в масива – ще видите грешката #N/A. TRUE търси приблизително съвпадение в масива и връща най-близката стойност, която е по-малка от търсената.
В следващия пример търсената стойност е 100, но в диапазона B2:C10 няма стойности, които да са по-малки от 100; това води до грешката.
Решение:
-
Коригирайте търсената стойност както е необходимо.
-
Ако не можете да промените търсената стойност и се нуждаете от по-голяма гъвкавост със съвпадащите стойности, помислете за използване на INDEX/MATCH вместо VLOOKUP – вж. раздела по-горе в тази статия. С INDEX/MATCH можете да търсите стойности, по-големи от, по-малки от или равни на търсената стойност. За повече информация относно използването на INDEX/MATCH вместо VLOOKUP вижте предишния раздел в тази тема.
Проблем: Колоната за търсене не е сортирана във възходящ ред
Ако аргументът range_lookup е зададен на TRUE – и една от колоните за справка не е сортирана във възходящ ред (A-Z) – ще видите грешката #N/A.
Решение:
-
Променете функцията VLOOKUP, така че да търси точно съвпадение. За да направите това, задайте аргумента диапазон_за_търсене на FALSE. Не е необходимо сортиране за FALSE.
-
Използвайте функциите INDEX/MATCH за търсене на стойност в несортирана таблица.
Проблем: Стойността е голямо число с плаваща запетая
Ако в клетките има стойности за дата и час или големи десетични числа, Excel връща грешката #N/A поради препълване при плаваща запетая. Числата с плаваща запетая имат цифри след десетичния знак. (Excel съхранява стойностите за време като числа с плаваща запетая.) Excel не може да съхранява числа с много големи плаващи знаци, така че за да работи функцията правилно, числата с плаваща запетая трябва да бъдат закръглени до 5 цифри след десетичния знак.
Решение: Скъсете числата, като ги за закръглите до 5 цифри след десетичния знак с помощта на функцията ROUND.
Имате нужда от още помощ?
Винаги можете да попитате експерт в техническата общност на Excel или да получите поддръжка в Общността за отговори от.
