У меня возникли проблемы с XLOOKUP, запущенным на вычисляемом столбце:
В моей исходной таблице есть список телефонных номеров с типом столбца General, ввод как 1##########. Эти значения не вычисляются.
В моей таблице поиска есть тот же список телефонных номеров, отформатированный как (NNN)NNN-NNNN, затем я создал столбец для правильного форматирования телефонного номера для поиска:
="1" & SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E2,"(",""),")",""),"-","")," ",""),".","")
Это правильно создает совпадение, которое визуально соответствует моей исходной таблице (C) и таблице поиска (F).
Исходная таблица
Справочная таблица
Однако я не вижу совпадений при использовании этого XLOOKUP:
=XLOOKUP([@[Phone Number]],'Data'!F:F,'Data'!G:G,"No Match", 0,1)
В качестве простого теста, если я вручную введу те же самые данные в столбец H и изменю их, XLOOKUP сработает.
Я пробовал форматировать оба столбца как "текст" и "общий", но безуспешно. Есть ли в Excel способ сказать: "Эй, этот столбец - строка, считайте его таковым?"
решение1
Если правильно поняли, то кажется, что Phone NumbersвSource Tableотформатирован как число/общее, в то время как вLookup Tableон отформатирован как текст, поскольку дополнительный 1элемент объединен с помощью &--> Ampersand.
При дальнейшем исследовании вы можете обнаружить, что Excelпо умолчанию числа отображаются, right alignedа текст — нет left aligned, если вы не отформатировали выравнивание, но на снимке экрана это не так, как вы также подтвердили оSourceформатирование данных. Также при вводе числа, Excelесли оно не было отформатировано, оно будет отображаться как General.
Что тебе необходимо сделать?
Объединить empty string--> ""с [Lookup_Value]вSource Tableприменяя формулу, как показано ниже, которая должна вам подойти:
• Формула, используемая в ячейкеG4
=XLOOKUP(F4&"",B4:B8,C4:C8,"No Match",0,1)
Вышесказанное можно также записать так:
=XLOOKUP(F4:F5&"",B4:B8,C4:C8,"No Match",0,1)
В контексте ОП формула будет выглядеть следующим образом:
=XLOOKUP([@[Phone Number]]&"",'Data'!F:F,'Data'!G:G,"No Match", 0,1)
Кроме того, если у вас есть доступ к MS365, я настоятельно рекомендую вам использовать следующую формулу для Person Account Mobileвместо множественной SUBSTITUTE()функции вLookup_Table
=1&CONCAT(TEXTSPLIT(A4,{"(",")","-"," ","."},,1))
Если a double unary--> --или 1умножается или делится или 0добавляется к формуле выше, то [Lookup_Value]in XLOOKUP()не потребуется связываться с пустой строкой
Примечание:Указанные номера телефонов являются поддельными и созданы случайным образом. Пожалуйста, измените ссылку и диапазоны в соответствии с вашими предпочтениями.