Как да отстраните грешките на VLOOKUP в Excel

Как да

отБен Стоктън

Последна актуализация на13 януари 2020 г.

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

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

Ограничения на VLOOKUP

Преди да започнете да използвате VLOOKUP, трябва да сте наясно, че той не винаги е най-добрият вариант за потребителите на Excel.

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

В примера по-долу най-отдалечената колона (колона А) се използва като колона за търсене. В диапазона няма дублирани стойности и данните за търсене (в този случай данни от колона Б) е вдясно от колоната за търсене.

Функциите INDEX и MATCH биха били добри алтернативи, ако някой от тези проблеми е проблем, както и новата XLOOKUP функция в Excel, която в момента е в бета тестване.

VLOOKUP също изисква данните да бъдат подредени в редове, за да могат точно да търсят и връщат данни. HLOOKUP би била добра алтернатива, ако това не е така.

Има някои допълнителни ограничения за формулите на VLOOKUP, които могат да причинят грешки, както ще обясним по-нататък.

VLOOKUP и # N / A грешки

Една от най-често срещаните грешки VLOOKUP в Excel е # N / A грешка. Тази грешка възниква, когато VLOOKUP не може да намери стойността, която търсите.

Като начало стойността на търсенето може да не съществува във вашия диапазон от данни или може да сте използвали грешна стойност. Ако видите грешка N / A, проверете двукратно стойността във вашата формула VLOOKUP.

Ако стойността е правилна, стойността на търсенето ви не съществува. Това предполага, че използвате VLOOKUP, за да намерите точни съвпадения, с range_lookup аргумент, зададен на FALSE.

В горния пример, търсене на a Студентски документ за самоличност с номер 104 (в клетка G4) връща # N / Грешка защото минималният идентификационен номер в диапазона е 105.

Ако range_lookup аргумент в края на вашата формула VLOOKUP липсва или е зададен ВЯРНО, след това VLOOKUP ще върне грешка # N / A, ако диапазонът ви от данни не е сортиран във възходящ ред.

Освен това ще върне грешка # N / A, ако стойността на търсенето ви е по-малка от най-ниската стойност в диапазона.

В горния пример Студентски документ за самоличност стойностите се смесват. Въпреки стойност от 105 съществува в гамата, VLOOKUP не може да извърши правилно търсене с range_lookup аргумент, зададен на ВЯРНО защото колона А не е сортирана във възходящ ред.

Други често срещани причини за грешки с № N / A включват използване на колона за търсене, която не е най-отдалечената и използване на клетка справки за стойности за търсене, които съдържат числа, но са форматирани като текст или съдържат излишни символи като пространства.

Отстраняване на грешки # VALUE Грешки

Най- #VALUE грешката обикновено е знак, че формулата, съдържаща функцията VLOOKUP, е погрешна по някакъв начин.

В повечето случаи това обикновено се дължи на клетката, която посочвате като стойност на вашето търсене. Най- максимален размер на стойност за търсене на VLOOKUP е 255 знака.

Ако имате работа с клетки, които съдържат по-дълги символни низове, VLOOKUP няма да може да се справи с тях.

Единственото решение за това е да замените формулата си VLOOKUP с комбинирана формула INDEX и MATCH. Например, когато една колона съдържа клетка със низ от 255 знака, вместо това могат да се използват вложена функция MATCH вътре в INDEX.

В примера по-долу, INDEX връща стойността в клетка B4, използвайки диапазона в колона А за идентифициране на правилния ред. Това използва вложеното СЪВПАДА функция за идентифициране на низ в колона А (съдържаща 300 знака), която съответства на клетката H4.

В този случай това е клетка A4, с INDEX връщане 108 (стойността на B4).

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

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

Ако срещнете грешка #VALUE, проверете двукратно формулата си VLOOKUP, за да потвърдите дали вашите препратки към клетки са правилни.

#NAME и VLOOKUP

Ако вашата грешка VLOOKUP не е грешка # VALUE или грешка # N / A, то вероятно е #NAME грешка. Преди да се паникьосвате при мисълта за този, бъдете сигурни - това е най-лесната грешка в VLOOKUP, която трябва да коригирате.

Грешка #NAME се появява, когато сте написали неправилно функция в Excel, независимо дали е VLOOKUP или друга функция като SUM. Кликнете върху вашата VLOOKUP клетка и проверете два пъти дали сте написали VLOOKUP правилно.

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

Използване на други функции на Excel

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

Ако VLOOKUP не е подходящ за вас, тогава се възползвайте от тях топ Excel функции вместо.