Ищем значения с VLOOKUP
Если не указать последний аргумент VLOOKUP, и Excel, и Google Таблицы используют приблизительное совпадение. Google советует писать FALSE.
Прайс-лист с кодами
Магазин хранит цены в маленькой таблице: коды в A2:A5, названия товаров в B2:B5, цены в C2:C5. На кассе кто-то вводит код и хочет узнать цену. Это работа для VLOOKUP. Функция находит значение в первом столбце таблицы и возвращает что-то из той же строки. Microsoft перечисляет четыре нужные ей части: искомое значение, диапазон, в котором оно находится, номер столбца в этом диапазоне, где лежит ответ, и нужно ли приблизительное (TRUE) или точное (FALSE) совпадение. =VLOOKUP(104, A2:C5, 3, FALSE) находит 104 в столбце A и возвращает цену из третьего столбца диапазона.
Большинство проблем создают два правила. Первое: значение должно быть в первом столбце диапазона, а ответ правее него. VLOOKUP никогда не смотрит влево, поэтому если коды стоят в столбце B, диапазон должен начинаться с B. Второе: номер столбца отсчитывается от первого столбца диапазона, а не от столбца A листа; в B2:D9 столбец B имеет номер 1, C номер 2, D номер 3. Обе справки дают одинаковый совет и для копируемых формул. Делай диапазон абсолютным, как $A$2:$C$5, чтобы он не съезжал при протягивании вниз.
Когда кода нет в списке
С FALSE функция VLOOKUP ищет ровно то значение, которое ты дал, и возвращает первую совпавшую строку. Если совпадения нет, получается #N/A. Номер столбца больше числа столбцов в диапазоне даёт #REF!, а номер меньше 1 даёт #VALUE!. Поиск текста не различает регистр, но различает пробелы: справка Google указывает, что " Apple", "Apple " и "Apple" являются тремя разными значениями. Число, сохранённое как текст, тоже отличается от числа, поэтому код, введённый как '104, не находится по 104.
При точном совпадении в обеих программах работают подстановочные знаки ? и *. ? заменяет любой один символ, а * любую цепочку символов, поэтому "Ora*" находит Orange. Когда #N/A является нормальным исходом, например код ещё не внесён в прайс-лист, оберни поиск в IFNA, которая есть в обеих программах: =IFNA(VLOOKUP(E2, $A$2:$C$5, 3, FALSE), "not found"). IFERROR тоже работает. Но она прячет ошибки любого рода, включая неверный номер столбца, который ты хотел бы увидеть, и страница Microsoft об ошибке #VALUE! предостерегает от IFERROR по той же причине.
Перетащи, чтобы повернуть. Колёсико или щипок для масштаба.
Ищем диапазон, а не точный код
TRUE или полное отсутствие четвёртого аргумента даёт приблизительное совпадение, это поведение по умолчанию в обеих программах. Справка Google описывает поиск так: функция идёт вниз по первому столбцу, пока не найдёт значение больше искомого, и останавливается на строке перед ним. Это подходит для диапазонов. В таблице оценок с 0, 60, 70, 80 и 90 в первом столбце балл 85 проходит 80, встречает 90 и возвращает B из строки 80. Первый столбец должен быть отсортирован от меньшего к большему, а значение меньше первой строки возвращает #N/A. На неотсортированном столбце ответу нельзя доверять, поэтому Google советует FALSE, если ты не имеешь в виду диапазоны.
Перетащи, чтобы повернуть. Колёсико или щипок для масштаба.
| Microsoft Excel | Google Таблицы | |
|---|---|---|
| Название 4-го аргумента | range_lookup | is_sorted |
| По умолчанию, если не указан | TRUE (приблизительное) | TRUE (приблизительное) |
| Совет справки | отсортировать первый столбец или использовать FALSE | рекомендуется FALSE |
| XLOOKUP | Microsoft 365 и Excel 2021 и новее; нет в 2016 и 2019 | есть |
| Нечитаемая формула | отклоняется с сообщением | #ERROR! |
| Циклическая ссылка | предупреждение, затем 0 или последнее значение | #REF! |
| ROUND без разрядов | не принимается | округляет до целого |
| Аргументов в функции | до 255 | любое количество |
Теперь в обеих программах есть и XLOOKUP. Она принимает диапазон поиска и отдельный диапазон результатов, поэтому ответ может стоять левее, и по умолчанию использует точное совпадение. Microsoft отмечает, что XLOOKUP нет в Excel 2016 и Excel 2019, поэтому файлу, который должен открываться в этих версиях, лучше оставаться с VLOOKUP. Таблица выше собирает различия двух программ, которые встретились в этом курсе.
- Не писать FALSE при поиске кода. По умолчанию действует приблизительное совпадение, поэтому код, которого нет в списке, может вернуть цену из строки над ним вместо #N/A.
- Отсчитывать номер столбца от столбца A листа. Отсчёт идёт от первого столбца диапазона. В C2:F20 столбец E имеет номер 3, а не 5.
- Протягивать поиск вниз без $ в диапазоне. A2:C5 съезжает на A3:C6, A4:C7 и так далее, и нижние строки перестают находить коды в верхней части таблицы.
- Оборачивать каждый поиск в IFERROR. Она прячет и #REF! от неверного номера столбца, и ожидаемую #N/A. IFNA прячет только #N/A.
- Коды 101, 103, 104, 107 с товарами Apple, Banana, Orange, Pear в A2:B5. Что возвращает =VLOOKUP(103, A2:B5, 2, FALSE)?
- Та же таблица. Что возвращает =VLOOKUP(102, A2:B5, 2, FALSE)?
- #N/A
- Apple
- Banana
- #REF!
- Та же таблица. Что возвращает =VLOOKUP(102, A2:B5, 2, TRUE)?
- Та же таблица. Что возвращает =VLOOKUP(103, A2:B5, 3, FALSE)?
- #REF!
- #N/A
- #VALUE!
- Banana
- Диапазон C2:F20. Какой номер столбца возвращает значения из столбца E?
- Таблица оценок: 0 F, 60 D, 70 C, 80 B, 90 A. Что возвращает приблизительная VLOOKUP для 85?
- Та же таблица. Что она возвращает ровно для 90?
- Что происходит, если не указать четвёртый аргумент?
- Приблизительное совпадение в обеих программах
- Точное совпадение в обеих программах
- Точное в Excel, приблизительное в Таблицах
- Ошибка
- Зачем записывать диапазон таблицы как $A$2:$C$5?
- Чтобы он не съезжал при протягивании формулы вниз
- Чтобы он сортировал таблицу
- Чтобы он находил только заглавные буквы
- Чтобы он возвращал текст
- Товары стоят в B2:B5, цены в C2:C5. Что возвращает =IFNA(VLOOKUP("Pencil", B2:C5, 2, FALSE), "not found")?
- В каких версиях Excel нет XLOOKUP?
- Excel 2016 и Excel 2019
- Microsoft 365
- Excel в браузере
- Excel 2021
- VLOOKUP(значение, диапазон, номер столбца, FALSE) для точного совпадения.
- Значение должно быть в первом столбце; номер столбца отсчитывается от диапазона.
- TRUE (или ничего) даёт приблизительное совпадение: первый столбец должен быть отсортирован, и функция возвращает последнюю строку, значение в которой не больше искомого.
- Используй абсолютный диапазон, IFNA для ожидаемой #N/A и FALSE, если не имеешь в виду диапазоны.
Источники курса (19)
- Microsoft Support, "Switch between relative, absolute, and mixed references" (support.microsoft.com/en-us/office/dfec08cd-ae65-4f56-839e-5f0d8d0baca9: $ before column and row, F4 to switch, copying two down and two right: $A$1 stays, A$1 to C$1, $A1 to $A3, A1 to C3)
- Microsoft Support, "AVERAGE function" (support.microsoft.com/en-us/office/047bac88-d466-426c-a32b-8f33eb960cf6: text, logical values and empty cells in a reference ignored, zeros included; 10, 7, 9, 27, 2 gives 11, with 5 gives 10; mean 5, median 4, mode 3 for 2 3 3 5 7 10)
- Microsoft Support, "COUNT function" (support.microsoft.com/en-us/office/a59cd7fc-b623-4d93-87a4-d23bf411294c: only numbers counted, dates count, errors and text do not)
- Microsoft Support, "ROUND function" (support.microsoft.com/en-us/office/c018c5d8-40fb-4053-90b1-b3e7f61a213c: num_digits required; 2.15 to 2.2, -1.475 to -1.48, 626.3 to 1000, 1.98 to 0, -50.55 to -100)
- Microsoft Support, "IF function" (support.microsoft.com/en-us/office/69aed7c9-4e8a-4755-a9bc-aa8bbff73be2: value_if_false optional, text in quotes except TRUE and FALSE, 0 and #NAME? problems)
- Microsoft Support, "VLOOKUP function" (support.microsoft.com/en-us/office/0bbc8083-26fe-4963-8ab8-93a18ad188a1: range_lookup TRUE by default, sorted first column, #N/A, #REF!, #VALUE!, absolute table range, wildcards with FALSE)
- Microsoft Support, "XLOOKUP function" (support.microsoft.com/en-us/office/b7fd680e-6d10-43e6-84f9-88eae8bf5929: not in Excel 2016 and Excel 2019, exact match by default)
- Microsoft Support, "How to correct a #VALUE! error" (support.microsoft.com/en-us/office/15e1b616-fbf2-4147-9c0b-0a11a20e409e: + and * may not calculate cells with text, use SUM instead, IFERROR hides all errors)
- Microsoft Support, "Excel specifications and limits" (15 digits, 64 nested levels, 255 arguments)
- Google Docs Editors Help, "VLOOKUP" (support.google.com/docs/answer/3093318: is_sorted TRUE by default, FALSE recommended, search down to a larger value, " Apple" and "Apple " differ, wildcards, IFNA, absolute range)
- Google Docs Editors Help, "XLOOKUP" (support.google.com/docs/answer/12405947)
- Google Docs Editors Help, "ROUND" (support.google.com/docs/answer/3093440: places optional, 0 by default, rounding by magnitude)
- Google Docs Editors Help, "IF" (support.google.com/docs/answer/3093364: order of the answers is the most common problem, IFS)
- Google Docs Editors Help, "AVERAGE" (support.google.com/docs/answer/3093615: text ignored, any number of arguments)
- Google Docs Editors Help, "COUNTA" (support.google.com/docs/answer/3093991: zero-length strings and spaces count)
- Google Docs Editors Help, "Keyboard shortcuts for Google Sheets" (support.google.com/docs/answer/181110: F4 and Fn+F4 while entering a formula, F4 as Redo)
- Pryor Learning and Exceljet (F4 cycle A1, $A$1, A$1, $A1; = comparison not case-sensitive)
- Ablebits (IF without value_if_false returns FALSE)
- Spreadsheet Class (Sheets circular dependency shows #REF!)
Проверено: 2026-10-19