Excel обрабатывает числа с буквами, отличными от цифр, хотя формат одинаков

Question

У меня есть номера, такие как 4BU, которые правильно обрабатываются по этой формуле:

=IF($B8<>"";VLOOKUP("*"&$B8&"*";Sheet1!$A:$J;1;FALSE);"")

Если он находит номер, он показывает его

Но у меня также есть номера, такие как 578, которые выводят только # N/A. Все они отформатированы как текст (в этом примере и B8, и A:J), но они не обрабатываются одинаково.

Я заметил, что числа без букв работают, когда я убираю символы подстановки из формулы, но это делает формулу бесполезной. Как это исправить?

Answer 1

Используйте это в формате массива:

=IF($B8<>"";INDEX(Sheet1!$A1:$J100;MATCH("*"&$B8&"*";Sheet1!$A1:$A100&"";0);1);"")

Будучи формулой массива, она должна соответствовать Ctrl-Shift-Enter, установленной как Enter, при выходе из режима редактирования. Если все сделано правильно, тогда Excel поместит {} вокруг формулы.

Будучи формулой Array, ссылки должны быть ограничены набором данных, а не целым столбцом. Я ссылался только на первые 100 строк, измените это на ваш набор данных.

Answer 2

Ваша формула VLookup

= IF($ B8 <> ""; VLOOKUP(" " & $ B8 & " "; Sheet1!$ A:$ J; 1; ЛОЖЬ); "")

Ищет строку, которая содержит все, что находится в ячейке B8. Как вы узнали, число не является строкой. Это все еще не строка, если вы форматируете ее как текст. Вы можете проверить это, изменив значение в справочной таблице с 578 на '578 , теперь вы сможете найти его, введя 578 (или подмножество, например 78) в ячейку B8.

Если у вас нет возможности изменить все числа в таблице поиска, единственное решение, о котором я могу подумать, - это создать отдельный столбец, содержащий копию столбца A, преобразованного в строку. Например, в столбце K Листа 1 можно создать копию столбца A с добавленной пустой строкой. Введите =$A1&"" в ячейку K1 и скопируйте его вниз. Теперь измените ваш VLOOKUP на поиск столбца K вместо A.