El tutorial muestra algunas fórmulas diferentes para realizar búsquedas bidimensionales en Excel. Solo mira las alternativas y elige tu favorita 🙂
Al buscar algo en sus hojas de cálculo de Excel, la mayoría de las veces buscará verticalmente en columnas u horizontalmente en filas. Pero a veces es necesario mirar tanto las filas como las columnas. En otras palabras, su objetivo es encontrar un valor en la intersección de una determinada fila y columna. Se llama búsqueda de matriz (también conocido como bidimensional o búsqueda bidireccional), y este tutorial muestra cómo hacerlo de 4 maneras diferentes.
Excel ÍNDICE COINCIDIR Fórmula COINCIDIR
La forma más popular de realizar una búsqueda bidireccional en Excel es utilizando ÍNDICE COINCIDIR COINCIDIR. Esta es una variación de la fórmula clásica ÍNDICE COINCIDIR a la que agrega una función COINCIDIR más para obtener los números de fila y columna:
ÍNDICE (matriz_datos, COINCIDIR (vbuscar_valor, buscar_columna_rango, 0), COINCIDIR (hbuscarvalor, buscar_fila_rango, 0))
Como ejemplo, hagamos una fórmula para extraer una población de cierto animal en un año determinado de la tabla a continuación. Para empezar, definimos todos los argumentos:
- Data_array – B2:E4 (celdas de datos, sin incluir los encabezados de fila y columna)
- Vlookup_value – H1 (animal objetivo)
- Lookup_column_range – A2:A4 (encabezados de fila: nombres de animales) – A3:A4
- Hlookup_value – H2 (año objetivo)
- Lookup_row_range – B1:E1 (encabezados de columna: años)
Reúna todos los argumentos y obtendrá esta fórmula para la búsqueda bidireccional:
=INDEX(B2:E4, MATCH(H1, A2:A4, 0), MATCH(H2, B1:E1, 0))
Cómo funciona esta fórmula
Si bien puede parecer un poco complejo a primera vista, la lógica de la fórmula es realmente sencilla y fácil de entender. La función ÍNDICE recupera un valor de la matriz de datos en función de los números de fila y columna, y dos funciones COINCIDIR proporcionan esos números:
INDEX(B2:E4, row_num, column_num)
Aquí, aprovechamos la capacidad de MATCH(lookup_value, lookup_array, [match_type]) para devolver un posición relativa de lookup_value en lookup_array.
Entonces, para obtener el número de fila, buscamos el animal de interés (H1) en los encabezados de fila (A2:A4):
MATCH(H1, A2:A4, 0)
Para obtener el número de columna, buscamos el año objetivo (H2) en los encabezados de columna (B1:E1):
MATCH(H2, B1:E1, 0)
En ambos casos, buscamos la coincidencia exacta estableciendo el tercer argumento en 0.
En este ejemplo, la primera PARTIDA devuelve 2 porque nuestro valor de búsqueda virtual (oso polar) se encuentra en A3, que es la segunda celda en A2:A4. La segunda COINCIDIR devuelve 3 porque el valor hlookup (2000) se encuentra en D1, que es la tercera celda en B1:E1.
Dado lo anterior, la fórmula se reduce a:
INDEX(B2:E4, 2, 3)
Y devuelva un valor en la intersección de la segunda fila y la tercera columna en la matriz de datos B2:E4, que es un valor en la celda D3.
Fórmula VLOOKUP y MATCH para búsqueda bidireccional
Otra forma de realizar una búsqueda bidimensional en Excel es mediante una combinación de las funciones BUSCARV y COINCIDIR:
BUSCARV(valor_buscar_v, matriz_tabla, COINCIDIR(valor_buscarh, rango_fila_buscar, 0), FALSO)
Para nuestra tabla de muestra, la fórmula toma la siguiente forma:
=VLOOKUP(H1, A2:E4, MATCH(H2, A1:E1, 0), FALSE)
Dónde:
- Table_array – A2:E4 (celdas de datos que incluyen encabezados de fila)
- Vlookup_value – H1 (animal objetivo)
- Hlookup_value – H2 (año objetivo)
- Lookup_row_range – A1:E1 (encabezados de columna: años)
Cómo funciona esta fórmula
El núcleo de la fórmula es la función BUSCARV configurada para una coincidencia exacta (el último argumento establecido en FALSO), que busca el valor de búsqueda (H1) en la primera columna de la matriz de la tabla (A2:E4) y devuelve un valor de otro columna en la misma fila. Para determinar de qué columna devolver un valor, utilice la función COINCIDIR que también está configurada para coincidencia exacta (el último argumento establecido en 0):
MATCH(H2, A1:E1, 0)
COINCIDIR busca el valor en H2 en los encabezados de columna (A1:E1) y devuelve la posición relativa de la celda encontrada. En nuestro caso, el año objetivo (2010) se encuentra en E1, que ocupa el quinto lugar en la matriz de búsqueda. Entonces, el número 5 va al argumento col_index_num de BUSCARV:
VLOOKUP(H1, A2:E4, 5, FALSE)
BUSCARV lo toma desde allí, encuentra una coincidencia exacta para su valor de búsqueda en A2 y devuelve un valor de la quinta columna en la misma fila, que es la celda E2.
¡Nota IMPORTANTE! Para que la fórmula funcione correctamente, table_array (A2:E4) de BUSCARV y lookup_array de MATCH (A1:E1) deben tener el mismo número de columnas; de lo contrario, el número pasado por MATCH a col_index_num será incorrecto (no corresponderá al posición de la columna en table_array).
Función BUSCARX para buscar en filas y columnas
Recientemente, Microsoft ha introducido una función más en Excel que pretende reemplazar todas las funciones de búsqueda existentes, como BUSCARV, BUSCARH e ÍNDICE COINCIDIR. Entre otras cosas, BUSCARX puede buscar en la intersección de una fila y una columna específicas:
BUSCARX(valor_buscar_v, rango_columna_buscar_v, BUSCARX(valor_buscarh, rango_fila_buscarh, matriz_datos))
Para nuestro conjunto de datos de muestra, la fórmula es la siguiente:
=XLOOKUP(H1, A2:A4, XLOOKUP(H2, B1:E1, B2:E4))
Nota. Actualmente, XLOOKUP es una función beta, que solo está disponible para los suscriptores de Office 365 que forman parte del programa Office Insiders.
Cómo funciona esta fórmula
La fórmula utiliza la capacidad de BUSCARX para devolver una fila o columna completa. La función interna busca el año de destino en la fila del encabezado y devuelve todos los valores de ese año (en este ejemplo, para el año 1980). Esos valores van a la volver_matriz argumento del XLOOKUP externo:
XLOOKUP(H1, A2:A4, {22000;25000;700}))
La función BUSCARX externa busca el animal objetivo en los encabezados de columna y devuelve el valor en la misma posición de return_array.
Fórmula SUMPRODUCT para búsqueda bidireccional
La función SUMPRODUCT es como una navaja suiza en Excel: puede hacer muchas cosas más allá de su propósito designado, especialmente cuando se trata de evaluar múltiples criterios.
Para buscar dos criterios, en filas y columnas, utilice esta fórmula genérica:
SUMPRODUCT(vlookup_column_range = vlookup_value) * (hlookup_row_range = hlookup_value), data_array)
Para realizar una búsqueda bidireccional en nuestro conjunto de datos, la fórmula es la siguiente:
=SUMPRODUCT((A2:A4=H1) * (B1:E1=H2), B2:E4)
La siguiente sintaxis también funcionará:
=SUMPRODUCT((A2:A4=H1) * (B1:E1=H2) * B2:E4)
Cómo funciona esta fórmula
En el corazón de la fórmula, comparamos dos valores de búsqueda con los encabezados de fila y columna (el animal objetivo en H1 con todos los nombres de animales en A2:A4 y el año objetivo en H2 con todos los años en B1:E1):
(A2:A4=H1) * (B1:E1=H2)
Esto da como resultado 2 matrices de valores VERDADERO y FALSO, donde los VERDADEROS representan coincidencias:
{FALSE;FALSE;TRUE} * {FALSE,TRUE,FALSE,FALSE}
La operación de multiplicación convierte los valores VERDADERO y FALSO en 1 y 0 y produce una matriz bidimensional de 4 columnas y 3 filas (las filas están separadas por punto y coma y cada columna de datos por una coma):
{0,0,0,0;0,0,0,0;0,1,0,0}
Las funciones SUMAPRODUCTO multiplican los elementos de la matriz anterior por los elementos de B2:E4 en las mismas posiciones:
{0,0,0,0;0,0,0,0;0,1,0,0} * {22000,13800,8500,3500;25000,23000,22000,20000;700,2000,2300,2500}
Y debido a que multiplicar por cero da cero, solo sobrevive el elemento correspondiente a 1 en la primera matriz:
SUMPRODUCT({0,0,0,0;0,0,0,0;0,2000,0,0})
Finalmente, SUMPRODUCT suma los elementos de la matriz resultante y devuelve un valor de 2000.
Nota. Si su tabla tiene más de una fila o encabezados de columna con el mismo nombre, la matriz final contendrá más de un número distinto de cero, y todos esos números se sumarán. Como resultado, obtendrá una suma de valores que cumplen ambos criterios. Es lo que hace que la fórmula SUMPRODUCT sea diferente de INDEX MATCH MATCH y VLOOKUP, que devuelven la primera coincidencia encontrada.
Búsqueda de matriz con rangos con nombre (intersección explícita)
Otra forma asombrosamente simple de realizar una búsqueda de matriz en Excel es mediante el uso de rangos con nombre. Así es cómo:
Parte 1: Nombrar columnas y filas
La forma más rápida de nombrar cada fila y cada columna en su tabla es esta:
- Selecciona toda la tabla (A1:E4 en nuestro caso).
- En la pestaña Fórmulas, en el grupo Nombres definidos, haga clic en Crear a partir de la selección o presione el atajo Ctrl + Shift + F3.
- En el cuadro de diálogo Crear nombres a partir de la selección, seleccione Fila superior y Columna izquierda, y haga clic en Aceptar.
Esto crea automáticamente nombres basados en los encabezados de fila y columna. Sin embargo, hay un par de advertencias:
- Si los encabezados de sus columnas y/o filas son números o contienen caracteres específicos que no están permitidos en los nombres de Excel, no se crearán los nombres para dichas columnas y filas. Para ver una lista de los nombres creados, abra el Administrador de nombres (Ctrl + F3). Si faltan algunos nombres, defínalos manualmente como se explica en Cómo nombrar un rango en Excel.
- Si algunos de los encabezados de fila o columna contienen espacios, los espacios se reemplazarán con guiones bajos, por ejemplo, Polar_bear.
Para nuestra tabla de muestra, Excel creó automáticamente solo los nombres de las filas. Los nombres de las columnas deben crearse manualmente porque los encabezados de las columnas son números. Para superar esto, simplemente puede anteponer los números con guiones bajos, como _1990.
Como resultado, tenemos los siguientes rangos con nombre:
Parte 2: hacer una fórmula de búsqueda de matriz
Para obtener un valor en la intersección de una fila y una columna dadas, simplemente escriba una de las siguientes fórmulas genéricas en una celda vacía:
=nombre_fila nombre_columna
O viceversa:
=nombre_columna nombre_fila
Por ejemplo, para obtener la población de ballenas azules en 1990, la fórmula es tan simple como:
=Blue_whale _1990
Si alguien necesita instrucciones más detalladas, los siguientes pasos lo guiarán a través del proceso:
- En una celda donde desea que aparezca el resultado, escriba el signo de igualdad (=).
- Comience a escribir el nombre de la fila de destino, por ejemplo, Blue_whale. Después de escribir un par de caracteres, Excel mostrará todos los nombres existentes que coincidan con su entrada. Haga doble clic en el nombre deseado para ingresarlo en su fórmula:
- Después del nombre de la fila, escriba un espacioque funciona como el operador de intersección en este caso.
- Ingrese el nombre de la columna de destino (_1990 en nuestro caso).
- Tan pronto como se ingresen los nombres de la fila y la columna, Excel resaltará la fila y la columna correspondientes en su tabla, y presione Entrar para completar la fórmula:
Su búsqueda de matriz está lista y la siguiente captura de pantalla muestra el resultado:
Así es como buscar en filas y columnas en Excel. ¡Gracias por leer y espero verte en nuestro blog la próxima semana!