Строка ВПР больше, чем параметры
Я пытаюсь искать строки на основе общих символов слева направо в Excel 2019
Если я ищу строки короче, чем значения поиска в столбце A, эта формула vlookup с подстановочным знаком в E2 работает
=VLOOKUP(D2&"*",A1:B5,2,FALSE)
Но как искать строку, которая больше, чем значения поиска?
Для ABCDE вывод будет H, так как
АВСДЕ
ABCDEFG
И вывод для ABCDEFGHIJKLM должен быть Y, так как ABCDEFGHIW — это строка, более похожая, чем ABCDEFGHIJKLM. Равных символов для соответствия слева направо будет не менее 4, если не отображается «не найдено»
ABCDEFGHIJKLM
ABCDEFGHIW
ABCDE потенциально может вернуть H или Y, поскольку он соответствует первым 5 символам как ABCDEFG, так и ABCDEFGHIW. Это порядок появления, количество несопоставленных символов в поиске или что-то еще, что определяет, что возвращается в случае такой ничьей?
Когда есть несколько вариантов с одинаковыми первыми символами, например, если я ищу ABCDE, а в столбце A есть варианты ABCDE12, ABCDE123, ABCDE1234567, то лучшим совпадением будет вариант с меньшей длиной, в этом примере будет ABCDE12. Если лучшие варианты имеют одинаковую длину, то ответом будет тот, который стоит первым в порядке появления
Ответы 2
Если вы всегда смотрите на совпадение левых символов, то:
Со старыми версиями:
=INDEX(VLOOKUP(LEFT(D5,LEN(D5)-ROW($ZZ$1:INDEX($ZZ:$ZZ,LEN(D5)))+1)&"*",A:B,2,FALSE),N(IF({1},SUM(--(ISERROR(VLOOKUP(LEFT(D5,LEN(D5)-ROW($ZZ$1:INDEX($ZZ:$ZZ,LEN(D5)))+1)&"*",A:B,2,FALSE))=TRUE))+1)))
Это может потребовать использования Ctrl-Shift-Enter вместо Enter при выходе из режима редактирования.
С Office 365
=LET(
lkp,D5,
lkprng,A:A,
otprng,B:B,
ar,XLOOKUP(
BYROW(SEQUENCE(LEN(lkp),,0),LAMBDA(a,LEFT(lkp,LEN(lkp)-a)))&"*",
lkprng,
otprng,
"",2),
TAKE(FILTER(ar,ar<>"",""),1))
Это повторяет удаление буквы из значения в D, возвращая все совпадения. Затем мы берем первую непустую строку.
Если совпадение может быть где угодно, нам нужно использовать Fuzzy Lookup:
https://www.microsoft.com/en-US/download/details.aspx?id=15011
@RasecMalkic см. редактирование. Возможно, вам придется обновить, чтобы увидеть его.
вы получили {} вокруг формулы, когда использовали Ctrl-Shift-Enter?
да, в виде матричной формулы
Теперь это работает очень хорошо. Единственная проблема, не связанная с вашей формулой, заключается в том, что мне нужно искать более 3000 строк в диапазоне, например A1: B5000, и он немного зависает для такой задачи.
Похоже, у вас есть ответ для vlookup, но если вы когда-нибудь получите 365, вы можете использовать xlookup, чтобы получить H для ABCDE:
=XLOOKUP(D2,A2:A5,B2:B5,,1)
Спасибо за вашу помощь
Пожалуйста. не размещайте вопросы с одинаковым содержанием только с измененным параметром.