En este artículo, aprenderemos cómo buscar valores en una tabla 2d usando una función INDEX-MATCH-MATCH en Excel.
Guión:
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 de COINCIDENCIA DE ÍNDICE, o digamos una fórmula de búsqueda 2d.
¿Como resolver el problema?
Para que la fórmula se entienda primero, debemos revisar un poco las siguientes funciones
- Función INDICE
- Función COINCIDIR
La función INDICE devuelve el valor en un índice dado en una matriz.
La función COINCIDIR devuelve el índice de la primera aparición del valor en una matriz (matriz de una sola dimensión).
Ahora crearemos 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:
= INDICE (datos, COINCIDIR (valor_buscado1, encabezados_fila, 0, COINCIDIR (valor_buscado2, encabezados_columna, 0)))
Datos: matriz de valores dentro de la tabla sin encabezados
lookup_value1 : valor para buscar en el encabezado de fila.
encabezados_fila : Matriz de índice de fila para buscar.
lookup_value1 : valor para buscar en column_header.
cabeceras_columna : columna Matriz de índice 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 puntuaciones obtenidas 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 instantánea 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:
= INDICE (tabla, COINCIDIR (J5, fila, 0, COINCIDIR (J4, columna, 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 Asunto 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 el ancho del producto. Necesitamos encontrar el precio para una altura (34) y una anchura (21) específicas, 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:
= INDICE (datos, COINCIDIR (K4, Alto, 1, COINCIDIR (K5, Ancho, 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) & 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 del 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 & MATCH en Excel. Explore más artículos en el valor de búsqueda de Excel aquí. No dude en indicar sus consultas a continuación en el cuadro de comentarios. Sin duda te ayudaremos.
Utilice ÍNDICE y COINCIDIR para buscar valor : Función INDICE & MATCH para buscar el valor requerido.
Rango SUM con INDICE en Excel : Utilice la función INDICE para encontrar la SUMA de los valores según sea necesario.
Cómo utilizar la función SUMA en Excel : Encuentre la SUMA de números usando la función SUMA explicada con el ejemplo.
Cómo usar la función INDICE en Excel : Encuentre el ÍNDICE de la matriz usando la función ÍNDICE explicada con el ejemplo.
Cómo usar la función COINCIDIR en Excel : Busque el COINCIDIR en la matriz usando el valor INDICE dentro de la función COINCIDIR explicada con el ejemplo.
Cómo usar la función BUSCAR en Excel : Busque el valor de búsqueda en la matriz usando la función BÚSQUEDA explicada con el ejemplo.
Cómo utilizar la función BUSCARV en Excel : Busque el valor de búsqueda en la matriz usando la función BUSCARV explicada con el ejemplo.
Cómo utilizar la función HLOOKUP en Excel : Busque el valor de búsqueda en la matriz usando la función HLOOKUP explicada con el ejemplo.
articulos populares
50 atajos de Excel para aumentar su productividad
Editar una lista desplegable
Referencia absoluta en Excel
Si con formato condicional
Si con comodines
Vlookup por fecha
Unir nombre y apellido en Excel
