Excel: fórmulas MATCH () sem correspondência de curinga

Question

Atualmente, uso MATCH() nas colunas de fórmulas do Excel 2016, como:

= MATCH( [@[ITEM_CODE]], IM_PURCH_VENDOR_SUBLIST[[#All],[ITEM-CODE]], 0 )

O resultado é essencialmente uma chave estrangeira. Ele é usado em outras colunas com INDEX() ou OFFSET() expressões para relacionar a linha atual com a linha correspondente na outra tabela.

Alguns valores de pesquisa contêm pontuação, incluindo os caracteres curinga especiais * , ? e ~ . Isso causa uma correspondência não intencional. Um exemplo real é o código do item *XA1 , que coincide involuntariamente com o código do item 1SC0021REXA1 na outra tabela.

Minha solução atual para o exemplo acima é:

= MATCH(
SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( [@[ITEM_CODE]], "~", "~~" ), "*", "~*" ), "?", "~?" ),
IM_PURCH_VENDOR_SUBLIST[[#All],[ITEM-CODE]],
0
)

O que não gosto? É fácil esquecer ao escrever INDEX(MATCH()) expressões, é fácil errar (a ordem é importante), e simplesmente desajeitado e difícil de ler. Mas eu não encontrei uma alternativa MATCH() não-curinga, ou mesmo uma maneira de simplificar a expressão de substituição, sem criar mais complexidade em outro lugar, como escrever uma função personalizada no VBA.

    

Answer 1

Você pode usar agregado:

=AGGREGATE(15,6,(ROW(IM_PURCH_VENDOR_SUBLIST[[#All],[ITEM-CODE]])-MIN(ROW(IM_PURCH_VENDOR_SUBLIST[[#All],[ITEM-CODE]]))+1)/(IM_PURCH_VENDOR_SUBLIST[[#All],[ITEM-CODE]]=[@[ITEM_CODE]]),1)

OuvocêpodeusaressaversãodamatrizdaMATCH:

=MATCH(TRUE,[ITEM_CODE]=IM_PURCH_VENDOR_SUBLIST[[#All],[ITEM-CODE]],0)

Sendoumafórmuladematriz,elaprecisaserconfirmadacomCtrl-Shift-EnteraosairdomodoEditar.

Este é o mesmo que o segundo, mas não requer a necessidade de CSE:

=MATCH(TRUE,INDEX([ITEM_CODE]=IM_PURCH_VENDOR_SUBLIST[[#All],[ITEM-CODE]],),0)