¿Cómo buscar un valor y devolver varios valores correspondientes en Excel?

En este tutorial se explica cómo buscar un valor y devolver varios valores correspondientes en Excel, tal como se muestra en las siguientes capturas de pantalla:

Buscar un valor y devolver varios valores correspondientes con una fórmula de matriz

Buscar un valor y devolver varios valores correspondientes con Filtro

Buscar un valor y devolver varios valores correspondientes con una Función Definida

Buscar un valor y devolver varios valores correspondientes con una fórmula de matriz

Existe una fórmula de matriz extensa que puede ayudarte a buscar un valor y devolver varios valores correspondientes.

1. Escribe el valor que deseas buscar en una celda en blanco. Consulta la captura de pantalla:

2. En la celda adyacente, introduce 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)) en ella y pulsa Shift + Ctrl + Enter al mismo tiempo, luego arrastra el Relleno automático para rellenar las celdas hacia abajo hasta que aparezca la primera celda en blanco. Consulta la captura de pantalla:

Nota: En la fórmula anterior, $A$1:$B$7 indica el área de datos, $A$1:$A$7 representa la columna donde se busca el valor, $D$4 es la celda donde escribiste el valor a buscar en el paso1, y2 indica que se buscan los valores correspondientes en la segunda columna.

Buscar un valor y devolver varios valores correspondientes con Filtro

En Excel, también puedes utilizar la función Filtro para resolver este problema.

1. Selecciona la columna en la que deseas buscar el valor y haz clic en Datos > Filtro. Consulta la captura de pantalla:

2. Luego haz clic en el botón de flecha en la primera celda del rango seleccionado y marca solo el valor que deseas buscar en la lista desplegable. Consulta la captura de pantalla:

3. Haz clic en OK. Ahora verás que solo el valor buscado y sus valores correspondientes han sido filtrados.

Buscar un valor y devolver varios valores correspondientes con una Función Definida

Si te interesa utilizar una Función Definida, también puedes resolver el problema con este método.

1. Pulsa las teclas Alt + F11 para abrir la ventana de Microsoft Visual Basic For Applications.

2. Haz clic en Módulo > Insertar para abrir una ventana deMódulo e inserta el siguiente código VBA en la ventana.

VBA: Buscar un valor y devolver varios valores correspondientes.

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. Cierra la ventana e introduce esta fórmula en una celda =MyVlookup(A10,$A$2:$B$7,2) (A10 indica el valor a buscar, $A$2:$B$7 el área de datos, y2 el número de índice de la columna). Pulsa las teclas Shift + Ctrl + Enter. Luego arrastra el controlador de relleno hacia abajo, coloca el cursor en la Barra de fórmulas y vuelve a pulsar Shift + Ctrl + Enter.

Consejo: Si quieres devolver los valores en celdas horizontales, puedes usar esta fórmula =MyVlookup(A10, $A$2:$B$7,2, "h").