Fórmula do Excel aprimorado Vlookup

Question

Estou procurando uma fórmula do Excel que eu possa usar para preencher automaticamente o E2 na captura de tela abaixo. A intenção é encontrar a idade de Marc na coluna B, ou seja, E2 deve ser 20. Alguma idéia, por favor? Eu tentei o Match, o VLookup e várias outras funções sem sorte.

    

Answer 1

Isso quase funciona:

{=INDEX(B$2:B$3, MATCH(1, (SEARCH(D2, A$2:A$3)>0)*1, 0), 1)}

Ou seja, procure por D2 em cada célula do intervalo A, retornando 1 para cada célula onde ela for encontrada, combine exatamente com 1 para retornar as linhas bem-sucedidas, depois indexe no intervalo B usando a linha retornada e obtenha o idade da primeira coluna nesse intervalo. Isso pressupõe que a correspondência retornará apenas uma linha. Use Ctrl-Shift-Enter para obter o {} modo de matriz de indicação.

Como o nome que procuramos pode ser um prefixo de outro nome, devemos procurar por um caractere len (name) de quebra de palavra (espaço ou fim da string) após o local em que foi encontrado:

{=INDEX(B$2:B$3,
        MATCH(0,
              LEN(SUBSTITUTE(MID(A$2:A$3,LEN(D2)+SEARCH(D2,A$2:A$3),1),
                             " ",
                             "")),
              0),
        1)}

Devemos procurar uma quebra de palavra no início também:

{=INDEX(B$2:B$3,MATCH(0,
   LEN(SUBSTITUTE(IFERROR(MID(A$2:A$3,SEARCH(D2,A$2:A$3)-1,1)," ")," ",""))+
   LEN(SUBSTITUTE(MID(A$2:A$3,LEN(D2)+SEARCH(D2,A$2:A$3),1),       " ","")),
   0),1)}

    

Answer 2

Desde que você pediu uma resposta do VBA também. Eu configurei uma amostra como a sua:

Usandoamacroaseguir,escrevi:

PublicSubdo_stuff_and_things()DimiAsIntegerDimname_to_findAsStringDimageAsStringname_to_find=Range("'Sheet1'!D2").Value


age = "not found"

i = 2

Do Until i = Range("'Sheet1'!A1").End(xlDown).Row + 1

 If InStr(Range("'Sheet1'!A" & i).Value, name_to_find) Then
 age = Range("'Sheet1'!B" & i).Value
 Exit Do
 End If

 i = i + 1
Loop


Range("'Sheet1'!E2").Value = age
End Sub

Ele simplesmente usa a função InStr para ver se o campo contém seu valor de pesquisa. É claro que isso exige que o nome seja digitado exatamente como é na coluna de pesquisa.

Espero que isso ajude.

Se você precisar de ajuda para implementar isso, deixe-me saber.

    

Answer 3

Você pode usar o vlookup com curingas:

= VLOOKUP ("*" & D2 & "*", $ A $ 2: $ B $ 3,2, FALSE)

Onde D2 é a célula que você deseja pesquisar (que também pode ser codificada como "Marc"), $ A $ 2: $ B $ 3 é o seu intervalo, que contém $ na frente de cada coluna e linha de referência, que será Torne o intervalo estático, porque você provavelmente não deseja que seu intervalo seja alterado se estiver arrastando essa fórmula para uma coluna.

2 é o índice da coluna que você gostaria de recuperar dentro do seu intervalo e false representa que esta é uma aproximação, que quando usada em conjunto com o seu curinga *, irá procurar qualquer valor com Marc nela. xyzMarc123 retornará um valor.