Найти частичный текст в Excel 2013

Question

В Excel 2013 я хотел бы найти значение в столбце B в строке, где строка соответствует строке в столбце A. Что усложняет то, что строки в столбце A могут быть короче, чем искомая строка значения. Пример:

Column A    Column B
ABCD-       Result1
EF          Result2
BCD         Result3

Теперь примеры строк, которые должны возвращаться ...

"Result1": "ABCD-", "ABCD-EFG", "ABCD-H"
"Result2": "EF", "EFG", "EFGHIIJKL"
"Result3": "BCD", "BCDXY"

"ABCD" не должен возвращать никакого результата.

Предположим, искомая строка находится в ячейке C1.

Нужно ли прибегать к макросу или есть формула для этого?

UPD: будет несколько искомых строк, которые на самом деле будут находиться в столбце на другом листе, и в начале не будет подстановочных знаков. Значение, которое я ищу, может быть помещено в столбец рядом со столбцом с искомыми строками.

UPD2: предположим, что содержимое столбца A и столбца B на листе 1 и содержимое на листе 2 ниже:

SoughtString    ResultFound
ABCD-           Result1
EF              Result2
BCD             Result3
BCDXY           Result3
EFG             Result2
ABCD-EFG        Result1
EFGHIIJKL       Result2
ABCD-H          Result1
ABCD            No match

Скопировав слегка измененную формулу массива из принятого ответа в ячейку B2:

=IFERROR(INDEX(Sheet1!B$2:B$4;MATCH(1;COUNTIF(A2;Sheet1!A$2:A$4&"*")*(Sheet1!A$2:A$4<>"");0));"No match")

Answer 1

Не ясно, какую роль играет С1. Если искомые строки уже существуют в столбце, вы можете использовать это как то, что вы показываете как Col A Листа 2. Если они где-то еще, вы можете построить Col A, просто ссылаясь на соответствующую ячейку в другом списке. Например, если список находится в столбце Z, начиная со строки 2, A2 будет =Z2 , и вы можете скопировать эту формулу в столбец A, чтобы заранее заполнить больше ячеек, чем вам нужно (они останутся пустыми, пока не понадобятся).

Если список искомых строк создается по одной новой записи за раз, вы можете просто поместить новые записи в следующую позицию в любом списке (столбец Z или столбец A). Если новые записи должны идти в C1, и каждая новая запись заменяет предыдущую, вам понадобится программа макроса или VBA для построения списка из записей C1.

Начиная с того момента, когда искомые строки будут в списке в столбце A любым из этих способов, это то, что будет указано в столбце B. Предположим, Result1 находится в B2. Формула в B2 будет иметь вид:

    =IF(ISERROR(FIND(Sheet1!A2,A2)),"NO MATCH",IF(FIND(Sheet1!A2,A2)=1,A2,"NO MATCH"))

Скопируйте формулу вниз столбец B столько, сколько вам нужно. Вы можете предварительно заполнить Col B произвольным большим количеством записей, и они останутся пустыми до тех пор, пока в соответствующей ячейке в Col A не будет указано значение, дающее результат.

Answer 2

Предполагая, что у вас есть данные в строках со 2 по 10, вы можете использовать эту "формулу массива"

=IFERROR(INDEX(B$2:B$10,MATCH(1,COUNTIF(C1,A$2:A$10&"*")*(A$2:A$10<>""),0)),"No match")

подтверждено с помощью CTRL+SHIFT+ENTER