Como atribuo uma chave com base na contagem de chaves disponíveis e no valor do indicador?

Question

Preciso de ajuda para escrever uma fórmula que faça o seguinte:

1. Olha para ver se um indicador = 1
2. Se indicator = 1, pesquise o valor da chave com base no número disponível para essa chave específica
3. Continue pesquisando valores adicionais, usando o histórico das chaves usadas para essa linha específica
4. Redefinir e repetir para a próxima linha

Como exemplo:

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  

A fórmula que preciso escrever é para a seção "Chave correspondente".

Lendo a linha 4 da esquerda:

• U1 e U3 têm indicadores, por isso, chego à tabela de contagens de chaves ao descobrir que tenho 1 B e 1 D
• U1 em "Matched Key" obtém "B" como chave e U3 em "Matched Key" obtém D. U2 não recebe nenhum valor porque seu indicador está definido como 0.

Na linha 5:

• U1, U2 e U3 exigem atribuição de chave.
• Eu olho em "Key Counts" e vejo que tenho 2 A e 1 D.
• U1 e U2 recebem os A e U3 recebem um dos Ds (haverá um restante).

Answer 1

Consegui responder minha própria pergunta usando fórmulas de matriz (essa fórmula é para a célula 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())}

Eu tive que adicionar mais uma "matriz" que é para contagens cumulativas (que está no intervalo 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

Outras maneiras de esfolar esse gato, mas isso funciona para mim.