Búsqueda en la tabla 2d utilizando la función INDICE y COINCIDIR
En este artículo, aprenderemos cómo buscar valores en tablas 2d usando una función INDEX-MATCH-MATCH en Excel.
Escenario:
Suponga que necesita realizar varias búsquedas desde una tabla que tiene cientos de columnas. En tales casos, usar fórmulas diferentes para cada búsqueda llevará demasiado tiempo. ¿Qué tal crear una fórmula de búsqueda dinámica que pueda buscar por el encabezado proporcionado? Sí, podemos hacer esto.
Esta fórmula se llama fórmula COINCIDIR INDICE, o una fórmula de búsqueda 2d.
¿Cómo solucionar el problema?
Para que la fórmula se entienda primero, debemos revisar un poco las siguientes funciones. link: / lookup-formulas-excel-index-function [INDEX function]
-
link: / lookup-formulas-excel-match-function [MATCH function]
link: / lookup-formulas-excel-index-function [La función INDICE] devuelve el valor en un índice dado en una matriz.
link: / lookup-formulas-excel-match-function [MATCH function] devuelve el índice de la primera aparición del valor en una matriz (matriz de una sola dimensión).
Ahora haremos una fórmula usando las funciones anteriores. La función de coincidencia devolverá el índice del valor de búsqueda1 en el campo de encabezado de fila. Y otra función COINCIDIR devolverá el índice del valor de búsqueda2 en el campo de encabezado de columna. Los números de índice ahora se introducirán en la función INDICE para obtener los valores bajo el valor de búsqueda de los datos de la tabla 2D.
Fórmula genérica:
= INDEX ( data , MATCH ( lookup_value1, row_headers, 0 , MATCH ( lookup_value2, column_headers, 0 ) ) )
Datos: matriz de valores dentro de la tabla sin encabezados lookup_value1: valor para buscar en el row_header.
row_headers: matriz de índice de fila para buscar.
lookup_value1: valor a buscar en el column_header.
column_headers: matriz de índice de columna para buscar.
Ejemplo:
Las declaraciones anteriores pueden ser complicadas de entender. Entonces, comprendamos esto usando la fórmula en un ejemplo. Aquí tenemos una lista de puntajes obtenidos por los estudiantes con su lista de materias. Necesitamos encontrar el puntaje para un estudiante (Gary) y una asignatura (estudios sociales) específicos como se muestra en la imagen a continuación.
El valor Student1 debe coincidir con la matriz Row_header y el valor Subject2 debe coincidir con la matriz Column_header.
Usa la fórmula en la celda J6:
= INDEX ( table , MATCH ( J5, row, 0 , MATCH ( J4, column, 0 ) ) )
Explicación:
La función COINCIDIR hace coincidir el valor de Estudiante en la celda J4 con la matriz de encabezado de fila y devuelve su posición 3 * como un número.
La función COINCIDIR hace coincidir el valor de Asunto en la celda J5 con la matriz de encabezado de columna y devuelve su posición 4 * como un número.
-
La función INDICE toma el número de índice de fila y columna, busca en los datos de la tabla y devuelve el valor coincidente.
-
El argumento de tipo MATCH se fija en 0. Ya que la fórmula extraerá la coincidencia exacta.
Aquí los valores de la fórmula se dan como referencias de celda y row_header, table y column_header como rangos con nombre.
Como puede ver en la instantánea anterior, obtuvimos el puntaje obtenido por el estudiante Gary en la asignatura de Estudios Sociales como 36.
Demuestra que la fórmula funciona bien y, en caso de duda, consulte las notas a continuación para comprenderlo.
Ahora usaremos la coincidencia aproximada con encabezados de fila y encabezados de columna como números. La coincidencia aproximada solo toma los valores numéricos ya que no hay forma de que se aplique a los valores de texto. Aquí tenemos un precio de valores según la Altura y Ancho del producto.
Necesitamos encontrar el precio para una altura (34) y un ancho (21) específicos, como se muestra en la instantánea a continuación.
El valor Alto1 debe coincidir con la matriz Row_header y el valor Width2 debe coincidir con la matriz Column_header.
Usa la fórmula en la celda K6:
= INDEX (data , MATCH (K4, Height, 1 , MATCH ( K5, Width, 1 ) ) )
Explicación:
La función COINCIDIR hace coincidir el valor de Altura en la celda K4 con la matriz de encabezado de fila y devuelve su posición 3 * como un número.
La función COINCIDIR hace coincidir el valor de Ancho en la celda K5 con la matriz de encabezado de columna y devuelve su posición 2 * como un número.
-
La función INDICE toma el número de índice de fila y columna, busca en los datos de la tabla y devuelve el valor coincidente.
-
El argumento de tipo MATCH se fija en 1. Ya que la fórmula extraerá la coincidencia aproximada.
Aquí los valores de la fórmula se dan como referencias de celda y row_header, data y column_header como rangos con nombre como se menciona en la instantánea anterior.
Como puede ver en la instantánea anterior, tenemos el Precio obtenido por altura (34) y Ancho (21) como 53.10. Demuestra que la fórmula funciona bien y, en caso de duda, consulte las notas a continuación para obtener más información.
Notas:
-
La función devuelve el error #NA si el argumento de la matriz de búsqueda para la función COINCIDIR es una matriz 2 D, que es el campo de encabezado de los datos ..
-
La función coincide con el valor exacto ya que el argumento de tipo de coincidencia con la función COINCIDIR es 0.
-
Los valores de búsqueda se pueden dar como referencia de celda o directamente usando el símbolo de comillas («) en la fórmula como argumentos.
Espero que haya entendido cómo usar la tabla de búsqueda en 2 D usando la función INDICE & COINCIDIR en Excel. Explore más artículos en Excel valor de búsqueda aquí. Por favor, siéntase libre de indicar sus consultas a continuación en el cuadro de comentarios.
Sin duda le ayudaremos.
Artículos relacionados
link: / lookup-formulas-use-index-and-match-to-lookup-value [Use INDEX y MATCH para buscar valor]: Función INDEX & MATCH para buscar el valor según sea necesario.
` link: / summing-sum-range-with-index-in-excel [SUM rango con INDICE en Excel] `: Use la función INDICE para encontrar la SUMA de los valores según sea necesario.
` link: / contando- excel-sum-function [Cómo usar la función SUM en Excel] `: Encuentra la SUMA de números usando la función SUM explicada con el ejemplo.
` link: / lookup-formulas-excel-index-function [Cómo para usar la función INDICE en Excel] `: Encuentre el ÍNDICE de la matriz usando la función INDICE explicada con el ejemplo.
link: / lookup-formulas-excel-match-function [Cómo usar la función COINCIDIR en Excel]: Encuentra el COINCIDIR en la matriz usando el valor INDICE dentro de la función COINCIDIR explicado con el ejemplo.
link: / lookup-formulas-excel-lookup-function [Cómo usar la función BUSCAR en Excel]: Encuentra el valor de búsqueda en la matriz usando la función BUSCAR explicada con el ejemplo.
link: / formulas-and-functions-introduction-of-vlookup-function [Cómo usar la función VLOOKUP en Excel]: Busque el valor de búsqueda en la matriz usando la función VLOOKUP explicada con el ejemplo.
link: / lookup-formulas-hlookup-function-in-excel [Cómo usar la función HLOOKUP en Excel]: Encuentre el valor de búsqueda en la matriz usando la función HLOOKUP explicada con el ejemplo.
Artículos populares
link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-aumentar-su-productividad [50 atajos de Excel para aumentar su productividad]
link: / excel-generals-how-to-edit-a-dropdown-list-in-microsoft-excel [Editar una lista desplegable]
link: / excel-range-name-absolute-reference-in-excel [Referencia absoluta en Excel]
link: / tips-conditional-formatting-with-if-statement [Si con formato condicional]
enlace: / fórmulas-lógicas-si-función-con-comodines [Si con comodines]
enlace: / lookup-formulas-vlookup-by-date-in-excel [Vlookup por fecha]
link: / excel-text-edit-and-format-join-first-and-last-name-in-excel [Unir nombre y apellido en excel]