Qué saber
- Primero, cree una función ÍNDICE, luego inicie la función COINCIDIR anidada ingresando el argumento Valor_buscado.
- A continuación, agregue el argumento Lookup_array seguido del argumento Match_type, luego especifique el rango de columna.
- Luego, convierta la función anidada en una fórmula matricial presionando Ctrl+ Shift+ Enter. Finalmente, agregue los términos de búsqueda a la hoja de trabajo.
Este artículo explica cómo crear una fórmula de búsqueda que utilice varios criterios en Excel para buscar información en una base de datos o tabla de datos mediante una fórmula de matriz. La fórmula de matriz implica anidar la función COINCIDIR dentro de la función ÍNDICE. La información cubre Excel para Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 y Excel para Mac.
Sigue el tutorial
Para seguir los pasos de este tutorial, ingrese los datos de muestra en las siguientes celdas, como se muestra en la imagen a continuación. Las filas 3 y 4 se dejan en blanco para acomodar la fórmula de matriz creada durante este tutorial. (Tenga en cuenta que este tutorial no incluye el formato que se ve en la imagen).
- Ingrese el rango superior de datos en las celdas D1 a F2.
- Ingrese el segundo rango en las celdas D5 a F11.
Crear una función ÍNDICE en Excel
La función ÍNDICE es una de las pocas funciones en Excel que tiene múltiples formas. La función tiene una forma de matriz y una forma de referencia. El formulario de matriz devuelve los datos de una base de datos o tabla de datos. El formulario de referencia proporciona la referencia de celda o la ubicación de los datos en la tabla.
En este tutorial, el formulario de matriz se usa para encontrar el nombre del proveedor de los widgets de titanio, en lugar de la referencia de celda a este proveedor en la base de datos.
Siga estos pasos para crear la función ÍNDICE:
- Seleccione la celda F3 para convertirla en la celda activa. Esta celda es donde se ingresará la función anidada.
-
Ir a Fórmulas.
Image - Seleccione Buscar y referencia para abrir la lista desplegable de funciones.
- Seleccione ÍNDICE para abrir el cuadro de diálogo Seleccionar argumentos.
- Seleccione matriz, núm_fila, núm_columna.
- Seleccione OK para abrir el cuadro de diálogo Argumentos de función. En Excel para Mac, se abre Formula Builder.
- Coloque el cursor en el cuadro de texto Array.
-
Res alte las celdas D6 a F11 en la hoja de trabajo para ingresar el rango en el cuadro de diálogo.
Deje abierto el cuadro de diálogo Argumentos de función. La fórmula no está terminada. Completará la fórmula en las instrucciones a continuación.
Image
Iniciar la función COINCIDIR anidada
Al anidar una función dentro de otra, no es posible abrir el generador de fórmulas de la segunda función, o anidada, para ingresar los argumentos necesarios. La función anidada debe ingresarse como uno de los argumentos de la primera función.
Al ingresar funciones manualmente, los argumentos de la función están separados entre sí por una coma.
El primer paso para ingresar la función COINCIDIR anidada es ingresar el argumento Valor_buscado. Lookup_value es la ubicación o referencia de celda para el término de búsqueda que se buscará en la base de datos.
El valor de búsqueda acepta solo un criterio o término de búsqueda. Para buscar varios criterios, amplíe Lookup_value concatenando o uniendo dos o más referencias de celda mediante el símbolo de y comercial (&).
- En el cuadro de diálogo Argumentos de función, coloque el cursor en el cuadro de texto Row_num.
- Introduzca COINCIDIR(.
- Seleccione la celda D3 para ingresar esa referencia de celda en el cuadro de diálogo.
- Ingrese & (el ampersand) después de la referencia de celda D3 para agregar una segunda referencia de celda.
- Seleccione la celda E3 para ingresar la segunda referencia de celda.
-
Enter , (una coma) después de la referencia de celda E3 para completar la entrada del argumento Lookup_value de la función MATCH.
Image En el último paso del tutorial, los valores de búsqueda se ingresarán en las celdas D3 y E3 de la hoja de trabajo.
Complete la función COINCIDIR anidada
Este paso cubre la adición del argumento Lookup_array para la función COINCIDIR anidada. Lookup_array es el rango de celdas que busca la función MATCH para encontrar el argumento Lookup_value agregado en el paso anterior del tutorial.
Debido a que se identificaron dos campos de búsqueda en el argumento Lookup_array, se debe hacer lo mismo para Lookup_array. La función COINCIDIR solo busca una matriz para cada término especificado. Para ingresar varios arreglos, use el ampersand para concatenar los arreglos juntos.
- Coloque el cursor al final de los datos en el cuadro de texto Row_num. El cursor aparece después de la coma al final de la entrada actual.
- Res alte las celdas D6 a D11 en la hoja de trabajo para ingresar el rango. Este rango es la primera matriz que busca la función.
- Ingrese & (un ampersand) después de que la celda haga referencia a D6:D11. Este símbolo hace que la función busque dos matrices.
- Res alte las celdas E6 a E11 en la hoja de trabajo para ingresar el rango. Este rango es la segunda matriz que busca la función.
-
Introduzca , (una coma) después de la referencia de celda E3 para completar la entrada del argumento Lookup_array de la función MATCH.
Image - Deje el cuadro de diálogo abierto para el siguiente paso del tutorial.
Agregue el argumento de tipo MATCH
El tercer y último argumento de la función MATCH es el argumento Match_type. Este argumento le dice a Excel cómo hacer coincidir Lookup_value con valores en Lookup_array. Las opciones disponibles son 1, 0 o -1.
Este argumento es opcional. Si se omite, la función usa el valor predeterminado de 1.
- Si Match_type=1 o se omite, MATCH encuentra el valor más grande que es menor o igual que Lookup_value. Los datos de Lookup_array deben ordenarse en orden ascendente.
- Si Match_type=0, MATCH encuentra el primer valor que es igual a Lookup_value. Los datos de Lookup_array se pueden clasificar en cualquier orden.
- Si Match_type=-1, MATCH encuentra el valor más pequeño que es mayor o igual que Lookup_value. Los datos de Lookup_array deben ordenarse en orden descendente.
Ingrese estos pasos después de la coma ingresada en el paso anterior en la línea Row_num en la función ÍNDICE:
- Ingrese 0 (un cero) después de la coma en el cuadro de texto Row_num. Este número hace que la función anidada devuelva coincidencias exactas con los términos ingresados en las celdas D3 y E3.
-
Ingrese ) (un paréntesis de cierre) para completar la función COINCIDIR.
Image - Deje el cuadro de diálogo abierto para el siguiente paso del tutorial.
Finalizar la función ÍNDICE
La función MATCH está lista. Es hora de moverse al cuadro de texto Column_num del cuadro de diálogo e ingresar el último argumento para la función ÍNDICE. Este argumento le dice a Excel que el número de columna está en el rango D6 a F11. Este rango es donde encuentra la información devuelta por la función. En este caso, un proveedor de widgets de titanio.
- Coloque el cursor en el cuadro de texto Column_num.
-
Introduzca 3 (el número tres). Este número le dice a la fórmula que busque datos en la tercera columna del rango D6 a F11.
Image - Deje el cuadro de diálogo abierto para el siguiente paso del tutorial.
Crear la fórmula de matriz
Antes de cerrar el cuadro de diálogo, convierta la función anidada en una fórmula matricial. Esta matriz permite que la función busque múltiples términos en la tabla de datos. En este tutorial, se combinan dos términos: Widgets de la columna 1 y Titanium de la columna 2.
Para crear una fórmula de matriz en Excel, presione CTRL, SHIFT y ENTERteclas simultáneamente. Una vez presionada, la función está rodeada de llaves, lo que indica que la función ahora es una matriz.
- Seleccione OK para cerrar el cuadro de diálogo. En Excel para Mac, seleccione Listo.
- Seleccione la celda F3 para ver la fórmula, luego coloque el cursor al final de la fórmula en la barra de fórmulas.
- Para convertir la fórmula en una matriz, presione CTRL+ SHIFT+ ENTER.
- Un error N/A aparece en la celda F3. Esta es la celda donde se ingresó la función.
-
El error N/A aparece en la celda F3 porque las celdas D3 y E3 están en blanco. D3 y E3 son las celdas donde la función busca el valor de búsqueda. Después de agregar datos a estas dos celdas, el error se reemplaza con información de la base de datos.
Image
Agregar los criterios de búsqueda
El último paso es agregar los términos de búsqueda a la hoja de trabajo. Este paso coincide con los términos Widgets de la columna 1 y Titanium de la columna 2.
Si la fórmula encuentra una coincidencia para ambos términos en las columnas correspondientes de la base de datos, devuelve el valor de la tercera columna.
- Seleccionar celda D3.
- Introducir Widgets.
- Seleccionar celda E3.
- Escriba Titanio y presione Entrar.
- El nombre del proveedor, Widgets Inc., aparece en la celda F3. Este es el único proveedor de la lista que vende widgets de titanio.
-
Seleccione la celda F3. La función aparece en la barra de fórmulas encima de la hoja de cálculo.
{=ÍNDICE(D6:F11, COINCIDIR(D3&E3, D6:D11&E6:E11, 0), 3)}
En este ejemplo, solo hay un proveedor de widgets de titanio. Si hubo más de un proveedor, la función devuelve el proveedor que figura primero en la base de datos.
Image
