Excel: ищет данные из строки 1, но возвращает данные на 3 строки ниже формулы

Question

У меня проблемы с придумыванием формулы, которая мне нужна, я ищу помощи, чтобы посмотреть, поможет ли мне кто-нибудь из вас. Я приложил снимок экрана с таблицей и "формулу".

на другом листе я хочу получить данные из таблицы ниже с конечным балансом Тома столбца B в одной ячейке и столбца C в другом конечном остатке Джеффа столбца B в одной ячейке и столбца C в другой

«Я использовал = INDEX(B1:B10, MATCH($ D $ 1, $ A $ 1:$ A $ 10,0)+3)» в E1, и он работал для TOM, но он не работает для JEFF. Мне нужна помощь с Джеффом, но это также должно работать и для TOM.

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

Таблица

       A             B      C      D      E
1     TOM                          TOM    13
2     beg bal         11     200 
3     act             2      20 
4     end bal         13     220
5     JEFF
6     beg bal         25     154
7     ---------------------------------------
8     act             100     45
9     ---------------------------------------
10    end bal         125     200

Answer 1

Обновление для разных мест и мест для End Balance и других

=INDEX($A$1:$D$10, SMALL(IF("end bal"=$A$1:$A$10, ROW($A$1:$A$10),""),ROW(1:1)), COLUMN(B1))
Просто нажмите Ctrl + Shift + Enter одновременно с написанием формулы (формулы массива)

A1:F10 - это целые данные, где бы они ни начинались
Колонка для компаний и Баланс
B столбец для стоимости
С колонка для дивидендов

D колонка для Тома ... где вы хотите получить результат
Начните с первого (Tom, JEFF ...), для которого вы хотите, чтобы конечный баланс соответствовал исходным данным (вы можете скопировать столбец A и удалить дубликаты и ненужные данные)
Например в D1 напиши Tom, D2 напиши JEFF ....
В E1 напишите формулу, и вы можете перетащить ее в той же строке и вниз в том же столбце
Он находит самый маленький ряд (конец бал) Том (маленький, 1) ДЖЕФФ (маленький, 2) ...

Другой заказ для JEFF, TOM ...

Если порядок не совпадает с исходными данными
Например, создайте вспомогательный столбец в D и напишите в нем следующее:
=1+COUNTIF($A$1:A2,"end bal") в D2 и перетаскивая его вниз, он будет писать число в той же строке имени (это число вместо строки (1:1) в маленьком
Новая формула станет:
=INDEX($A$1:$D$10,SMALL(IF("end bal"=$A$1:$A$10, ROW($A$1:$A$10),""),VLOOKUP(E2,$A$1:$F$23,4,FALSE)), COLUMN(B1))
Формула массива нажмите Ctrl + Shift + Enter одновременно
Vlookup найдет число, созданное счетчиком, которое соответствует имени в E2
4 - столбец D, в котором написано число