☰

Extraia valores de um intervalo que está faltando em outro

tag-icon • tag-icon microsoft-excel vlookup array
Extraia valores de um intervalo que está faltando em outro

Tenho duas colunas no Excel, "ROSTER" e "PRESENT", mostradas abaixo:

imagem da coluna

Existe uma fórmula para atingir a coluna "NÃO AQUI"? Eu tentei usar VLOOKUP()ehttps://superuser.com/a/289653/135912sem sucesso =(

Qualquer ajuda seria apreciada!

Obrigado!

Responder1

Não há nenhuma função integrada que possa realizar essa tarefa sozinha.

Você pode tentar esta fórmula de matriz na coluna "Not Here" (MS Excel 2007+)

=IFERROR(INDEX(roster,SMALL(IF(COUNTIF(present,roster)=0,ROW()-1,""),ROW()-1),1),"")

Onde (no meu exemplo)
rosterestá um intervalo nomeado que se refere a $A$2:$A$21
presentum intervalo nomeado que se refere a$B$2:$B$21

Para inserir a fórmula, selecione as células na coluna Not Here (no meu caso éC2atéCapítulo 21), digite a fórmula e pressione Ctrl+ Shift+Enter

insira a descrição da imagem aqui

Responder2

Isso pode ser um pouco exagerado, mas funciona. Esperamos que você não se importe de ter uma coluna intermediária 'Aqui Não' com espaços, antes de chegar ao resultado final (Aqui Não 2).

Imagem da solução de trabalho

Por trás das cenas:

Intervalos nomeados em uso:

  • Lista: (B3:B19)
  • Presente: (C3:C19)
  • Aqui Não: (F3:F19)

Fórmula de matriz inserida no intervalo (D3:D19)...

{=IF(ISERROR(MATCH(Roster,Present,0)),Roster,"")}

Fórmulas de matriz inseridas em células (E3:E19)...

{=IFERROR(INDEX(NotHere,SMALL(IF(FREQUENCY(IF(NotHere<>"",MATCH(ROW(NotHere),ROW(NotHere)),""),MATCH(ROW(NotHere),ROW(NotHere)))>0,MATCH(ROW(NotHere),ROW(NotHere)),""),ROW(A1)),COLUMN(A1)),"")}

{=IFERROR(INDEX(NotHere,SMALL(IF(FREQUENCY(IF(NotHere<>"",MATCH(ROW(NotHere),ROW(NotHere)),""),MATCH(ROW(NotHere),ROW(NotHere)))>0,MATCH(ROW(NotHere),ROW(NotHere)),""),ROW(A2)),COLUMN(A2)),"")}

etc...

Embora pareça uma solução demorada, funcionará independentemente de onde a tabela for colocada na planilha. Ele também remove #numerros no Excel 2007, caso você esteja usando essa versão.

informação relacionada