Autor: Oscar Cronquist Artículo actualizado en mayo 26, 2020

Imagen de cómo extraer múltiples valores basados ​​en una condición utilizando una fórmula matricial

La función BUSCARV está diseñada para devolver solo un valor correspondiente de la primera instancia de un valor de búsqueda, desde una columna que elija. Pero hay una solución alternativa para identificar múltiples coincidencias.

Las fórmulas de matriz que se muestran a continuación son más pequeñas y fáciles de entender y solucionar problemas que las útil función BUSCARV.

Sin embargo, no está limitado a las fórmulas de matriz, Excel también tiene funciones integradas que funcionan muy bien, se sorprenderá de lo fácil que es valores de filtro en un conjunto de datos.

Indice de contenido

  1. BUSCARV - Devuelve múltiples valores [verticalmente]
  2. BUSCARV - Devuelve múltiples valores [horizontalmente]
  3. BUSCARV - Extraer múltiples registros en función de una condición
  4. Buscar y devolver múltiples valores [Autofiltro]
  5. Buscar y devolver múltiples valores [Filtro avanzado]
  6. Buscar y devolver múltiples valores [Tabla definida por Excel]
  7. Devuelve múltiples valores vertical u horizontalmente [UDF]
  8. Cómo contar los resultados de VLOOKUP
  9. Buscar y devolver múltiples valores en una celda

He hecho una fórmula, demostrada en un artículo separado, que te permite BUSCARV y devolver múltiples valores en las hojas de trabajoTambién hay un complemento que facilita aún más la realización de esta tarea.

Ahora, si solo necesita una instancia de cada valor devuelto, revise este artículo: Vlookup - Devuelve múltiples valores distintos únicos Le permite especificar una condición y la fórmula ni siquiera es una fórmula de matriz.

También he escrito un artículo sobre buscando una cadena (búsqueda con comodines) y devuelve los valores correspondientes, requiere una fórmula algo más complicada, pero no te preocupes, también encontrarás una explicación allí.

¿Sabías que también es posible BUSCARV y devolver múltiples valores distribuidos en varias columnas, la fórmula incluso ignora los espacios en blanco.

BUSCARV - Devuelve múltiples valores verticalmente

Imagen de cómo extraer múltiples valores basados ​​en una condición utilizando una fórmula matricial

¿VLOOKUP puede devolver múltiples valores? Puede, sin embargo, la fórmula sería enorme si tuviera que contener la Función VLOOKUP. La fórmula presentada aquí no contiene esa función, sin embargo, es más versátil y más pequeña.

La imagen de arriba muestra una fórmula de matriz que extrae valores adyacentes basados ​​en un valor de búsqueda en la celda D10.

Otra gran cosa con esta fórmula de matriz es que le permite buscar y devolver valores de cualquier columna que desee, al contrario de la función BUSCARV que solo le permite buscar en la columna más a la izquierda, en un rango dado.

Fórmula de matriz en D10:

=INDEX($C$3:$C$7, SMALL(IF(($B$10=$B$3:$B$7), MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), ""),ROWS($A$1:A1)))

Este video explica cómo BUSCARV y devolver múltiples valores coincidentes:

La fórmula de la matriz en la celda G3 busca en la columna B "Francia" y devuelve los valores adyacentes de la columna C. La fórmula de la matriz en la celda G3 filtra los valores sin ordenar, si desea ordenar alfabéticamente los valores de retorno, lea esto:
Vlookup con 2 o más criterios de búsqueda y devuelve múltiples coincidencias

Cómo crear una fórmula matricial

  1. Copia la fórmula matricial de arriba. (Ctrl + c)
  2. Haga doble clic en una celda.
  3. Pegar (Ctrl + v) fórmula matricial.
  4. Mantenga presionado Ctrl + Shift simultáneamente.
  5. Presione Enter una vez.
  6. Suelte todas las teclas.

Leer más

Volver arriba

La fórmula matricial anterior solo filtra los valores con una condición, el siguiente artículo explica cómo filtrar en función de múltiples criterios: Vlookup con 2 o más criterios de búsqueda y devuelve múltiples coincidencias

Si no le gustan las fórmulas matriciales, pruebe esta fórmula regular pero más complicada en la celda D10:

=INDEX($C$3:$C$7,SMALL(INDEX(($B$10=$B$3:$B$7)*(MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)))+($E$3<>$B$3:$B$7)*1048577,),ROWS($A$1:A1)))

Volver arriba

Explicando la fórmula matricial (Valores de retorno verticalmente)

Puede seguir fácilmente mientras explico la fórmula, seleccione la celda D10. Vaya a la pestaña "Fórmulas" en la cinta y haga clic en el botón "Evaluar fórmula".

Imagen que muestra el cuadro de diálogo Evaluar fórmula

Haga clic en el botón "Evaluar" que se muestra arriba para avanzar al siguiente paso.

Paso 1 - Identifica celdas iguales al criterio

= (signo igual) es un operador de comparación y verifica si el criterio (E3) es igual a los valores en la matriz ($ B $ 3: $ B $ 7). Este operador es no distingue mayúsculas y minúsculas.

$ E $ 3 = $ B $ 3: $ B $ 7

se convierte en

"France"= {" Alemania ";" Italia ";"France";"Italia";"France"}

y devoluciones

{FALSO, FALSO, VERDADERO, FALSO, VERDADERO}

Paso 2: crea una matriz que contiene los números de fila correspondientes

El Función de fila devuelve el número de fila en función de una referencia de celda, estamos utilizando una referencia de celda que apunta a un rango de celdas que contiene varias filas, por lo que la función ROW devuelve una matriz de números de fila.

PARTIDO (FILA ($ B $ 3: $ B $ 7), FILA ($ B $ 3: $ B $ 7))

El Función MATCH encuentra la posición relativa de un valor en un rango de celdas o matriz, sin embargo, estoy usando múltiples valores, por lo que este paso devuelve una matriz de números.

PARTIDO (FILA ($ B $ 3: $ B $ 7), FILA ($ B $ 3: $ B $ 7))

se convierte en

COINCIDIR ({3, 4, 5, 6, 7}, {3, 4, 5, 6, 7})

y devuelve {1,2,3,4,5}

Paso 3 - Filtrar números de fila iguales a una condición

El Función IF tiene tres argumentos, el primero debe ser una expresión lógica. Si la expresión se evalúa como VERDADERA, sucede una cosa (argumento 2) y si FALSA sucede otra cosa (argumento 3).

IF(($B$10=$B$3:$B$7), MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), "")

se convierte en

SI (FALSO, FALSO, VERDADERO, FALSO, VERDADERO}, COINCIDIR (FILA ($ B $ 3: $ B $ 7), FILA ($ B $ 3: $ B $ 7)), "")

se convierte en

SI (FALSO, FALSO, VERDADERO, FALSO, VERDADERO}, {1, 2, 3, 4, 5}, "")

y devuelve {"", "", 3, "", 5}

Paso 4 - Devuelve el k-ésimo número de fila más pequeño

Para poder devolver un nuevo valor en una celda cada uso el Pequeña función para filtrar números de fila de menor a mayor.

El Función de filas realiza un seguimiento de los números basados ​​en una referencia de celda en expansión. Se expandirá a medida que la fórmula se copie a las celdas de abajo.

SMALL(IF(($E$3=$B$3:$B$7),ROW($B$3:$B$7)-MIN(ROW($B$3:$B$7))+1,""),ROWS($A$1:A1))

se convierte en

PEQUEÑO ({"", "", 3, "", 5}, FILAS ($ A $ 1: A1))

Esta parte de la fórmula devuelve el k-ésimo número más pequeño en la matriz {"", "", 3, "", 5}

Para calcular el k-ésimo valor más pequeño, estoy usando FILAS ($ A $ 1: A1) para crear el número 1.

Cuando la formula en celula D10 se copia a la celda D11, ROWS ($ A $ 1: A1) cambia a ROWS ($ A $ 1: A2). ROWS ($ A $ 1: A2) devuelve 2.

En la celda D10: = ÍNDICE ($ C $ 3: $ C $ 7, PEQUEÑO ({"", "", 3, "", 5}, FILAS ($ A $ 1: A1))

= ÍNDICE ($ C $ 3: $ C $ 7, PEQUEÑO ({"", "", 3, "", 5}, 1))

El número más pequeño en la matriz {"", "", 3, "", 5} es 3.

En la celda D11: = ÍNDICE ($ C $ 3: $ C $ 7, PEQUEÑO ({"", "", 3, "", 5}, FILAS ($ A $ 1: A2)))

= ÍNDICE ($ C $ 3: $ C $ 7, PEQUEÑO ({"", "", 3, "", 5}, 2))

El segundo número más pequeño en la matriz {"", "", 3, "", 5} es 5.

Paso 4: valor de retorno basado en el número de fila

El Función ÍNDICE devuelve un valor basado en una referencia de celda y números de columna / fila.

En la celda D10:

= ÍNDICE ($ C $ 3: $ C $ 7, 3)

se convierte en

= ÍNDICE ({"Pera", "Naranja", "Manzana", "Plátano", "Limón"}, 3)

y devuelve "Apple" en la celda D10.

En la celda D11:

= ÍNDICE ($ C $ 3: $ C $ 7, 5) devuelve "Limón"

Este artículo muestra cómo filtrar una tabla definida por Excel mediante programación basado en una condición usando código de evento y una macro.

Volver arriba

Cómo eliminar #num errores

Imagen de cómo extraer múltiples valores basados ​​en una condición utilizando una fórmula matricial

La imagen de arriba muestra la fórmula de matriz copiada en la celda D12, sin embargo, solo se muestran dos valores, las celdas restantes no muestran nada, ni siquiera un error.

Fórmula de matriz en la celda D10:

=IFERROR(INDEX($C$3:$C$7, SMALL(IF(($B$10=$B$3:$B$7), MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), ""),ROWS($A$1:A1))), "")

El Función de IFERROR le permite convertir valores de error en celdas en blanco o realmente en el valor que desee. En este caso devuelve celdas en blanco.

Nota: IFERROR detecta todo tipo de errores de fórmula. no detectará el error tan fácilmente si hay algún otro tipo de error en la fórmula. Use esta función con precaución.

Cómo ingresar una fórmula matricial

Artículos recomendados

Volver arriba

Contar valores coincidentes

La siguiente imagen muestra un conjunto de datos en las columnas B y C. El valor de búsqueda en la celda E3 se usa para identificar los valores de celda coincidentes en la columna B.

Imagen de cómo contar múltiples valores basados ​​en una condición usando una fórmula

Fórmula en celda G3:

=COUNTIF($B$3:$B$7,E3)

Fórmula alternativa en célula G3:

=SUMPRODUCT(($B$3:$B$7=E3)*1)

Artículos recomendados

Volver arriba

Devuelve múltiples valores horizontalmente

Imagen de cómo extraer múltiples valores horizontalmente en función de una condición mediante una fórmula matricial

Esta fórmula matricial se ingresa en la celda C9. Luego copia la celda C9 y pégala a la derecha.

Fórmula de matriz en C9:

=INDEX($C$2:$C$6, SMALL(IF($B$9=$B$2:$B$6, ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1, ""), COLUMN(A1)))

Introduzca la fórmula como una fórmula matricial o usa esta fórmula regular pero más complicada:

=INDEX($C$2:$C$5, SMALL(INDEX(($B$9=$B$2:$B$6)*(MATCH(ROW($B$2:$B$6), ROW($B$2:$B$6)))+($B$9<>$B$2:$B$6)*1048577, 0, 0), COLUMN(A1)))

Volver arriba

Descargar el archivo de Excel


Return-multiple-values-horizontally.xlsx

Artículos recomendados

Extraer múltiples registros basados ​​en una condición

Imagen de cómo extraer varios registros en función de una condición mediante una fórmula matricial

La fórmula en la celda A10 extrae registros basados ​​en el valor en la celda B9.

Fórmula de matriz en la celda A10:

=INDEX($A$2:$C$7, SMALL(IF($B$9=$A$2:$A$7, ROW($A$2:$A$7)-MIN(ROW($A$2:$A$7))+1, ""), ROW(A1)),COLUMN(A1))

Para ingresar una fórmula matricial, escriba la fórmula en una celda y luego presione y mantenga presionado CTRL + MAYÚS simultáneamente, ahora presione Entrar una vez. Suelte todas las teclas.

La barra de fórmulas ahora muestra la fórmula con un corchete de inicio y final que le indica que ingresó la fórmula con éxito. No entres en los rizos tú mismo.

Copie la celda A10 y péguela en el rango de celdas B10: C10. Luego copie A10: C10 y péguelo en el rango de celdas A11: C12.

Mira un video donde explico cómo usar la fórmula matricial y cómo funciona.

Ingrese la fórmula anterior como una fórmula matricial o usa esta fórmula regular pero más complicada:

=INDEX($A$2:$C$7, SMALL(INDEX(($B$9=$A$2:$A$7)*(MATCH(ROW($A$2:$A$7), ROW($A$2:$A$7)))+($B$9<>$A$2:$A$7)*1048577, 0, 0),ROW(A1)),COLUMN(A1))

Lectura recomendada

Volver arriba

Descargar el archivo de Excel


Vlookup-return-multiple-records.xlsx

Buscar y devolver múltiples valores [Autofiltro]

El Autofiltro es una función incorporada en Excel que le permite filtrar datos rápidamente. El siguiente video le muestra cómo filtrar rápidamente un conjunto de datos, no creo que pueda hacerlo más rápido que esto.

Instrucciones sobre cómo filtrar un conjunto de datos [Autofiltro]

  1. Haga clic derecho en un valor de celda que desea filtrar
  2. Haga clic en "Filtrar" y luego en "Filtrar por el valor de la celda seleccionada"
    Imagen de cómo filtrar múltiples valores en función de una condición mediante Autofiltro
  3. ¡Eso es!

Cómo quitar un filtro

  1. Haga clic en el botón de filtro junto al encabezado, que se muestra en la imagen a continuación
    Imagen de cómo filtrar valores en función de una condición mediante Autofiltro
  2. Haga clic en "Borrar filtro de" País ""
    Ilustración de cómo limpiar el filtro usando Autofiltro
  3. Los botones de Autofiltro al lado de cada encabezado siguen ahí.
    Imagen de botones de autofiltro
  4. Si también desea eliminarlos, vaya a la pestaña "Inicio" en la cinta y haga clic en el botón "Ordenar y filtrar", luego en "Filtrar"
    Imagen de cómo quitar los botones de Autofiltro
  5. El conjunto de datos ahora se ve así:

Volver arriba

Buscar y devolver múltiples valores [Filtro avanzado]

El Filtro avanzado es una herramienta en Excel que le permite filtrar un conjunto de datos utilizando combinaciones de criterios complicados como la lógica AND-OR que la herramienta de Autofiltro normal no puede cumplir.

En este caso, solo voy a filtrar por una sola condición, por lo que esta será una introducción fácil al filtro avanzado en Excel.

  1. Copie los encabezados del conjunto de datos y colóquelos encima o debajo del conjunto de datos, para evitar confusiones si las condiciones desaparecen cuando se aplica un filtro.
    Las filas se ocultarán y, si una condición se encuentra en la misma fila, también se ocultará. Creé encabezados en la fila 2, vea la imagen de arriba.
  2. Ingrese la condición debajo del encabezado correcto al que desea aplicar un filtro, ingresé mi condición en la celda B3.
  3. Seleccionar rango de celdas B5: C10.
  4. Ir a la pestaña "Datos" en la cinta.
  5. Haga clic en el botón "Avanzado".
  6. Haga clic en el campo Criterio: campo y seleccione el rango de celdas B2: C3
  7. Haga clic en el botón Aceptar.

La imagen de arriba muestra el conjunto de datos filtrado según la condición utilizada en la celda B3. Para borrar el filtro, simplemente vaya a la pestaña "Datos" en la cinta y haga clic en el botón "Borrar".

Volver arriba

Buscar y devolver múltiples valores [Tabla definida por Excel]

La imagen de arriba te muestra un conjunto de datos convertido en un Tabla definida de Excel y se filtró según el elemento "Francia" en la columna B.

  1. Seleccione una celda en su conjunto de datos.
  2. Presione CTRL + T (acceso directo para crear una tabla definida de Excel).
  3. Aparece un cuadro de diálogo, haga clic en la casilla de verificación si su conjunto de datos contiene encabezados.
  4. Haga clic en el botón Aceptar.

Para filtrar la tabla siga estos sencillos pasos:

  1. Haga clic en la flecha negra al lado del nombre de un encabezado.
  2. Asegúrese de que la casilla de verificación junto al valor que desea usar como condición esté seleccionada.
  3. Haga clic en el botón Aceptar.

Entonces, ¿por qué usar una tabla definida por Excel? Una tabla definida por Excel contiene muchas más características útiles.

  • Ingrese una fórmula en una celda y Excel ingresa automáticamente la fórmula en las celdas restantes de la Tabla de Excel en la misma columna.
  • Las referencias de celdas se convierten a referencias estructuradas, por ejemplo, una referencia de celda a la columna "País" podría verse así: Tabla [País].
    Esto es beneficioso porque no necesita ajustar las referencias de celda si su tabla crece o se reduce, la referencia de celda es la misma pase lo que pase. No necesitas usar rangos dinámicos nombrados ya sea.
  • Fácil de filtrar y sort datos.
  • Fácil de agregar o eliminar datos, simplemente escriba sus datos debajo de la última fila de la tabla y la tabla definida por Excel se expandirá automáticamente.
  • Utilizar como fuente de datos para un gráfico y el El gráfico mostrará lo que está filtrado.

Volver arriba

Devuelve múltiples valores vertical u horizontalmente [UDF]

Imagen de una función definida por el usuario que extrae valores en función de una condición

Asegúrese de haber copiado el código vba a continuación en un módulo estándar antes de ingresar la fórmula matricial.

Sintaxis de funciones definidas por el usuario

vbaVlookup (valor_buscado, matriz_tabla, número_índice_col, [h])

Argumentos

valor de búsqueda Necesario.
matriz de tabla Necesario. Una referencia de celda a la tabla de datos que desea buscar.
col_index_num Necesario. Un número que representa la columna en table_array.
[h] Opcional. Devuelve los valores horizontalmente.

Fórmula de matriz en la celda C14: D14:

=vbaVlookup(B14, $B$2:$C$6, 2, "h")

Vea un video que explica cómo usar la función definida por el usuario

Cómo ingresar una fórmula de matriz de función personalizada

  1. Seleccionar rango de celdas C9: C11
  2. Escriba encima de la función personalizada
  3. Mantener presionado Ctrl + Shift
  4. Presione Enter una vez
  5. Libera todas las llaves

Cómo ingresar una fórmula matricial

Las fórmulas de matriz le permiten hacer cálculos avanzados que no son posibles con fórmulas regulares.

Cómo ingresar una fórmula matricial

Volver arriba

Cómo ingresar una fórmula de matriz de función personalizada

  1. Seleccionar rango de celdas C14: D14
  2. Escriba encima de la función personalizada
  3. Mantener presionado Ctrl + Shift
  4. Presione Enter una vez
  5. Libera todas las llaves

Cómo copiar la fórmula de matriz a la siguiente fila

  1. Seleccionar rango de celdas C14: D14
  2. Copiar rango de celdas
  3. Seleccionar rango de celdas C15: D15
  4. Pasta

Codigo vba

  1. Copie el código vba a continuación.
  2. Presione Alt + F11 para abrir el editor visual básico.
  3. Haga clic derecho en su libro de trabajo en el explorador de proyectos.
  4. Haga clic en "Insertar".
  5. Haga clic en "Módulo".
  6. Pegue el código en el módulo de código.
  7. Salir del editor vb y volver a Microsoft Excel
'Name User Defined Function and arguments
Function vbaVlookup(lookup_value As Range, tbl As Range, col_index_num As Integer, Optional layout As String = "v")

'Declare variables and data types
Dim r As Single, Lrow, Lcol As Single, temp() As Variant

'Redimension array variable temp
ReDim temp(0)

'Iterate through cells in cell range
For r = 1 To tbl.Rows.Count

   'Check if lookup_value is equal to cell value
   If lookup_value = tbl.Cells(r, 1) Then

      'Save cell value to array variable temp
      temp(UBound(temp)) = tbl.Cells(r, col_index_num)

      'Add anoher container to array variable temp
      ReDim Preserve temp(UBound(temp) + 1)
   End If
Next r

'Check if variable layout equals h
If layout = "h" Then

   'Save the number of columns the user has entered this User Defined Function in. 
   Lcol = Range(Application.Caller.Address).Columns.Count
   
   'Iterate through each container in array variable temp that won't be populated
   For r = UBound(temp) To Lcol
      
       'Save a blank to array container
       temp(UBound(temp)) = ""

       'Increase the size of array variable temp with 1
       ReDim Preserve temp(UBound(temp) + 1)
   Next r

   'Decrease the size of array variable temp with 1
   ReDim Preserve temp(UBound(temp) - 1)

   'Return values to worksheet
   vbaVlookup = temp

'These lines will be executed if variable layout is not equal to h
Else

   'Save the number of rows the user has entered this User Defined Function in
   Lrow = Range(Application.Caller.Address).Rows.Count

   'Iterate through empty cells and save nothing to them in order to avoid an error being displayed
   For r = UBound(temp) To Lrow
      temp(UBound(temp)) = ""
      ReDim Preserve temp(UBound(temp) + 1)
   Next r

'Decrease the size of array variable temp with 1
ReDim Preserve temp(UBound(temp) - 1)

'Return temp variable to worksheet with values rearranged vertically
vbaVlookup = Application.Transpose(temp)
End If

End Function

Lectura recomendada

Volver arriba

Descargar el archivo de Excel


Vlookup-vba.xlsm

Volver arriba