PARTIDO DE ÍNDICE de Excel Comprobación de varias columnas

Question

Básicamente, el problema que estoy tratando de resolver es una BUSCARV que busca un valor en las columnas A:E y devuelve el valor contenido en la columna F si se encuentra en cualquiera de ellas.

Dado que BUSCARV no está a la altura de la tarea, he analizado la sintaxis INDEX-MATCH, pero me cuesta entender cómo completar esto para una matriz de valores, en lugar de una sola columna. He creado un conjunto de datos de ejemplo a continuación para intentar explicar esto:

A------B------C------D------E------F

1------2------3------4------5------Apple

12-----13--------------------------Banana

14---------------------------------Carrot

Si la celda que se está verificando contiene 1,2,3,4 o 5, el resultado de la fórmula debería ser Apple. Si es 12 o 13 debería devolver Banana y finalmente si contiene 14 debería devolver Carrot.

La segunda mitad de esto proviene del hecho de que la celda a la que se hace referencia no es un valor único, sino una tabla completa en sí misma. Como tal, esta búsqueda se completará una gran cantidad de veces según diferentes valores.

Entonces, para demostrarlo, hay otra tabla en otro lugar (como a continuación) que tiene estos valores. Estoy intentando que el sistema identifique qué fila y, por lo tanto, cuál de los valores de "Manzana, Plátano, Zanahoria" asociar con cada columna. La tabla quedaría como se muestra a continuación.

HOLA------------

1------(manzana)----

2------(manzana)----

12-----(plátano)-

etc.-----------------

Los valores entre paréntesis son donde la fórmula calcula estos valores.

Answer 1

Tienes varios casos diferentes. Consideremos un caso:

En algún lugar de las columnasAa través demihay una y única celda que contiene 13, devuelve el contenido de la celda en la columnaFen la misma fila.

Usaremos una columna "ayudante". EnG1ingresar:

=COUNTIF(A1:E1,13)

y copie. Esto nos permite identificar la fila:

Ahora podemos usarCOINCIDIR()/ÍNDICE():

Elija una celda e ingrese:

=INDEX(F:F,MATCH(1,G:G,0))

Si las "reglas" cambian y podría haber más de un 13 en una fila o varias filas que contengan 13, modificaríamos la columna auxiliar.

EDITAR #1:

Según su actualización, el primer paso sería extraer el código fijo13fuera de las fórmulas en la columna "ayudante" y colóquelas en su propia celda,(decirH1). Luego podrá ejecutar diferentes casos simplemente cambiando una sola celda.

Si tiene una gran cantidad de casos en una tabla, puede crear una macro para configurar cada caso.(actualizarH1)y registrar los resultados.

Answer 2

Según mi propia investigación y conversaciones con @Gary'sStudent, la solución que utilicé fue crear una fórmula COINCIDIR para cada una de las posibles columnas en las que podría estar contenido el valor, junto con una declaración "IFERROR" en blanco.

I1 =IFERROR(MATCH($H1,A$1:A$3,0),"")     
J1 =IFERROR(MATCH($H1,B$1:B$3,0),"")     
K1 =IFERROR(MATCH($H1,C$1:C$3,0),"")    
L1 =IFERROR(MATCH($H1,D$1:D$3,0),"")    
M1 =IFERROR(MATCH($H1,E$1:E$3,0),"")
etc.

Estas columnas ahora se pueden ocultar para evitar la confusión/interacción del usuario.

Luego creé un índice que los acumula en un valor único, que debería coincidir con la FILA en cuestión. Nuevamente, hay una marca (primera SUMA) para ingresar esto como un valor en blanco si el valor no se encuentra en la tabla.

N1 =IF(SUM(I1:M1)=0,"",INDEX($A$1:$F$3,SUM(I1:M1),6))

Finalmente, ingresé algunas fórmulas de formato condicional para garantizar que el usuario identifique y reemplace/elimine cualquier dato duplicado.

A1:E3 Cell contains a blank value                [Formatting None Set, Stop if True]
A1:E3 =COUNTIF($A$1:$E$3,A1)>1                   [Formatting Text:White, Background:Red]

H1:N1 =COUNTIF($A$1:$E$3,H1)>1       [Formatting Text:Red, Background:Red]

Esto es simplemente una señal para que el usuario elimine estos datos duplicados.

Answer 3

Para una única fórmula en H1:

=INDEX($F$1:INDEX(F:F,MATCH("ZZZ",F:F)),AGGREGATE(15,6,ROW($A$1:INDEX(E:E,MATCH("ZZZ",F:F)))/($A$1:INDEX(E:E,MATCH("ZZZ",F:F))=H1),1))

Esta es una fórmula matricial, por lo que debemos limitar las referencias al tamaño del conjunto de datos. Todos INDEX(E:E,MATCH("ZZZ",F:F))hacen eso. Esto devuelve la última fila de la columna F que tiene texto. Luego la establece como la última fila para iterar.

El método @Gary'sStudent evita las fórmulas de matriz y puede ser el método necesario. A medida que aumentan el conjunto de datos y el número de fórmulas, también aumenta el tiempo para los cálculos. Incluso hasta, en algún momento, el colapso de Excel. Por lo general, esto requiere unos pocos miles, pero quiero hacer una advertencia.

---

EDITAR

Para evitar el uso de fórmulas de matriz y seguir siendo una fórmula:

=IFERROR(INDEX(F:F,MIN(IFERROR(MATCH($H1,A:A,0),1050000),IFERROR(MATCH($H1,B:B,‌​0),1050000),IFERROR(MATCH($H1,C:C,0),1050000),IFERROR(MATCH($H1,D:D,0),1050000),I‌​FERROR(MATCH($H1,E:E,0),1050000))),"")

Esto se basa en la respuesta del OP, simplemente combinó ese método en una fórmula.

Esta fórmula ignorará las entradas duplicadas y devolverá la primera fila en la que se encuentra el número.

Y debido a que no es una matriz, las referencias de columnas completas no son perjudiciales para los tiempos de cálculo.

Answer 4

Un método diferente se basaría en una tabla auxiliar, que representa cómo se habría estructurado este "debería" en primer lugar. Esto evitaría las ecuaciones monstruosas que son molestas de depurar y cambiar después, y es capaz de resolver limpiamente un número variable de columnas, a diferencia de la idea de tener 5 columnas de búsqueda.

Si lo anterior está en la Hoja1, agregue una Hoja2. En ese lugar cuatro columnas; Fila, Columna, ID, Nombre

La fórmula Rowdebe ser (en psuedo código, "Último" significa "para la fila de arriba en la hoja 2")

=IF(Column = 1, Last row + 1 , Last row)

Fórmula en Column:

=IF(OR(Last Column = 5; INDEX(StartTable, last row, last column + 1) = ""), 1, Last column+1)

Fórmula en IDy Name:

=INDEX(StartTable, Row, Column)    
=INDEX(NameColumn, Row, 1)

Luego completa esto (básicamente hasta row> número de filas en la tabla original).

Finalmente, utiliza la nueva tabla con una búsqueda virtual o un índice/coincidencia normal.

PRO: Fórmulas mucho más sencillas, más fáciles de utilizar y comprender.

CONTRAS: Necesita mesa adicional, debe mantener la longitud de la mesa. En cuanto al rendimiento, existe un riesgo, ya que esto requiere prácticamente un solo subproceso para toda la "cadena" de valores.

Además, si un par de filas de error están bien, el código puede ser algo más simple y posiblemente más eficaz; entonces podemos asumir que el número de columnas siempre es 5, dando tanto fila como columna.