Búsquedas basadas en varias columnas a la vez

En el artículo de hoy vamos a ver cómo podemos realizar búsquedas dentro de una tabla basadas en elementos de varias columnas a la vez. Supongamos que tenemos la siguiente tabla:

Queremos introducir en la ENTRADA DE DATOS la Referencia y el Tipo para, en la SALIDA DE DATOS, obtener información sobre el Coste, el PVP y el Descuento asociado (Dcto), información con la que, además, realizaremos algunos cálculos:

Como parece evidente necesitamos realizar una búsqueda basada en dos elementos de distintas columnas (Referencia y Tipo). Para solucionar este problema (con el menor esfuerzo posible) debemos seguir los siguientes pasos:

1. Seleccionamos el rango D2:H11 y vamos al menú Insertar/Nombres/Crear. Por defecto, en el menú que aparecerá, vienen marcadas las opciones nombres en columna superior y nombres en columna izquierda. Dejamos seleccionada solamente nombres en columna superior y aceptamos. De esta manera habremos creado los nombres de cada columna de nuestra tabla (puede comprobarlo abriendo el Cuadro de Nombres que se encuentra a la izquierda de la barra de fórmulas) que se corresponderán precisamente con los rótulos de cada columna de dicha tabla.

2. El siguiente paso será preparar la entrada de datos con sendas listas desplegables para que seleccionemos de éstas la Referencia y Tipo que nos interese. Para ello nos situamos en la celda B2 y vamos al menú Datos/Validación de datos y seleccionamos Permitir/Lista. En el cuadro Origen escribimos =Referencia  ,que es el nombre del rango que contiene los elementos de nuestra lista.

3. Nos situamos en la celda B3 y vamos al menú Datos/Validación de datos y seleccionamos Permitir/Lista. En el cuadro Origen seleccionamos el rango E3:E5 (fíjese que en este rango están los tres tipos que se repiten para las distintas referencias y por ello no hace falta seleccionar el rango E3:E11 o utilizar el Nombre de Rango Tipo). Aceptamos.

Ya tenemos la entrada de datos preparada para seleccionar, por medio de listas desplegables, la referencia y tipo con las que nos interese trabajar. Ya sólo nos queda formular... 

Para resolver "el problema" de buscar en varias columnas (en nuestro caso dos) vamos a utilizar las funciones INDIRECTO, COINCIDIR, CONCATENAR e INDICE, con entrada matricial (las funciones INDIRECTO y COINCIDIR han sido utilizadas en numerosos ejemplos de este blog que puede consultar si lo necesita). La función CONCATENAR nos permite lo que su nombre indica, es decir, unir varios argumentos en uno solo. El operador "&" hace las mismas funciones que CONCATENAR. Puede probar esta función situándose, por ejemplo, en la celda D14 de nuestro modelo y escribiendo la siguiente fórmula: ="El Coste de "&D3&E3&" es de "&F3    , y el resultado que obtendrá será: El Coste de MontañaGTX es de 452

La función INDICE, por otro lado, nos proporciona el dato que se encuentra en una fila y columna determinada de una matriz.

4. Nos situamos en B6 y escribimos la siguiente fórmula:

=INDICE(INDIRECTO(A6);COINCIDIR($B$2&$B$3;Referencia&Tipo;0))

PERO NO PULSAMOS ENTER. Como estamos trabajando con matrices dentro de la función COINCIDIR, tenemos que realizar una entrada matricial y para ello debemos pulsar las teclas Ctrl+Shift+Enter. El resultado será la misma fórmula pero con llaves:

{=INDICE(INDIRECTO(A6);COINCIDIR($B$2&$B$3;Referencia&Tipo;0))}

Fíjese que hemos utilizado "&" (que es lo mismo que CONCATENAR) dentro de la función COINCIDIR. De esta manera le estamos pidiendo que una B2 y B3 y que los busque en el rango llamado Referencias y Tipo, que también hemos unido con "&". El resultado de esta parte de la fórmula será el número de fila de nuestra tabla que ocupa la unión de B2 y B3. Conseguido esto sólo nos queda indicarle de qué columna queremos la información y asunto resuelto. Fíjese que en B6 queremos obtener el Coste. Para ello sería suficiente con poner el nombre de este rango (que es precisamente Coste) como primer argumento de la función INDICE. Pero en B7 y en B8 necesitaremos el PVP y el Descuento respectivamente. Para resolverlo con una sola fórmula he utilizado INDIRECTO(A6) en vez del nombre de rango correspondiente (y, por supuesto, referencias absolutas en B2 y B3). 

5. Copiamos la fórmula escrita en B6 en las celdas B7 y B8. Problema resuelto.

6. Las fórmulas de B10 y B11 no tienen ningún misterio, a saber:

    en B10:   =B7*(1-B8)        para calcular el precio menos el descuento.

    en B11:   =(B10-B6)/B6   para calcula el porcentaje de margen tras el descuento.