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

Question

Мне нужна помощь в написании формулы, которая делает следующее:

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

В качестве примера:

COLUMN   A      B   C   D       E       F   G   H       I       J   K   L   M
         Row    Indicators      |       Matched Key     |       Key Counts          
           3    U1  U2  U3      |       U1  U2  U3      |       A   B   C   D
           4    1   0   1       |       B   N/A D       |       0   1   0   1   
           5    1   1   1       |       A   A   D       |       2   0   0   2  

Формула, которую мне нужно написать, предназначена для раздела "Соответствующий ключ".

Чтение строки 4 слева:

• У U1 и U3 оба есть индикаторы, поэтому я беру в таблицу Key Counts, чтобы найти, что у меня есть 1 B и один D
• U1 в "Matched Key" получает "B" в качестве своего ключа, а U3 в "Matched Key" получает D. U2 не присвоено значение, так как его индикатор установлен на 0.

В строке 5:

• U1, U2 и U3 требуют назначения клавиш.
• Я смотрю в "Подсчет баллов" и вижу, что у меня есть 2 A и 1 D.
• U1 и U2 получают A, а U3 получает один из D (будет один остаток).

Answer 1

Я смог ответить на свой вопрос, используя формулы массива (эта формула для ячейки H4):

{=IF(D4=1,OFFSET($I$2,0,COLUMN(INDEX(OFFSET($J$4,0, ( (MIN(IF((J4:M4>=1) *(J4:M4<>0)*(O4:R4>=SUM(B4:D4)),COLUMN(J4:M4)))-1))-COLUMN($I$2) ):$N$4,MATCH(TRUE,OFFSET($J$4,0,  ( (MIN(IF((J4:M4>=1) *(J4:M4<>0)*(O4:R4>=SUM(B4:D4)),COLUMN(J4:M4)))-1))-COLUMN($I$2)  ):$N$4>=1,0)))-COLUMN($I$4)),NA())}

Мне пришлось добавить еще одну "матрицу", которая предназначена для кумулятивного счета (которая находится в диапазоне O4:R5):

COLUMN  N     O    P   Q    R
        Row   Cumulative Counts         
          3    A    B   C   D
          4    1    2   4   4
          5    2    2   2   3

Другие способы снятия кожи с этого кота, но это работает для меня.