Un solo criterio de búsqueda múltiples resultados

Question

Necesito generar los resultados de una columna de datos ordenados con un solo criterio de búsqueda. A veces hay varias apariciones del mismo criterio. LOOKUP encuentra sólo la primera aparición. Necesito ingresar una celda para que coincida con J8: J581 y los datos correspondientes que se mostrarán son N8: N581

J   K L M N
bob       RED
bob       BLUE
Bob       Green
Sue       yellow
Sue       white
fred      grey
pete      brown

.

input=bob
output=    bob  RED
                BLUE
                Green

Answer 1

Si simplemente busca devolver filas como el ejemplo anterior, use una tabla de Excel.

1. Selecciona tus datos
2. Crear una tabla Insert>Tables>Table(marque la casilla Mi tabla tiene encabezados)
3. Una vez que se haya creado la tabla, simplemente elija su botón de filtro (flecha hacia abajo en la fila del encabezado de la tabla) y elija su valor de filtro (por ejemplo, en su muestra, elija Bob); esto devolverá solo las filas "Bob".

Aquí hay una imagen de antes:

Y después:

EDITAR: Según su información adicional, consideraría agregar una tabla dinámica basada en su tabla de datos. Esto le permitirá crear una vista "delineada" de su información, para ver mejor qué campings tienen una sola identificación frente a múltiples identificaciones. Se vería algo como esto:

Además, como señala @pnuts, Excel está limitado a 10,000 elementos en una lista desplegable (es decir, filtro), pero no existe un límite documentado para los miembros de la tabla. He tenido tablas con 10.000 filas.

EDITAR2:Si desea encontrar fácilmente sólo valores repetidos, una tabla dinámica es absolutamente el camino a seguir.

1. Tome su tabla de datos inicial y agregue una fila de "contador". =IF([@Name]=D1,F1+1,1)Esto devolverá un recuento de cuántas filas tienen el mismo nombre.
2. Cree una tabla dinámica Insert>Tables>Pivot Tablea partir de sus datos.
3. Formatee su tabla dinámica de esta manera:
   • Etiquetas de fila = Name,, y desactive los subtotales y los totales generales para todos los valores Code.Counter
   • Contador de filtro = Borrar 1, esto mostrará solo nombres con múltiples valores.
   • Nombre del filtro = cualquier nombre de camping específico que le interese.

Answer 2

Digamos que su celda de entrada es A1 y sus celdas de salida son A3 para el nombre y B3:B.. para el rango de salida (con el final del rango B descendiendo hasta el número máximo de resultados que espera ver) .

Para A3, pon la fórmula =A1.

Para B3, pon la fórmula.

  =IFERROR(INDEX($N$1:$N$99,SMALL(IF($J$1:$J$99=$A$3,ROW($N$1:$N$99)-ROW($N$1)+1),ROWS($N$1:$N1))),"")

Esta es una fórmula matricial y debe ingresarse con la combinación de teclas Control- Shift- Enter. Luego puede copiarlo al final de su rango de salida.

Tenga en cuenta que la fórmula está codificada para una lista de entrada con un máximo de 99 filas. Puede cambiar esto a la longitud que necesite. Si bien sería posible hacer referencia a toda la columna (J:J y N:N), habría un impacto en el rendimiento que probablemente desee evitar.

Cómo funciona la fórmula

Trabajando de adentro hacia afuera, la fórmula primero compara el nombre en el que se realizará la búsqueda (celda A3) con la lista completa de nombres (hasta 99 nombres en el rango J1:J99). Esa comparación se muestra en la línea 6 del desglose de funciones que se muestra a continuación.

El producto de esa comparación es una matriz con valores Verdaderos para una coincidencia y valores Falso para una no coincidencia, por ejemplo, {Falso, Falso, Falso, Falso, Verdadero, Verdadero, Falso, ... etc.}.

Luego, se hace una comparación entre esa matriz con una matriz que puede considerarse como los "números de línea" de la lista de nombres: {1, 2, 3, 4, 5, 6,... 99}. Esta comparación se realiza mediante la declaración IF en las líneas 6 a 8 del cuadro de fórmulas.

La comparación es elemento por elemento. Si un elemento de la matriz de comparación de nombres es igual a Verdadero, entonces IF devuelve su número de línea correspondiente; si el elemento es igual a Falso, el IF devuelve FALSO. Usando las dos matrices de ejemplo anteriores, el resultado de la declaración IF sería {Falso, Falso, Falso, Falso, 4, 5, Falso, ...}.

Continuando, la función PEQUEÑA (comenzando en la línea 8 del esquema de la función) se usa para obtener el késimo elemento más pequeño de esta nueva matriz del IF. La "k" en este caso la proporciona la expresión ROWS($N$1:$N1), que simplemente contará del 1 al 99 cuando se copie toda la fórmula desde la fila 1 hasta la fila 99 (ROWS($N$1 :$N1) = 1, FILAS($N$1:$N2) = 2, y así sucesivamente).

Entonces, SMALL primero encontrará el elemento más pequeño de la matriz producida por IF,postergaciónlos elementos que son falsos. En otras palabras, devolverá el número de la primera línea donde el nombre que se compara coincide con un nombre en la lista de nombres de búsqueda. En nuestro ejemplo, ese es el número 4, como se muestra en la sexta columna de la siguiente tabla.

El paso final utiliza ÍNDICE en los valores de búsqueda para recuperar el elemento correspondiente al número de línea que se ha calculado. En este caso, el cuarto elemento del ejemplo de lista de colores de la pregunta es "amarillo". (El IFERROR se asegura de que se muestre un espacio en blanco cuando la fórmula no logra encontrar una coincidencia).

Ese fue el resultado que arrojó la primera copia de la fórmula completa. Cuando se copia a la siguiente celda, lo único que cambia en el cálculo es el valor de "k" para la función PEQUEÑA, que avanza a 2. Y el segundo número de línea más pequeño que se encontró es 5, que produce el valor "blanco".