Найдите имя в таблице и получите оценку из 7 столбцов вниз, основываясь на последнем местоположении, в котором было найдено имя

Question

Вот изображение листа, над которым я работаю

Для столбца T2: TI я пытаюсь создать формулу, которая ищет в таблице 1 A1: O последнюю игру, в которую играл игрок, и получает свой счет, который всегда будет на 7 столбцов ниже, чем у победителя и проигравшего, и если последняя игра отсутствует найденный столбец T будет просто равен столбцам W для этого игрока. Пожалуйста, дайте мне знать, если мой вопрос неясен или вы не видите мою фотографию. Сейчас я пытаюсь подобную формулу

=Index(Table1,Match(Q2,D:E,0),0),7) 

чтобы найти имена в Q2:Q в последнем ряду таблицы1 A1:O, и как только оно найдет это имя, спуститесь на 7 столбцов вниз и получите счет, и если имя или счет не найдены, T будет равняться начальным очкам игроков, которые находится в W.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
       A       |       D       |       E       |       M       |       N       |
--------------------------------------------------------------------------------
   Match ID    |     Winner    |      Loser    | WinnerOutcome | LoserOutcome  |                              
--------------------------------------------------------------------------------
       1       |     Kamau     |     Rodknee   |      100      |      150      |                              
--------------------------------------------------------------------------------
       2       |    Rodknee    |      Kamau    |      200      |      50       |                              
--------------------------------------------------------------------------------

                                  Table2
------------------------------------------------
       Q       |       T       |       W       |  
------------------------------------------------                         
   Players     |    Points     |Starter Points |                             
------------------------------------------------
     Kamau     |       ?       |       50      |                               
------------------------------------------------
    Rodknee    |       ?       |       200     | 
------------------------------------------------
      Jim      |       ?       |       50      |                                 
-------------------------------------------------

Answer 1

Поэтому я хотел бы создать вспомогательные столбцы стандартной формулы, чтобы объединить их в порядке. Я поместил это в Y2:

=INDEX(D:E,QUOTIENT(ROW(1:1)-1,2)+2,MOD(ROW(1:1)-1,2)+1)

И это в Z2:

=INDEX(M:N,QUOTIENT(ROW(1:1)-1,2)+2,MOD(ROW(1:1)-1,2)+1)

И скопируйте так, как вы хотите, убедитесь, что это достаточно далеко, чтобы охватить все возможные объемы данных.

Затем в S2 мы используем эту формулу массива:

=IFERROR(INDEX($Z$2:INDEX(Z:Z,MATCH("ZZZ",Y:Y)),MATCH(2,IF($Y$2:INDEX(Y:Y,MATCH("ZZZ",Y:Y))=P2,1))),V2)

Будучи формулой массива, мы должны отметить две вещи:

1. При вводе формулы массива необходимо подтвердить с помощью Ctrl-Shift-Enter при выходе из режима редактирования вместо Enter. Если все сделано правильно, Excel поместит {} вокруг формулы.

2. Формула массива вычисляется экспоненциально, поэтому мы хотим ограничить ссылки только на те ячейки, в которых есть данные. В приведенной выше формуле мы делаем это с помощью $Z$2:INDEX(Z:Z,MATCH("ZZZ",Y:Y)) который устанавливает первую ячейку как Z2, а последнюю - как последнюю ячейку в Z, которая находится в строка с фамилией в Y.

---

Если вы действительно хотите это в одной формуле, то это сделает это:

=IFERROR(IF(INDEX(D:D,IF(MAX(INDEX((ROW($D$2:$E$3))*($D$2:$E$3=P2),))=0,-1,MAX(INDEX((ROW($D$2:$E$3))*($D$2:$E$3=P2),))))=P2,INDEX(M:M,MAX(INDEX((ROW($D$2:$E$3))*($D$2:$E$3=P2),))),INDEX(N:N,MAX(INDEX((ROW($D$2:$E$3))*($D$2:$E$3=P2),)))),V2)

Или этот работает также:

=IFERROR(INDEX(M:N,MAX(INDEX((ROW($D$2:$E$3))*($D$2:$E$3=P2),)),IF(INDEX(D:D,IF(MAX(INDEX((ROW($D$2:$E$3))*($D$2:$E$3=P2),))=0,-1,MAX(INDEX((ROW($D$2:$E$3))*($D$2:$E$3=P2),))))=P2,1,2)),V2)

Хотя он вводится нормально, это все еще формулы массива, и $D$2:$E$3 должны быть экстентами данных. Вы можете заменить все $E$3 на INDEX(E:E,MATCH("ZZZ",E:E)) или просто использовать ссылку на таблицу для этих двух столбцов. Но в любом случае эта ссылка должна быть ограничена экстентами данных, а не полным столбцом.