Как индексировать совпадения вверх от указанной ячейки до непустого

Question

У меня есть данные в виде дерева каталогов (см. Рисунок)

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

В настоящее время у меня есть индекс, который точно указывает, что это за код, но я хочу настроить инструмент поиска (см. Нижнюю таблицу на рисунке ниже), который показывает мне все уровни сразу. На картинке я ввел значения, которые хочу вернуть, используя формулы.

Поэтому, если я введу код уровня 4, я хочу использовать формулы, которые будут возвращать программы уровня 4, 3, 2 и 1. Для кода уровня 3 я хочу, чтобы он возвращал программы уровня 3, 2 и 1.

Есть более 10000 строк данных.

Мне кажется, все так просто ... Начните с этой строки и смотрите вверх в этом столбце, пока не достигнете значения ..... Но, похоже, не все так просто.

Любая помощь / советы будут с благодарностью!

для -Alex-

Answer 1

Как вы заметили, Excel изначально не поддерживает «взгляд вверх». Такие функции, как index() или vlookup() , отлично подходят для поиска первого совпадения, но не последнего.

Предполагая, что ваши данные находятся в ячейках A1:F10, эта формула находит последнюю непустую ячейку в столбце A, начиная с кода (столбец D), введенного в ячейку H2. Его можно заполнить вправо (чтобы найти другие столбцы) и вниз (чтобы найти больше кодов). Это формула массива, которая должна быть подтверждена нажатием Ctrl + Shift + Enter.

=INDEX(A$2:A$10,MAX(IF(NOT(ISBLANK(OFFSET(A$2,,,MATCH($H2,$D$2:$D$10,0)))),ROW(OFFSET(A$2,,,MATCH($H2,$D$2:$D$10,0))),""))-1)

OFFSET(A$2,,,MATCH($H2,$D$2:$D$10,0)) используется для динамического изменения размера поискового массива так, чтобы он начинался с A2 и заканчивался строкой, в которой найден нужный код , Так что, если ваш код находится в D5, этот кусок возвращает A2:A5

MAX(IF(NOT(ISBLANK(...)),ROW(...),"")) Возвращает номер строки самой высокой непустой ячейки в диапазоне, который мы ранее нашли.

INDEX(A$2:A$10,...-1) и, наконец, index() находит фактическое значение ячейки, которую мы нашли. Мы вычитаем один, потому что диапазон поиска начинается со строки 2, тогда как счетчик строк, который мы используем, очевидно, начинается с 1.