Поиск частичного текста в диапазоне, возврат индекса

Мне нужно найти частичный текст в определенном диапазоне и получить значение, которое представляет собой X строк по индексу ячейки найденного текста. Я пробовал функции INDEX и MATCH, но безуспешно.

Как в примере: поиск частичного текста ASDFGHJK и нужно вернуть значение три строки под: (I NEED THIS VALUE). Я попытался получить индекс строки найденной ячейки и +3, чтобы получить необходимое значение ( "Мне НУЖЕН ЭТОТ ЗНАЧЕНИЕ" ), но безуспешно.

Любые идеи, как это сделать?

Ответы

Ответ 1

Вы можете использовать "подстановочные знаки" с MATCH, поэтому предполагая "ASDFGHJK" в H1 согласно ответу Питера, вы можете использовать эту обычную формулу

=INDEX(G:G,MATCH("*"&H1&"*",G:G,0)+3)

MATCH может ссылаться только на один столбец или строку, поэтому, если вы хотите искать 6 столбцов, вам нужно либо настроить формулу с 6 функциями MATCH, либо изменить другой подход - попробуйте эту формулу массива, предполагая, что данные поиска в A2: G100

=INDIRECT("R"&REPLACE(TEXT(MIN(IF(ISNUMBER(SEARCH(H1,A2:G100)),(ROW(A2:G100)+3)*1000+COLUMN(A2:G100))),"000000"),4,0,"C"),FALSE)

подтверждено с помощью Ctrl - Shift - Enter

Ответ 2

Эта формула выполнит задание:

=INDEX(G:G,MATCH(FALSE,ISERROR(SEARCH(H1,G:G)),0)+3)

вам нужно ввести его в виде формулы массива, т.е. нажать Ctrl - Shift - Enter. Предполагается, что подстрока, которую вы ищете, находится в ячейке H1.

Ответ 3

Я только что нашел это, когда googling решил ту же проблему, и должен был внести незначительные изменения в решение, чтобы заставить его работать в моей ситуации, так как у меня было 2 похожих подстроки: "Sun" и "Sunstruck" для поиска, Предлагаемое решение заключалось в поиске неверной записи при поиске "Солнца". Данные в столбце B

Я добавил еще один столбец C, формулы C1 = "& B1 &" "и изменил поиск на = COUNTIF (B1: B10," * "&" *") > 0, дополнительный столбец для разрешить поиск первой последней записи в конкатенированной строке.