Vincular y buscar por listas relacionadas en Excel

Vincular y buscar por listas relacionadas en Excel es muy útil para presentar y asociar datos. Vincular listas significa que si selecciono un valor en una lista, en la siguiente lista mostrará sus valores relacionados. Por ejemplo, Provincia y Distrito , o Producto y Marca. Asimismo, te enseñaré a realizar una búsqueda de un valor en base a estas listas. Por ejemplo, que se muestre el alcalde en base a una provincia y distrito seleccionado, o el precio de un producto en base al tipo de producto y marca.

Vincular dos listas relacionadas en Excel

Para vincular listas vamos a necesitar:

• Usar nombre para un Rango de Celdas.
• Usar la función Indirecto.

Para entender mejor, vamos a incluir un ejemplo de «Tipo de Producto» y  «Marca».  Los tipos de producto serán celular y laptop.

1. Nombrar como rango a los tipos de producto (Celular, Laptop) con el nombre «TipoProducto».
2. Nombrar como rango a cada uno de los tipos de producto, con el nombre «celular» , «laptop».
3.  En la celda de la primera lista, seleccionamos el criterio de validación «Lista» de la opción «Validación de Datos» de la pestaña «Datos».Ingresamos el primer rango  definido  «TipoProducto».
4.  En la celda de la siguiente lista, seleccionamos el criterio de validación «Lista» de la opción «Validación de Datos» de la pestaña «Datos». Ingresamos la función Indirecto y señalamos como referencia a la celda de la primera lista. (Punto 3)

Vincular tres listas relacionadas en Excel

También se requerirá el nombre de Rango y la función Indirecto.

En el ejemplo, vincularemos Departamento, Provincia y Distrito.

1. Por cada departamento, copiamos sus provincias debajo del mismo. Indicaremos el nombre de rango al departamento. Ejemplo, al conjunto de provincias nombraremos «Lima», «Cajamarca».
2. De manera similar, para cada provincia copiamos sus distritos debajo del mismo. Indicaremos el nombre de rango al departamento y provincia para diferenciar del nombre anterior en caso se repita. Ejemplo, al conjunto de distrito de lima llamaremos «LimaLima».
3. En la lista de selección para la celda «Departamento»,  pulsaremos la opción «Validación de Datos», seleccionamos por «Lista» e indicamos los departamentos.
4. Respecto a la celda «Provincia», pulsaremos la opción pulsaremos la opción «Validación de Datos», seleccionamos por «Lista» e indicamos  la función «Indirecto(<celda Departamento>)».
5. Respecto a la celda  «Distrito», pulsaremos la opción pulsaremos la opción «Validación de Datos», seleccionamos por «Lista» e indicamos  la función «Indirecto(<celda Departamento>& <celda Provincia>)».

Vincular y buscar por listas relacionadas en Excel

Para ello necesitaremos conocer:

• Usar nombre para un Rango de Celdas.
• Usar la función Indirecto.
• Usar la función Indice
• Usar la función Coincidir

En este ejemplo, buscaremos el precios según el tipo de producto y su marca relacionada.

1. Utilizaremos los pasos descritos en «Vincular dos listas relacionadas» para cargar los valores de «Tipo de Producto» y «Marca».
2. Asignamos el nombre de rango «Precio» en los datos asociados a Tipo de Producto y Marca.
3. En la celda del Precio escribiremos la siguiente función : «=INDICE(PRECIO,COINCIDIR(L5&L7, PRODUCTO&MARCA,0))»
4. Pulsaremos SHIFT + ENTER.  Si pulsamos sólo la tecla Enter se mostrará un error, esto se debe a que la función es de tipo matricial.

Para entender mejor la fórmula del punto 3, detallo cada parte correspondiente:

a) PRECIO: Nombre de rango que definimos en el paso 2.

b) L5 & L7 : Celdas donde se vincula «Tipo de Producto» y «Marca»  respectivamente.

c) PRODUCTO & MARCA : El nombre de Rango para el «Tipo de Producto» y «Marca» respectivamente.

Video Tutorial

En este videotutorial te mostraré el Excel usado en estos ejemplos.

Espero que estos ejemplos hayan sido útiles,  puedes solicitarme el Excel de los ejemplos que se muestran en el video y en este artículo. Te lo proporciono de manera gratuita, para ello dale me gusta a mi FanPage y solicítalo por ese medio.  Puedes sorprender en tu trabajo presentando en Excel de esta manera, así como también podrás optimizar y mejorar la consistencia e integridad de datos.

Un consejo sobre estas funciones si es que compartes el Excel es que protegas parte de los datos para que no alteren las filas, columnas. Recuerda que sí puedes proteger sólo parte de los datos, en este artículo puedes conocer estos tips: Proteger todo o parte del Excel.