Общая формула
=VLOOKUP(val&"",table,col,0)
Резюме
Чтобы использовать функцию ВПР для извлечения информации из таблицы, в которой значения ключа представляют собой числа, хранящиеся в виде текста, вы можете использовать формулу, которая объединяет пустую строку ("") с числовым значением поиска, переводя его в текст. В показанном примере формула H3 имеет следующий вид:
=VLOOKUP(id&"",planets,2,0) // returns "Earth"
где id (H2) и планеты (B3: B11) являются именованными диапазонами.
Примечание. Этот пример представляет собой обходной путь к проблеме несоответствия чисел и текста, которая вызывает ошибку # N / A. Если несоответствия нет, обходной путь не требуется, и вы можете использовать обычную формулу ВПР.
Объяснение
Распространенная ошибка ВПР - это несоответствие чисел и текста. Чаще всего столбец подстановки в таблице содержит числовые значения, которые выглядят как числа, но фактически являются числами, хранящимися в виде текста. Когда подлинное число передается в ВПР в качестве первого аргумента, формула возвращает ошибку # Н / Д, даже если кажется, что есть совпадение. На приведенном ниже экране показан пример этой проблемы:
Числа в столбце B на самом деле являются текстовыми, поэтому числовое значение поиска 3 не выполняется, хотя кажется, что функция ВПР должна соответствовать B5 и возвращать «Земля». Вы можете ввести число как текстовое значение, поставив перед числом одинарную кавычку (').
Лучшее решение - убедиться, что значения поиска в таблице действительно являются числами. Однако, если у вас нет контроля над таблицей, вы можете изменить формулу ВПР для приведения значения поиска в соответствие с типом в таблице. В показанном примере мы приводим числовое значение подстановки к тексту, объединяя пустую строку:
=VLOOKUP(id,planets,2,0) // original =VLOOKUP(id&"",planets,2,0) // revised
И исправленная формула исправляет ошибку:
Вы также можете сделать то же самое с более длинной формулой, которая использует функцию ТЕКСТ для преобразования числа в текст:
=VLOOKUP(TEXT(id,"@"),planets,2,0)
И числа, и текст
Если вы не можете быть уверены, когда у вас будут числа, а когда - текст, вы можете использовать оба варианта, заключив ВПР в функцию ЕСЛИОШИБКА и используя формулу, которая обрабатывает оба случая:
=IFERROR(VLOOKUP(id,planets,3,0),VLOOKUP(id&"",planets,3,0))
Здесь мы сначала пробуем обычную формулу ВПР, которая предполагает, что и значение поиска, и первый столбец в таблицах являются числами. Если это вызывает ошибку, мы пробуем еще раз с измененной формулой. Если эта формула также не работает, функция ВПР, как всегда, будет возвращать ошибку # Н / Д.
