Cómo crear una fórmula de búsqueda de Excel con múltiples criterios

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 de matriz 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 use múltiples criterios en Excel para encontrar información en una base de datos o tabla de datos usando 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.

Siga junto con 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 utiliza para buscar 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:

1. Seleccione la celda F3 para convertirla en la celda activa. Esta celda es donde se ingresará la función anidada.
2. Ir a Fórmulas.

   

3. Elija Búsqueda y referencia para abrir la lista desplegable de funciones.
4. Seleccione ÍNDICE para abrir el cuadro de diálogo Seleccionar argumentos.
5. Elija array,row_num,column_num.
6. Seleccione Aceptar para abrir el cuadro de diálogo Argumentos de función. En Excel para Mac, se abre Formula Builder.
7. Coloque el cursor en el cuadro de texto Array.
8. Resalte 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.
   

Inicie 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.

Lookup_value 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 con el símbolo de y comercial (&).

1. En el cuadro de diálogo Argumentos de función, coloque el cursor en el cuadro de texto Row_num.
2. Ingrese COINCIDIR (.
3. Seleccione la celda D3 para ingresar esa referencia de celda en el cuadro de diálogo.
4. Ingrese & (el ampersand) después de la referencia de celda D3 para agregar una segunda referencia de celda.
5. Seleccione la celda E3 para ingresar la segunda referencia de celda.
6. Ingrese , (una coma) después de la referencia de celda E3 para completar la entrada del argumento Valor_buscado de la función COINCIDIR.

   
   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 COINCIDIR 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.

1. 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.
2. Resalte 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.
3. Ingresa & (un ampersand) después de que la celda haga referencia a D6:D11. Este símbolo hace que la función busque dos matrices.
4. Resalte 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.
5. Ingrese , (una coma) después de la referencia de celda E3 para completar la entrada del argumento Lookup_array de la función MATCH.

   

6. 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 utiliza 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 busca el valor más pequeño que sea 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:

1. 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.
2. Introduzca ) (un paréntesis de cierre) para completar la función PARTIDO.

   

3. Deje el cuadro de diálogo abierto para el siguiente paso del tutorial.

Terminar la función ÍNDICE

La función MATCH está hecha. 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.

1. Coloque el cursor en el cuadro de texto Column_num.
2. 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.

   

3. 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 las teclas CTRL, SHIFT y ENTER simultáneamente. Una vez presionada, la función está rodeada de llaves, lo que indica que la función ahora es una matriz.

1. Seleccione Aceptar para cerrar el cuadro de diálogo. En Excel para Mac, seleccione Listo.
2. 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.
3. Para convertir la fórmula en una matriz, presione CTRL + MAYÚS + ENTRAR.
4. Aparece un error #N/A en la celda F3. Esta es la celda donde se ingresó la función.
5. 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.

   

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.

1. Seleccione la celda D3.
2. Ingrese Widgets.
3. Seleccione la celda E3.
4. Escriba Titanio y presione Entrar.
5. El nombre del proveedor, Widgets Inc., aparece en la celda F3. Este es el único proveedor de la lista que vende widgets de titanio.
6. Seleccione la celda F3. La función aparece en la barra de fórmulas encima de la hoja de cálculo. 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.
   
{=INDEX(D6:F11,MATCH(D3&E3,D6:D11&E6:E11,0),3)}