Note: The other languages of the website are Google-translated. Back to English

¿Cómo devolver múltiples valores coincidentes según uno o varios criterios en Excel?

Normalmente, buscar un valor específico y devolver el elemento coincidente es fácil para la mayoría de nosotros usando la función BUSCARV. Pero, ¿alguna vez ha intentado devolver varios valores coincidentes en función de uno o más criterios como se muestra en la siguiente captura de pantalla? En este artículo, presentaré algunas fórmulas para resolver esta compleja tarea en Excel.

Devuelve varios valores coincidentes según uno o varios criterios con fórmulas de matriz


Devuelve varios valores coincidentes según uno o varios criterios con fórmulas de matriz

Por ejemplo, quiero extraer todos los nombres cuya edad sea 28 y provengan de Estados Unidos, por favor aplique la siguiente fórmula:

1. Copie o ingrese la fórmula a continuación en una celda en blanco donde desea ubicar el resultado:

=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

Nota:: En la fórmula anterior, B2: B11 es la columna de la que se devuelve el valor coincidente; F2, C2: C11 son la primera condición y los datos de la columna que contienen la primera condición; G2, D2: D11 son la segunda condición y los datos de la columna que contienen esta condición, cámbielos según sus necesidades.

2. Entonces presione Ctrl + Shift + Enter claves para obtener el primer resultado coincidente, y luego seleccione la primera celda de fórmula y arrastre el controlador de relleno hacia las celdas hasta que se muestre el valor de error, ahora, todos los valores coincidentes se devuelven como se muestra a continuación en la captura de pantalla:

Consejos: Si solo necesita devolver todos los valores coincidentes en función de una condición, aplique la siguiente fórmula de matriz:

=IFERROR(INDEX($B$2:$B$11, SMALL(IF($F$2=$D$2:$D$11, ROW($D$2:$D$11)-ROW($D$2)+1), ROW(1:1))),"" )


Artículos más relativos:

  • Devolver varios valores de búsqueda en una celda separada por comas
  • En Excel, podemos aplicar la función BUSCARV para devolver el primer valor coincidente de las celdas de una tabla, pero, a veces, necesitamos extraer todos los valores coincidentes y luego separarlos por un delimitador específico, como una coma, un guión, etc ... en un solo celda como se muestra en la siguiente captura de pantalla. ¿Cómo podríamos obtener y devolver múltiples valores de búsqueda en una celda separada por comas en Excel?
  • Vlookup y devuelve múltiples valores coincidentes a la vez en la hoja de Google
  • La función normal de Vlookup en la hoja de Google puede ayudarlo a encontrar y devolver el primer valor coincidente basado en un dato dado. Pero, a veces, es posible que deba realizar una búsqueda virtual y devolver todos los valores coincidentes como se muestra en la siguiente captura de pantalla. ¿Tiene alguna forma buena y fácil de resolver esta tarea en la hoja de Google?
  • Vlookup y devuelve varios valores de la lista desplegable
  • En Excel, ¿cómo podría visualizar y devolver múltiples valores correspondientes de una lista desplegable, lo que significa que cuando elige un elemento de la lista desplegable, todos sus valores relativos se muestran a la vez como se muestra en la siguiente captura de pantalla? En este artículo, presentaré la solución paso a paso.
  • Vlookup y devuelve múltiples valores verticalmente en Excel
  • Normalmente, puede usar la función Vlookup para obtener el primer valor correspondiente, pero, a veces, desea devolver todos los registros coincidentes según un criterio específico. En este artículo, hablaré sobre cómo visualizar y devolver todos los valores coincidentes verticalmente, horizontalmente o en una sola celda.
  • Vlookup y devuelve datos coincidentes entre dos valores en Excel
  • En Excel, podemos aplicar la función normal de Vlookup para obtener el valor correspondiente basado en un dato dado. Pero, a veces, queremos visualizar y devolver el valor coincidente entre dos valores como se muestra en la siguiente captura de pantalla, ¿cómo podría manejar esta tarea en Excel?

 


  • Barra de súper fórmula (edite fácilmente varias líneas de texto y fórmulas); Diseño de lectura (leer y editar fácilmente un gran número de celdas); Pegar en rango filtrado...
  • Combinar celdas / filas / columnas y conservación de datos; Contenido de celdas divididas; Combinar filas duplicadas y suma / promedio... Prevenir celdas duplicadas; Comparar rangos...
  • Seleccione Duplicado o Único Filas; Seleccionar filas en blanco (todas las celdas están vacías); Super Find y Fuzzy Find en muchos libros de trabajo; Selección aleatoria ...
  • Copia exacta Varias celdas sin cambiar la referencia de la fórmula; Crear referencias automáticamente a varias hojas; Insertar viñetas, Casillas de verificación y más ...
  • Fórmulas favoritas e insertar rápidamente, Rangos, gráficos e imágenes; Cifrar celdas con contraseña; Crear lista de distribución y enviar correos electrónicos ...
  • Extraer texto, Agregar texto, Eliminar por posición, Quitar espacio; Crear e imprimir subtotales de paginación; Convertir entre contenido de celdas y comentarios...
  • Súper filtro (guardar y aplicar esquemas de filtros a otras hojas); Orden avanzado por mes / semana / día, frecuencia y más; Filtro especial en negrita, cursiva ...
  • Combinar libros y hojas de trabajo; Combinar tablas basadas en columnas clave; Dividir datos en varias hojas; Conversión por lotes de xls, xlsx y PDF...
  • Agrupación de tablas dinámicas por número de semana, día de la semana y más ... Mostrar celdas bloqueadas y desbloqueadas por diferentes colores; Resalte las celdas que tienen fórmula / nombre...
pestaña kte 201905
  • Habilite la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
  • Abra y cree varios documentos en nuevas pestañas de la misma ventana, en lugar de en nuevas ventanas.
  • ¡Aumenta su productividad en un 50% y reduce cientos de clics del mouse todos los días!
officetab parte inferior

 

Comentarios (25)
Aún no hay calificaciones. ¡Sé el primero en calificar!
Este comentario fue minimizado por el moderador en el sitio
Probé esta misma fórmula exacta; copiado al 100%. Lo único que cambié fue que los datos coincidieran y se devolvieran. Cuando uso esta fórmula, Excel dice "Ha ingresado demasiados argumentos para esta función). = ÍNDICE ('Informe de volumen 2020'! $ B $ 3: $ B $ 100, PEQUEÑO (SI (CONTAR.SI ($ A $ 1,' Volumen Informe'!$A$2020:$A$3)*COUNTIF($A$100,'3 Volume Report'!$D$2020:$D$3),ROW('100 Volume Report'!$A$2020:$G$3)- MIN(FILA('Informe de volumen 100'!$A$2020:$G$3))+100,"1"),FILA(A0),COLUMNA(A1))
Este comentario fue minimizado por el moderador en el sitio
Hola, ¿podría dar sus datos y el error de fórmula como captura de pantalla aquí?
Este comentario fue minimizado por el moderador en el sitio
Hola, ¿Cómo puedo usarlo para la condición Horizontal.
Este comentario fue minimizado por el moderador en el sitio
¿Qué es el "0" después del +1 en la fórmula? Eso no está en el ejemplo uno.
Este comentario fue minimizado por el moderador en el sitio
Hola, había probado la misma fórmula. estoy obteniendo resultados, pero cuando doy CSE no proporciona respuestas múltiples
Este comentario fue minimizado por el moderador en el sitio

Este comentario fue minimizado por el moderador en el sitio
Con respecto a la devolución de múltiples valores coincidentes basados ​​en uno o varios criterios con fórmulas de matriz: ¿Por qué si tengo los datos en cualquier otro lugar, excepto comenzando en A1, no funciona aunque actualice todas las referencias de celda en la fórmula?
Este comentario fue minimizado por el moderador en el sitio
En el primer ejemplo, ¿qué cambio en la fórmula sería necesario para devolver a todos los que tenían menos de 28 años?
Este comentario fue minimizado por el moderador en el sitio
Hola,

Me preguntaba si es posible ingresar un segundo criterio pero del mismo rango que el primer criterio,

Por ejemplo, con el ejemplo anterior, me gustaría buscar los nombres de personas tanto de Estados Unidos como de Francia. Entonces, la celda F3 tendría Francia, Scarlett y Andrew también se incluirían en la lista de la Columna G.

Gracias por la ayuda de antemano.
Este comentario fue minimizado por el moderador en el sitio
Hola Nick,

Encantado de ayudar. Si desea obtener los nombres de personas tanto de Estados Unidos como de Francia, le aconsejo que use nuestra fórmula dos veces para obtener el resultado. Por favor, vea la captura de pantalla, en F2 y G2 son los valores "Estados Unidos" y "Francia". Aplicar fórmula =SI.ERROR(ÍNDICE($B$2:$B$11, PEQUEÑO(SI($F$2=$D$2:$D$11, FILA($D$2:$D$11)-FILA($D$2)+1 ), ROW(1:1))),"" ) para obtener los resultados de América. Y aplique la fórmula =SI.ERROR(ÍNDICE($B$2:$B$11, PEQUEÑO(SI($G$2=$D$2:$D$11, FILA($D$2:$D$11)-FILA($D$2)+ 1), ROW(1:1))),"" ) para obtener los resultados de Francia. Es simple. Por favor, inténtalo.

Atentamente,
Mandy
Este comentario fue minimizado por el moderador en el sitio
Cuando uso la segunda fórmula y arrastro hacia abajo, no aparece nada. El resultado de la fórmula (fx) dice que debería devolver algo, pero está en blanco. ¿Cómo corrijo esto?
Este comentario fue minimizado por el moderador en el sitio
Hola Alysia,

Encantado de ayudar. Probé la segunda fórmula en el artículo y arrastré la fórmula hacia abajo, se devolvieron el resto de los resultados. Creo que puede haber dos razones para su problema. Primero, tal vez olvide presionar las teclas Ctrl + Shift + Enter para ingresar la fórmula. En segundo lugar, el resultado coincidente es solo uno, por lo que no se devuelven otros resultados. Por favor, tenga un cheque.

Atentamente,
Mandy
Este comentario fue minimizado por el moderador en el sitio
Hola,
Intenté usar la fórmula y genera un valor de 0 o la imagen adjunta
Este comentario fue minimizado por el moderador en el sitio
Hola Milku
Su captura de pantalla mostró el software WPS de la versión MAC, por lo que no estoy seguro de si nuestra fórmula está disponible.
Cargué un archivo de Excel aquí, puede intentar ver si puede calcular correctamente en su entorno.
¡Gracias!
Este comentario fue minimizado por el moderador en el sitio
Hola,
qué se necesitaría para desarrollar la primera fórmula en el siguiente caso:
Algunas ID están en blanco (por ejemplo, la celda A5 está en blanco) y me gustaría una condición adicional que genere líneas solo cuando las ID no están en blanco. (Entonces, la salida debería ser James y Abdul.
¡Gracias!
Este comentario fue minimizado por el moderador en el sitio
Hola Jo,
Para resolver su problema, aplique la siguiente fórmula:
=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11)*($A$2:$A$11<>0), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

Por favor, pruébalo, ¡espero que te pueda ayudar!
Este comentario fue minimizado por el moderador en el sitio
Hola,

si en la celda H1 escribo "Nombre" y quiero vincularlo con la fórmula, ¿cómo funcionaría?
Entonces podría escribir "ID" en la celda H1 y automáticamente obtendría como resultado: AA1004; DD1009; PP1023 (para la primera fórmula)

Gracias de antemano!
Este comentario fue minimizado por el moderador en el sitio
hola marie
Lo siento, no puedo entender el punto de su primer problema, ¿podría explicar su problema de manera más clara y detallada? O puede insertar una captura de pantalla aquí para describir su problema.
En cuanto a la segunda pregunta, solo necesita cambiar la referencia de celda como esta:
=INDEX($A$2:$A$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

Recuerda presionar Ctrl + Shift + Enter llaves juntas.
Por favor, inténtalo, ¡espero que te pueda ayudar!
Este comentario fue minimizado por el moderador en el sitio
Heyi, gracias por la fórmula. Funcionó para valores/texto "fijos" como criterios. Sin embargo, uno de los criterios que estoy tratando de usar es una condición (valores <>0), pero no funciona con la fórmula descrita. ¿Saben qué debo cambiar para adaptar la fórmula y poder tener una condición como uno de los criterios, por favor?

Mejor,

Juan
Este comentario fue minimizado por el moderador en el sitio
hola marcus
Para resolver su problema, consulte este artículo:
https://www.extendoffice.com/documents/excel/6393-excel-vlookup-function.html#b3-2
Hay algunas explicaciones detalladas de esta tarea. Solo necesita cambiar el criterio por el suyo propio.
¡Gracias!
Este comentario fue minimizado por el moderador en el sitio
Hola,

Primero, ¡gracias por compartir!

¿Puede proporcionar una solución al caso a continuación?

Tengo 3 columnas (A: contiene información de referencia, B: contiene información para buscar, C: resultado de la búsqueda)

La URL de la imagen se proporciona a continuación

https://ibb.co/VHCd09K

Columna A---------------------------Columna B------------Columna C
Nombre de archivo----------------------------Nombre----------------Nombre de archivo, Nombre de documento, Nombre del elemento, Nombre
Elemento cambiado-----------------Elemento--------------Elemento cambiado, nombre de elemento, ID de elemento
Ubicación de la columna
Nombre del documento
Nombre del elemento
Nombre
Categoría
Garantía
Pendiente
ID de elemento

Lo que necesito es buscar en la columna A cualquier coincidencia parcial con la celda B2 (Nombre) o B3 (Elemento) y obtener el resultado en una celda,

Gracias Behzad
Este comentario fue minimizado por el moderador en el sitio
Hola, Behzad
Tal vez la siguiente función definida por el usuario pueda ayudarlo.
Public Function ConcatPartLookUp(rngInput As Range, rngSource As Range, Optional strDelimiter As String, Optional blCaseSensitive)
Dim rng As Range
If strDelimiter = "" Then strDelimiter = ","
If IsMissing(blCaseSensitive) Then
    blCaseSensitive = False
Else
    blCaseSensitive = True
End If
For Each rng In rngSource
    If blCaseSensitive Then
        If InStr(1, rng.Value, rngInput.Value, vbBinaryCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
    Else
        If InStr(1, rng.Value, rngInput.Value, vbTextCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
    End If
Next
If Len(ConcatPartLookUp) > 0 Then ConcatPartLookUp = Mid(ConcatPartLookUp, 2, Len(ConcatPartLookUp))
End Function


Después de copiar y pegar este código, y luego use esta fórmula:=ConcatPartLookUp(B2,$A$2:$A$8) para obtener el resultado que necesita.
Por favor, inténtalo, ¡espero que te pueda ayudar!
Este comentario fue minimizado por el moderador en el sitio
Hola,

Gracias por publicar estos ejemplos.
Estoy tratando de implementar esto en mi propia hoja, pero no lo hago funcionar (tal vez porque estoy usando una versión europea de Excel).

Quiero obtener las fechas de los días que tuve mis turnos o que trabajé 'algunas' (>0) horas para un cliente.

Entonces en I3 está el nombre y en J3 el mes. K3 y L3 son los turnos (se trabaja 1) y las horas (no sé cómo configurar esto, debería ser más de cero)

Mis resultados esperados están en:
Turnos: I7 e I8
horas: J7

Así que trabajé más de 0 horas para la 'persona 2' en octubre el 3-10-2022
tenía turnos para la persona 2 el '10-10-2022' y el 28-10-2022

Cuando agrego '=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW ($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))' en mi hoja de Excel, no permite el coma entre las diferentes partes de la fórmula.
Entonces necesito cambiarlos a ';'.
Pero cuando lo pruebo siempre dice: '¿#NOMBRE?'

Entonces, ¿alguien puede ayudarme con esto?

Saludos cordiales,

Bas
[img]https://drive.google.com/file/d/1iIPQKuj_PNhqWyWlwJ4IQTqGNEd6B9Hw/view?usp=share_link[/img]
Este comentario fue minimizado por el moderador en el sitio
Hola, si hay valores duplicados (por ejemplo, dos adams), ¿cómo me aseguro de que solo devuelva 1 adam y no 2?
Este comentario fue minimizado por el moderador en el sitio
Hola bobby
Para extraer solo valores coincidentes únicos, debe aplicar la siguiente fórmula:
Después de pegar la fórmula, presione Ctrl + Shift + Enter teclas juntas para obtener el resultado correcto.
=SI.ERROR(ÍNDICE($B$2:$B$5, COINCIDIR(0, CONTAR.SI(H1:$H$1, $B$2:$B$5)+SI($D$2:$D$5<>$G$2, 1 , 0)+SI($C$2:$C$5<>$F$2, 1, 0), 0)), "")

Por favor, inténtalo, ¡espero que te pueda ayudar!
No hay comentarios publicados aquí todavía
Deje sus comentarios
Publicar como invitado
×
Califica esta publicación:
0   Personajes
Ubicaciones sugeridas

Seguinos

Copyright © 2009 - www.extendoffice.com. | Reservados todos los derechos. Energizado por ExtendOffice, | Mapa del Sitio
Microsoft y el logotipo de Office son marcas comerciales o marcas comerciales registradas de Microsoft Corporation en los Estados Unidos y / o en otros países.
Protegido por Sectigo SSL