Я пытался заставить это работать, предполагая, что:
- "Присоединенные компоненты" всегда находятся в одном столбце.
- Вы на самом деле пытаетесь найти описание для каждого «Материала №».
И я буду использовать этот лист для работы над:
Это может быть не совсем то, что вам нужно, но я могу попытаться улучшить мой ответ с вашими замечаниями по этому поводу.
Повторно использовав вашу формулу, чтобы определить, где находится "Прикрепленные компоненты" в столбце, а затем добавьте 2, вы получите относительную строку, где начинается описание материала:
=MATCH("Attached Components",B1:B32,0)+2
Результат в примере "7".
После вам нужно указать последний ряд, где находятся описания. Для поиска в правильном диапазоне необходимо изменить формулу в зависимости от того, какая строка "Присоединенные компоненты" расположена. Комбинация MATCH, ADDRESS, CONCATENATE воссоздает диапазон.
MATCH дает вам относительную строку, ADDRESS преобразует номер строки и номер столбца в строку с именем ячейки (ADDRESS (1,1)= "$ A $ 1"), CONCATENATE объединит строки, чтобы создать диапазон.
CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3))
Это возвращает строку типа "$ C $ 7:$ C $ 25". Таким образом, он охватывает столбец Description и начинается со строки, где у вас есть значения на 18 строк ниже. Чтобы покрыть больше или меньше строк, просто измените «+20» в формуле на соответствующее значение.
Поиск последней строки - это всего лишь поиск первой пустой ячейки с IF и MIN.
{=MIN(
IF(
INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3)))="",
ROW(INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3))))
)
)-1}
Эта формула является формулой массива. Вот почему у него есть квадратные скобки (не вводите квадратные скобки, они появляются, когда вы вводите формулу, а затем нажимаете Ctrl+Shift+Enter)
INDIRECT преобразует строку, которую мы встроили в ссылку на ячейку. ROW дает номер строки в результате. MIN примет наименьшее значение в возвращаемом диапазоне.
«-1» в конце должен иметь номер строки последнего описания, а не первую пустую строку.
В примере по этой формуле возвращается "9".
Теперь у нас есть номер строки первого и последнего описания от 7 до 9. Мы можем объединить эти числа так, как мы хотим, используя ADDRESS, CONCATENATE и INDIRECT для выполнения любой необходимой операции. Но на этот раз у вас есть конкретная ссылка на ячейку для работы.
Например поиск материала #:
В этом последнем примере ячейки содержат
E2:
=MATCH("Attached Components",B1:B32,0)+2
F2 (чтобы войти, используя Ctrl+Shift+Enter):
=MIN(
IF(
INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3)))="",
ROW(INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3))))
)
)-1
F7:
=VLOOKUP(E7,INDIRECT(CONCATENATE(ADDRESS(E2,1),":",ADDRESS(F2,3))),3,FALSE)
Таким образом, когда вы вводите Material # в ячейку E7, он отображает описание в ячейке F7.
РЕДАКТИРОВАТЬ:
После комментариев решение может быть разработано следующим образом:
Используя более сложный пример:
Сопоставление строк - это просто каскад функции 2 MATCH. Используя первую функцию MATCH, чтобы найти Part #, а затем вторую, чтобы найти интересующий раздел:
F3: строка части, которую вы ищете
F4: формула для поиска «Part #» в первом столбце.
=MATCH($F$3,A1:A32,0)
F6: название раздела, который вы ищете
F7: формула для поиска раздела в части, определенной ранее. Сопоставление выполняется в диапазоне, который начинается в строке «Part #» (хранится в ячейке F4). Диапазон строится по формуле того же типа, что и INDIRECT, CONCATENATE, ADDRESS. Затем относительная строка, возвращаемая MATCH, смещается на F4-1, чтобы получить абсолютный номер строки.
=MATCH($F$6,INDIRECT(CONCATENATE(ADDRESS(F4,2,1),":",ADDRESS(F4+20,2,1))),0)+F4-1
Теперь, чтобы идентифицировать первую и последнюю строки описания, мы можем использовать те же формулы, что и раньше:
F9: добавление 2 к номеру строки "Присоединенные компоненты", чтобы получить первую строку описания.
=F7+2
F10: поиск первой пустой строки в диапазоне описания (начиная со строки, сохраненной в F9). Это формула массива, которую необходимо ввести с помощью CTRL+SHIFT+ENTER
=MIN(
IF(
INDIRECT(CONCATENATE(ADDRESS($F$9,3),":",ADDRESS($F$9+20,3)))="",
ROW(INDIRECT(CONCATENATE(ADDRESS($F$9,3),":",ADDRESS($F$9+20,3))))
)
)-1
Затем для отображения описания мы можем использовать INDIRECT и столбец индекса:
F15:
=IF($F$9+$E15-1<=$F$10,INDIRECT(ADDRESS($F$9+$E15-1,1)),"")
G15:
=IF($F$9+$E15-1<=$F$10,INDIRECT(ADDRESS($F$9+$E15-1,3)),"")
В этих формулах отобразится № материала и описание строки, обозначенной индексом в столбце Е. Оператор IF должен убедиться, что мы не отображаем строки, которые находятся ниже последних строк. В этом примере отображается только 5 строк, но вы можете просто скопировать эту формулу, перетащив вниз первую строку и добавив новые индексы, чтобы получить больше.
