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

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

=VLOOKUP(5,A1:B10,2,FALSE)

В этом примере выполняется поиск в таблице поиска (A1: B10) в поисках значения 5 в первом столбце таблицы. Если он найден, то возвращается соответствующее значение из второго столбца. Если он не найден, функция ВПР возвращает ошибку # Н / Д, указывая на то, что не удалось найти значение. (Значение FALSE в качестве четвертого параметра указывает, что вам не нужны приблизительные совпадения.)

Таким образом, ключ состоит в том, чтобы воспроизвести это значение # N / A и построить то, что вы хотите вернуть, если совпадения нет. Следующая формула вернет серию из пяти звездочек, если при поиске совпадений не было:

=IF(ISNA(VLOOKUP(5,A1:B10,2,FALSE)),"**",VLOOKUP(5,A1:B10,2,FALSE))

Функция ISNA используется для проверки того, является ли результатом VLOOKUP ошибка # N / A. Если да, то возвращаются звездочки; если нет, то возвращается значение поиска. Если вы хотите, чтобы формула возвращала «ничего»,

тогда вы можете использовать этот вариант:

=IF(ISNA(VLOOKUP(5,A1:B10,2,FALSE)),"",VLOOKUP(5,A1:B10,2,FALSE))

Эта версия возвращает пустую строку, если в таблице поиска не было совпадений. Для некоторых целей это может быть не совсем то, что вам нужно. Возможно, вам будет удобнее вернуть ноль, а затем скрыть нули на листе (Файл | Параметры | Дополнительно | Параметры отображения для этого рабочего листа | Снимите флажок Показывать ноль в ячейках с нулевым значением). Если вы хотите, чтобы возвращался ноль, потребуется всего одно изменение:

=IF(ISNA(VLOOKUP(5,A1:B10,2,FALSE)),0,VLOOKUP(5,A1:B10,2,FALSE))

Конечно, вы также можете использовать функцию ЕСЛИОШИБКА, чтобы найти то, что вам нужно. Следующий вариант формулы будет работать нормально:

IFERROR(VLOOKUP(5,A1:B10,2,FALSE),"**")

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (10940) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365. Вы можете найти версию этого совета для старого интерфейса меню Excel здесь:

link: / excel-Returning_Blanks_or_Asterisks_from_a_Lookup [Возврат пробелов или звездочек из поиска].