Ошибка ref в excel vlookup

Обновлено: 30.06.2024

The reason for this, depending on the value of the supplied [range_lookup] argument, is generally one of the following:

The smallest value in the left-hand column of the table_array is greater than the supplied lookup_value .

The left column of the table_array is not in ascending order.

Diagnosing the Problem

Step 1 - Check The Syntax of Your Formula

Therefore you need to check the following:

That the lookup_value is actually set to the value that you want to look up;
That the table_array is correctly set to the range of values that you want to search and return values from. Make sure that the column of values to be searched is the first column of the table_array ;
If the [range_lookup] argument is set to TRUE (or is omitted) make sure that the first column of the table_array is in ascending order.

Step 2 - Check Data Types

If you are satisfied that the syntax of your Vlookup function is correct, check that the lookup_value has the same data type as the values to be searched (i.e. the values in the first column of the table_array ).

If these values have different data types, Excel will be unable to compare them. For example, if the lookup_value is the text string "1110004", the Excel Vlookup function will not be able to find this value within the set of numeric values, 1110001, 1110002, 1110003, 1110004, etc.

This is shown in the spreadsheet below:

One of the easiest ways to quickly identify if a value in a cell is a text value is to use the Excel IsText function. I.e. for the example above, to check if cell B1 is actually a text value, type the following into any available cell:

Then check the contents of cell E6 by typing the following into any available cell:

The IsText function returns TRUE if the supplied cell contains a text value or FALSE otherwise. Therefore, if the two above formulas return different results, you know that the contents of cells B1 and E6 have different data types.

Solution: Force the Data to Have the Same Type

If you find that your lookup_value and the first column of your table_array do have different data types, this can be resolved by changing one of the data types (for example, you might want to change the contents of column E of the above spreadsheet into text values).

For an explanation of how do this, see the Convert Number to Text or the Convert Text to Number pages.

Step 3 - Check For True Equality Between 'Matching' Values

If you are using the exact match version of the Vlookup function (i.e. with the [range_lookup] set to FALSE), it may be the case that the lookup_value is not exactly equal to the value that you believe it should match, within the table_array .

For example, in the spreadsheet below, the contents of cells B1 and E6 look equal, but Excel may not actually evaluate these two cells to be exactly equal.

In the example above, to test if Excel considers the contents of cells B1 and E6 to be truly equal, enter the following formula into any free Excel cell:

This formula will evaluate to TRUE if Excel considers the contents of cells B1 and E6 to be truly equal or FALSE otherwise.

If the above formula evaluates to FALSE, this tells you that the cause of your error is that Excel does not consider the contents of cells B1 and E6 to be truly equal. This is likely to be due to one of the following reasons:

Possible Reason No. 1 - Different Data Types

The lookup_value and the data in the first column of your table_array may have different Excel data types (see Step 2 above).

Possible Reason No. 2 - Unseen Characters

You may have unseen characters, such as spaces, at the start or end of either the lookup_value , or in the cells of your table_array . These characters cause the lookup_value and the 'matching' value in the table_array to be slightly different.

Solution - Remove Unseen Characters

Double click on each of the cells and check the contents to see if there are any unseen characters at the start or end of the cells. If so, you will need to remove any additional characters from the cells. This can be done manually, but if you want to change several cells, it may be faster to use the Excel Trim function, as follows:

Create a new column next to the one with the additional spaces in it.
Use the Trim function in your new column, to get rid of extra spaces.
(e.g. if the original data is in cell E2, the function =TRIM(E2) removes any leading or trailing spaces).
Copy the Trim function down the entire column of values.
Copy the contents of the new column and paste the values only into the original column, using paste->special->values.
Delete the column containing the Trim function.

Note that the Trim function removes trailing spaces from the start and end of a text string. However, if you have other unseen characters, you may need to carry out the above steps using the Excel Clean function instead of (or as well as) the Trim function.

Further Vlookup troubleshooting tips are provided, in the form of a handy Vlookup Quick Reference Card, on the Microsoft Office website.

В какой-то момент мы все посмотрели на функцию VLOOKUP и подумали, что ищем другой язык. Если вы ранее не работали с функцией VLOOKUP, это может быть немного утомительно и часто приводит к разочарованию при возникновении проблем.

Понимание аргументов VLOOKUP

Прежде чем обсуждать различные решения для работы с VLOOKUP, важно понять, что делает VLOOKUP и как он работает. Давайте взглянем и разберем функцию.

Lookup_Value

Это значение, на которое вы хотите посмотреть. Это может быть значение ячейки или статическое значение, указанное в формуле.

= ВПР (A2,DogTable, 2,0) где A2 = собака

таблица_массив

Это диапазон, в котором вы хотите найти свой Lookup_Value. Примечание: искомое значение должно быть в крайнем левом столбце диапазона.

Предполагая, что таблица выше называется «DogTable». Эта именованная таблица представляет диапазон A3: C7.

Итак, наша формула может быть:

= ВПР (С1, DogTable, 2,0)

Обе формулы работают, однако, используя именованные диапазоны и диапазоны таблиц для вашего Table_Array, являются более динамичными и универсальными. Мы рекомендуем это, а не абсолютные диапазоны.

Col_Index_Num

Если вы хотите найти значение «Dog» в DogTable и вернуть его размер, вы будете указывать номер столбца, начиная с первого столбца диапазона.

диапазон_просмотра

Значение по умолчанию для range_lookup всегда будет 1, если не указано. Это позволит найти относительное соответствие и, как правило, не очень точно для большинства целей. Рекомендуется искать точное совпадение, используя 0 или FALSE.

Это будет происходить время от времени, и может быть сложно отследить, если у вас есть сложные формулы, связанные с VLOOKUP. Давайте посмотрим на приведенный ниже пример и посмотрим, в чем проблема и как ее решить.

В приведенном ниже примере у нас есть другой набор данных, где мы хотим найти стоимость животного. Итак, мы собираемся использовать VLOOKUP для ссылки на нашу таблицу базы данных «DogTable» и получения информации о ценах. Как видно ниже, мы используем = VLOOKUP (S10, DogTable, 3,0). S10 содержит значение Bird. S9 содержит значение Dog.

В нескольких предыдущих статьях мы изучили различные грани функции ВПР в Excel. Если Вы читали их внимательно, то сейчас должны быть экспертом в этой области. Однако не без причины многие специалисты по Excel считают ВПР одной из наиболее сложных функций. Она имеет кучу ограничений и особенностей, которые становятся источником многих проблем и ошибок.

1. Искомое значение написано с опечаткой

Хорошая мысль проверить этот пункт в первую очередь! Опечатки часто возникают, когда Вы работаете с очень большими объёмами данных, состоящих из тысяч строк, или когда искомое значение вписано в формулу.

Искомое значение меньше наименьшего значения в просматриваемом массиве.
Столбец поиска не упорядочен по возрастанию.

4. Столбец поиска не является крайним левым

Решение: Если нет возможности изменить структуру данных так, чтобы столбец поиска был крайним левым, Вы можете использовать комбинацию функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH), как более гибкую альтернативу для ВПР.

5. Числа форматированы как текст

Это обычно случается, когда Вы импортируете информацию из внешних баз данных или когда ввели апостроф перед числом, чтобы сохранить стоящий в начале ноль.

Наиболее очевидные признаки числа в текстовом формате показаны на рисунке ниже:

Кроме этого, числа могут быть сохранены в формате General (Общий). В таком случае есть только один заметный признак – числа выровнены по левому краю ячейки, в то время как стандартно они выравниваются по правому краю.

Решение: Если это одиночное значение, просто кликните по иконке ошибки и выберите Convert to Number (Конвертировать в число) из контекстного меню.

Если такая ситуация со многими числами, выделите их и щелкните по выделенной области правой кнопкой мыши. В появившемся контекстном меню выберите Format Cells (Формат ячеек) > вкладка Number (Число) > формат Number (Числовой) и нажмите ОК.

6. В начале или в конце стоит пробел

Решение 1: Лишние пробелы в основной таблице (там, где функция ВПР)

Если лишние пробелы оказались в основной таблице, Вы можете обеспечить правильную работу формул, заключив аргумент lookup_value (искомое_значение) в функцию TRIM (СЖПРОБЕЛЫ):

Решение 2: Лишние пробелы в таблице поиска (в столбце поиска)

Так как это формула массива, не забудьте нажать Ctrl+Shift+Enter вместо привычного Enter, чтобы правильно ввести формулу.

1. Искомое значение длиннее 255 символов

Решение: Используйте связку функций ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH). Ниже представлена формула, которая отлично справится с этой задачей:

2. Не указан полный путь к рабочей книге для поиска

Если Вы извлекаете данные из другой рабочей книги, то должны указать полный путь к этому файлу. Если говорить точнее, Вы должны указать имя рабочей книги (включая расширение) в квадратных скобках [ ], далее указать имя листа, а затем – восклицательный знак. Всю эту конструкцию нужно заключить в апострофы, на случай если имя книги или листа содержит пробелы.

Вот полная структура функции ВПР для поиска в другой книге:

=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)

Настоящая формула может выглядеть так:

=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)

Эта формула будет искать значение ячейки A2 в столбце B на листе Sheet1 в рабочей книге New Prices и извлекать соответствующее значение из столбца D.

Для получения дополнительной информации о функции ВПР, ссылающейся на другой файл Excel, обратитесь к уроку: Поиск в другой рабочей книге с помощью ВПР.

3. Аргумент Номер_столбца меньше 1

Трудно представить ситуацию, когда кто-то вводит значение меньше 1, чтобы обозначить столбец, из которого нужно извлечь значение. Хотя это возможно, если значение этого аргумента вычисляется другой функцией Excel, вложенной в ВПР.

Решение очевидно – проверьте правописание!

ВПР не работает (ограничения, оговорки и решения)

Помимо достаточно сложного синтаксиса, ВПР имеет больше ограничений, чем любая другая функция Excel. Из-за этих ограничений, простые на первый взгляд формулы с ВПР часто приводят к неожиданным результатам. Ниже Вы найдёте решения для нескольких распространённых сценариев, когда ВПР ошибается.

1. ВПР не чувствительна к регистру

Функция ВПР не различает регистр и принимает символы нижнего и ВЕРХНЕГО регистра как одинаковые. Поэтому, если в таблице есть несколько элементов, которые различаются только регистром символов, функция ВПР возвратит первый попавшийся элемент, не взирая на регистр.

2. ВПР возвращает первое найденное значение

Как Вы уже знаете, ВПР возвращает из заданного столбца значение, соответствующее первому найденному совпадению с искомым. Однако, Вы можете заставить ее извлечь 2-е, 3-е, 4-е или любое другое повторение значения, которое Вам нужно. Если нужно извлечь все повторяющиеся значения, Вам потребуется комбинация из функций ИНДЕКС (INDEX), НАИМЕНЬШИЙ (SMALL) и СТРОКА (ROW).

3. В таблицу был добавлен или удалён столбец

К сожалению, формулы с ВПР перестают работать каждый раз, когда в таблицу поиска добавляется или удаляется новый столбец. Это происходит, потому что синтаксис ВПР требует указывать полностью весь диапазон поиска и конкретный номер столбца для извлечения данных. Естественно, и заданный диапазон, и номер столбца меняются, когда Вы удаляете столбец или вставляете новый.

Решение: И снова на помощь спешат функции ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH). В формуле ИНДЕКС+ПОИСКПОЗ Вы раздельно задаёте столбцы для поиска и для извлечения данных, и в результате можете удалять или вставлять сколько угодно столбцов, не беспокоясь о том, что придётся обновлять все связанные формулы поиска.

4. Ссылки на ячейки исказились при копировании формулы

Этот заголовок исчерпывающе объясняет суть проблемы, правда?

Решение: Всегда используйте абсолютные ссылки на ячейки (с символом $) при записи диапазона, например $A$2:$C$100 или $A:$C. В строке формул Вы можете быстро переключать тип ссылки, нажимая F4.

ВПР – работа с функциями ЕСЛИОШИБКА и ЕОШИБКА

ВПР: работа с функцией ЕСЛИОШИБКА

Синтаксис функции ЕСЛИОШИБКА (IFERROR) прост и говорит сам за себя:

То есть, для первого аргумента Вы вставляете значение, которое нужно проверить на предмет ошибки, а для второго аргумента указываете, что нужно возвратить, если ошибка найдётся.

Например, вот такая формула возвращает пустую ячейку, если искомое значение не найдено:

=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")

ВПР: работа с функцией ЕОШИБКА

Так как функция ЕСЛИОШИБКА появилась в Excel 2007, при работе в более ранних версиях Вам придётся использовать комбинацию ЕСЛИ (IF) и ЕОШИБКА (ISERROR) вот так:

Например, формула ЕСЛИ+ЕОШИБКА+ВПР, аналогична формуле ЕСЛИОШИБКА+ВПР, показанной выше:

Вам нужно точное совпадение

Последний аргумент функции ВПР, известный как range_lookup (интервальный_просмотр), спрашивает, какое совпадение Вы хотите получить – приблизительное или точное.

В большинстве случаев люди ищут конкретный продукт, заказ, сотрудника или клиента, и потому хотят точное совпадение. Если производится поиск уникального значения, то аргументом range_lookup (интервальный_просмотр) должно быть FALSE (ЛОЖЬ).

Этот аргумент не обязателен, но если его не указать, то будет использовано значение TRUE (ИСТИНА). В таком случае для правильной работы функции необходимо, чтобы данные были отсортированы в порядке возрастания.

На рисунке ниже показана функция ВПР с пропущенным аргументом range_lookup (интервальный_просмотр), которая возвращает ошибочный результат.

Решение

Если Вы ищите уникальное значение, задайте последний аргумент равным FALSE (ЛОЖЬ). Функция ВПР в примере выше должна выглядеть так:

Зафиксируйте ссылки на таблицу

Возможно, Вы захотите использовать сразу несколько функций ВПР, чтобы извлечь большее количество информации. Если Вы собираетесь скопировать функцию ВПР в несколько ячеек, то необходимо зафиксировать часть аргументов.

На рисунке ниже показан пример функции ВПР, введенной некорректно. Для аргументов lookup_value (искомое_значение) и table_array (таблица) введены неправильные диапазоны ячеек.

Решение

Аргумент table_array (таблица) – это таблица, которую ВПР использует для поиска и извлечения информации. Чтобы корректно скопировать функцию ВПР, в аргументе table_array (таблица) должна быть абсолютная ссылка на диапазон ячеек.

Кликните по адресу ссылки внутри формулы и нажмите F4 на клавиатуре, чтобы превратить относительную ссылку в абсолютную. Формула должна быть записана так:

В этом примере ссылки в аргументах lookup_value (искомое_значение) и table_array (таблица) сделаны абсолютными. Иногда достаточно зафиксировать только аргумент table_array (таблица).

Вставлен столбец

Аргумент col_index_num (номер_столбца) используется функцией ВПР, чтобы указать, какую информацию необходимо извлечь из записи.

В связи с тем, что аргумент введен как числовой индекс, он не очень надёжен. Если в таблицу вставить новый столбец, функция ВПР может перестать работать. Рисунок ниже показывает именно такой сценарий.

Столбец Quantity (Количество) был 3-м по счету, но после добавления нового столбца он стал 4-м. Однако функция ВПР автоматически не обновилась.

Решение 1

Одним из решений будет защитить таблицу, чтобы пользователи не могли вставлять новые столбцы. В случае, когда пользователям потребуется такая возможность, решение станет не жизнеспособным.

Решение 2

Другой вариант – вставить функцию MATCH (ПОИСКПОЗ) в аргумент col_index_num (номер_столбца) функции ВПР.

Функция ПОИСКПОЗ может быть использована для того, чтобы найти и возвратить номер требуемого столбца. Это сделает аргумент col_index_num (номер_столбца) динамичным, т.е. можно будет вставлять новые столбцы в таблицу, не влияя на работу функции ВПР.

Формула, показанная ниже, может быть использована в этом примере, чтобы решить проблему, описанную выше.

Таблица стала больше

По мере добавления новых строк в таблицу, функции ВПР может потребоваться корректировка для гарантии, что новые строки таблицы охвачены формулой. На рисунке ниже показана ситуация, когда функция ВПР просматривает не всю таблицу в поисках нужного фрукта.

Решение

Форматируйте диапазон ячеек как таблицу (Excel 2007+) или как именованный диапазон. Такие приёмы дадут гарантию, что ВПР всегда будет обрабатывать всю таблицу.

Чтобы форматировать диапазон как таблицу, выделите диапазон ячеек, который собираетесь использовать для аргумента table_array (таблица). На Ленте меню нажмите Home > Format as Table (Главная > Форматировать как таблицу) и выберите стиль из галереи. Откройте вкладку Table Tools > Design (Работа с таблицами > Конструктор) и в соответствующем поле измените имя таблицы.

В формуле на рисунке ниже использовано имя таблицы FruitList.

ВПР не может смотреть влево

Функция ВПР имеет такое ограничение: она не может извлекать информацию из колонок, находящихся слева от первой. Она ищет искомое значение в крайней левой колонке заданной таблицы и возвращает информацию, находящуюся правее.

Решение

Решение этой проблемы – не использовать ВПР вовсе. Используйте комбинацию функций INDEX (ИНДЕКС) и MATCH (ПОИСКПОЗ), которая стала привычной альтернативой для ВПР. Это намного более гибкое решение

Пример, приведённый ниже, был использован для извлечения информации из колонки слева от той, по которой производится поиск:

Данные в таблице дублируются

Функция ВПР может извлечь только одну запись. Она возвратит первую найденную запись, соответствующую введённому Вами условию поиска.

Если таблица содержит повторяющиеся значения, функция ВПР не справится с такой задачей правильно.

Решение 1

Нужны ли Вам повторяющиеся данные в списке? Если нет – удалите их. Это можно сделать быстро при помощи кнопки Removes Duplicates (Удалить дубликаты) на вкладке Data (Данные).

Решение 2

Решили оставить дубликаты? Хорошо! В таком случае, Вам нужна не функция ВПР. Для таких случаев отлично подойдёт сводная таблица, позволяющая выбрать значение и посмотреть результаты.

Таблица ниже – это список заказов. Допустим, Вы хотите найти все заказы определённого фрукта.

Сводная таблица позволяет выбрать значение из столбца ID в фильтре, которое соответствует определенному фрукту, и получить список всех связанных заказов. В нашем примере выбрано значение ID равное 23 (Бананы).

ВПР без забот

Эта статья показывает решения 6 наиболее распространённых причин сбоя в работе функции ВПР. Вооружившись этой информацией, Вы сможете насладиться более беззаботным будущим в компании замечательных функций Excel.

Читайте также: