Tengo dos tablas de Excel, la tabla A y la tabla B. La tabla A contiene las columnas ID de cliente, código postal, nombre de cliente y varias otras que tienen los mismos nombres de columna que las columnas de la tabla B. Quiero crear una fórmula, idealmente usando referencias estructuradas. para que el orden de las columnas en la tabla B sea irrelevante, eso busca el valor en la tabla B que coincide con el ID de cliente de la fila en la que estoy en la tabla A y el valor de la columna en la que estoy.
Por ejemplo, si mi fórmula está en la tercera columna de la tabla A y en una fila con el ID de cliente "123", quiero que verifique su propio nombre de columna (Nombre del cliente) y busque el valor de Nombre del cliente donde el ID del cliente = "123" en el cuadro B.
La siguiente fórmula funciona bien para la columna Nombre del cliente:
=INDEX(TableB[Customer Name], MATCH([@[Customer Number]], TableB[Customer Number], 0))
pero quiero poder crear una fórmula única que reemplace dinámicamente la pieza [Nombre del cliente] con el nombre de la columna en la que estoy para poder copiarla en todas las columnas. Intenté crear la referencia usando #Headers y usando indirecto pero aparece un error de referencia:
=INDIRECT("INDEX(TableB["&[#Headers]&"], MATCH([@[Customer Number]], TableB[Customer Number], 0))")
Respuesta1
INDEX MATCHes el enfoque correcto, sólo hay que tener cuidado al estructurarlo.
Mesa A a la izquierda. Tabla B a la derecha. Usaremos [Número de cliente] para buscar [Código postal].
Esta es la fórmula para escribir D2:
=INDEX(TableB,MATCH([@[Customer Number]],TableB[Customer Number],0),MATCH(D$1,TableB[#Headers],0))
INDEX, como sabes, devuelve el valor de una celda en la intersección de una fila y una columna. MATCHdevuelve la posición relativa de un valor en una matriz.
Entonces, para las dos entradas de INDEX, primero encontramos el número de fila en la tabla fuente que coincide con el valor de búsqueda que estamos usando (Número de cliente) (esta es la primera mitad tradicional de un INDEX MATCH) alimentando MATCHuna matriz vertical para buscar, luego encontramos el número de columna que coincide con el nombre de la columna en la que nos encontramos alimentando MATCHuna matriz horizontal que comprende la fila del encabezado de la columna de origen.
Notará que si agrega un campo a la tabla de origen y cambia el encabezado en la tabla de fórmulas, obtendrá los nuevos resultados sin cambiar la fórmula.
..y que esta fórmula se copia tanto hacia arriba como hacia abajo.
Las dos claves aquí son:
- sabiendo que MATCH contará tanto en sentido transversal como descendente.
- cambiar manualmente la referencia estructurada que Excel le proporciona a la referencia de celda de estilo R1C1 real, de modo que pueda hacer que el índice de columna de esa referencia de celda sea dinámico en lugar de fijo (es decir, C$1 en lugar de
TableA[[#Headers],[Post Code]], que codifica el valor de la campo en el que se encuentra y, como tal, no se copiará, aunque funcionará si cambia manualmente el nombre de la columna que tiene la fórmula de búsqueda y solo necesita tener un campo de búsqueda en la tabla de destino) .
NÓTESE BIENSé que esta pregunta tiene más de tres años, pero es una buena pregunta y una gran demostración de la versatilidad de la INDEX MATCHtécnica.