Существует ли формула Google Sheets (или Excel), такая как HLOOKUP(), но она будет выполнять поиск в двумерном диапазоне, а не в одной строке?

У меня есть электронная таблица Google Sheets, и я надеюсь написать формулу, которая находит местоположение заданной фразы в любом месте электронной таблицы, а затем возвращает значение ячейки на определенное количество ячеек ниже искомой ячейки. Например, если я ищу значение «15.11.2022», а эта ячейка — C4, я хотел бы вернуть значение ячейки C6. Я пытался использовать HLOOKUP(), но это ограничивает диапазон поиска одной строкой, и мне нужно иметь возможность искать в любом месте электронной таблицы (и данные имеют измерения, которые оба больше единицы).

Есть ли функция (Excel или Google Sheets), которая будет выполнять это? Любая помощь высоко ценится!

поделитесь копией / образцом вашего листа с примером желаемого результата

player0 16.11.2022 02:04
Преобразование HTML-таблицы в профессиональный документ Excel
Преобразование HTML-таблицы в профессиональный документ Excel
Это самый простой способ создания Excel из HTML-таблицы.
Импорт excel в laravel в базу данных
Импорт excel в laravel в базу данных
Здравствуйте, дорогой читатель, в этой статье я расскажу практическим и быстрым способом, как импортировать файл Excel в вашу базу данных с помощью...
1
1
82
3
Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

Ответ принят как подходящий

пытаться:

=INDEX(TEXTJOIN(, 1, IF(B1:F10=A1, B3:F12, )))

Для Excel аналогичная идея также работает. Вы можете проверить ответ @bosco_yip на этот вопрос: Как искать несколько столбцов в Excel. Отличное решение player0

David Leal 16.11.2022 06:43

Вот соответствующий подход, эквивалентный Excel:=LET(rng, B1:F10, TEXTJOIN(", ",,IF(rng=A1,IFERROR(OFFSET(rng, 2,0),"Out of range"),""))). Добавлена ​​проверка для предотвращения выхода за пределы диапазона. Стоит отметить, что такой подход работает только для положительных смещений по строке или столбцу. Если найдено более одного результата, он возвращает результат в виде значений, разделенных запятыми.

David Leal 16.11.2022 16:48

Предоставьте этот код с вашим значением поиска, значениями смещения строки и смещения столбца в H1: H3,

результаты поиска будут показаны в столбце K.

Если имеется более одного совпадения с искомым значением, все результаты будут вынесены в столбец K. (например, если имеется 3 совпадения с заданным искомым значением, но после применения смещений имеется более 1 значения результатов, все уникальные результаты будут возвращены в виде массива в столбце K).

=LAMBDA(LOOKUPVALUE,ROWOFFSET,COLOFFSET,DATA,
 LAMBDA(ROWA,COLA,
  LAMBDA(COLLEN,ROWLEN,
   UNIQUE(FLATTEN(MAKEARRAY(ROWLEN,COLLEN,LAMBDA(ROW,COL,
    LAMBDA(CELLVALUE,
     IF(CELLVALUE=LOOKUPVALUE,INDEX(DATA,ROW+ROWOFFSET,COL+COLOFFSET),"")
    )(INDEX(DATA,ROW,COL))
   ))),FALSE,TRUE)
  )(COUNTBLANK(ROWA)+COUNTA(ROWA),COUNTBLANK(COLA)+COUNTA(COLA))
 )(INDEX(DATA,1),INDEX(DATA,,1))
)($H$1,$H$2,$H$3,$A$1:$F)

Для Excel используйте образец данных, предоставленный ответом @Ping. В cel H2 можно поместить следующую формулу:

=LET(rng, A1:E5, n, ROWS(rng), m, COLUMNS(rng), lookup, G1,rOffset, G2, cOffSet, G3, 
  match, IF(rng=lookup,1,0), NF, "Not Found",
  IF(MAX(match) = 0, NF, LET(rows, MAKEARRAY(n, m, LAMBDA(r,c,r)),
    cols, MAKEARRAY(n, m, LAMBDA(r,c,c)), found, MAP(rows, cols, match,
    LAMBDA(r,c,m, IF(m = 1,IFERROR(INDEX(rng,r + rOffset,c + cOffSet), NF),""))),
    TEXTSPLIT(TEXTJOIN(",",, found),, ",")))
)

и вот соответствующий вывод:

Примечание. Это решение может находить значения назад по строке и/или столбцу, а также вперед (положительные или отрицательные значения смещения для строки и/или столбца). Поскольку он сначала находит результат, он возвращает соответствующее значение смещения.

match имя — это [0,1] массив той же формы, что и rng, который устанавливается в 1, если найдено значение lookup, иначе 0.

Мы используем MAKEARRAY для хранения строк и столбцов rng. Имена rows, cols представляют собой массивы той же формы, что и rng, со строками и столбцами соответственно.

Имя found:

MAP(rows, cols, match, LAMBDA(r,c,m, IF(m = 1,
  IFERROR(INDEX(rng,r + rOffset,c + cOffSet), NF),"")))

Возвращает массив той же формы, что и rng, с пустой ячейкой, где нет соответствия, или соответствующее значение из rng с учетом смещения строки и столбца.

Теперь нам просто нужно удалить пустые ячейки. Мы используем TEXTJOIN для этого. Наконец, мы используем TEXTSPLIT, чтобы вернуть результат в формате столбца массива.

Этот подход рассматривает следующие сценарии неудовлетворительного пути. Он возвращает Not Found (его можно настроить на другое значение):

  • Значение lookup не найдено
  • Значения смещения создают значение вне входного диапазона

Другие вопросы по теме