Como pesquisar valor retornar vários valores correspondentes no Excel?
Este tutorial fala sobre como procurar um valor e retornar vários valores correspondentes no Excel, conforme mostrado nas capturas de tela abaixo:
O valor de pesquisa retorna vários valores correspondentes com fórmula de matriz
O valor de pesquisa retorna vários valores correspondentes com Filtro
O valor de pesquisa retorna vários valores correspondentes com função definida
O valor de pesquisa retorna vários valores correspondentes com fórmula de matriz
Aqui está uma fórmula de matriz longa que pode ajudar na procura de um valor e retornar vários valores correspondentes.
1. Insira o valor que você deseja pesquisar em uma célula em branco. Veja a imagem:
2. Na célula adjacente, digite esta fórmula =IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$D$4,ROW($A$1:$A$7)),ROW(1:1)),2)),"",
INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$D$4,ROW($A$1:$A$7)),ROW(1:1)),2)) nele, e pressione Shift + Ctrl + Enter juntas, em seguida, arraste o Preenchimento automático para preencher as células até que a primeira célula em branco saia. Veja a imagem:
Observação: na fórmula acima, $ A $ 1: $ B $ 7 indica o intervalo de dados, $ A $ 1: $ A $ 7 representa o intervalo da coluna em que sua pesquisa o determinado valor, $ D $ 4 indica a célula que você digita pesquisar valor na etapa 1, 2 indica encontrar os valores correspondentes na segunda coluna.
O valor de pesquisa retorna vários valores correspondentes com Filtro
No Excel, você também pode usar o recurso Filtro para resolver esse problema.
1. Selecione o intervalo da coluna para o qual deseja pesquisar o valor e clique em Data > filtros. Veja a imagem:
2. Em seguida, clique no botão de seta na primeira célula do intervalo selecionado e marque o valor que deseja consultar apenas na lista suspensa. Veja a imagem:
3. Clique OK, agora você vê apenas o valor de pesquisa e seus valores correspondentes são filtrados.
O valor de pesquisa retorna vários valores correspondentes com função definida
Se você estiver interessado em Função Definida, também poderá resolver o problema com Função Definida.
1. Pressione Alt + F11 chaves para abrir o Microsoft Visual Basic para aplicativos janela.
2. Clique Módulo > inserção inserir um Módulo janela e copie o VBA abaixo para a janela.
VBA: o valor de pesquisa retorna vários valores correspondentes.
Function MyVlookup(pWorkRng As Range, pRng As Range, pColumnIndex As Integer, Optional pType As String = "v")
'Updateby20140827
Dim xRow As Single
Dim xCol As Single
Dim arr() As Variant
ReDim arr(0)
For i = 1 To pRng.Rows.Count
If pWorkRng = pRng.Cells(i, 1) Then
arr(UBound(arr)) = pRng.Cells(i, pColumnIndex)
ReDim Preserve arr(UBound(arr) + 1)
End If
Next
If pType = "h" Then
xCol = Range(Application.Caller.Address).Columns.Count
For i = UBound(arr) To xCol
arr(UBound(arr)) = ""
ReDim Preserve arr(UBound(arr) + 1)
Next
ReDim Preserve arr(UBound(arr) - 1)
MyVlookup = arr
Else
xRow = Range(Application.Caller.Address).Rows.Count
For i = UBound(arr) To xRow
arr(UBound(arr)) = ""
ReDim Preserve arr(UBound(arr) + 1)
Next
ReDim Preserve arr(UBound(arr) - 1)
MyVlookup = Application.WorksheetFunction.Transpose(arr)
End If
End Function
3. Feche a janela e digite esta fórmula em uma célula = MyVlookup (A10, $ A $ 2: $ B $ 7,2) (A10 indica o valor de pesquisa, $ A $ 2: $ B $ 7 indica o intervalo de dados, 2 indica o número do índice da coluna). E pressione Shift + Ctrl + Enter chaves. Em seguida, arraste a alça de preenchimento para baixo das células, coloque o cursor no Barra de Fórmulae pressione Shift + Ctrl + Enter novamente.
Dica: Se você deseja retornar os valores em células horizontais, você pode digitar esta fórmula = MyVlookup (A10, $ A $ 2: $ B $ 7, 2, "h").
Melhores ferramentas de produtividade de escritório
Aprimore suas habilidades de Excel com o Kutools para Excel e experimente uma eficiência como nunca antes. Kutools para Excel oferece mais de 300 recursos avançados para aumentar a produtividade e economizar tempo. Clique aqui para obter o recurso que você mais precisa...
Office Tab traz interface com guias para o Office e torna seu trabalho muito mais fácil
- Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
- Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!