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

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

=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))

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

Этот совет (3335) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и более поздних версий) здесь:

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