Cómo usar VLOOKUP() en Excel para encontrar registros faltantes

En Microsoft Excel, he utilizado muchas soluciones diferentes para comparar listas. A veces, la solución encuentra duplicados en una o más columnas; otras veces, la solución se centra en encontrar valores únicos. Pero, ¿qué pasa si quieres comparar dos conjuntos de datos en Excel para determinar si falta un registro en uno de los conjuntos?

Por ejemplo, en Excel, quieres comparar un conjunto de órdenes de compra entrantes con una lista exhaustiva de órdenes de compra distribuidas. Afortunadamente, no es tan difícil como podrías pensar. En este tutorial, te mostraré cómo usar VLOOKUP() en Excel para encontrar registros faltantes en un conjunto de datos en comparación con una lista exhaustiva.

Estoy utilizando Microsoft 365 en un sistema de Windows 10 de 64 bits, pero puedes utilizar cualquier versión y Excel for the web admite VLOOKUP(). Para tu comodidad, puedes descargar los archivos de demostración en formato .xlsx y .xls.

¿Cuál es el problema que intentamos resolver en Excel?

Si tu empresa ofrece soporte continuo, probablemente estés familiarizado con el sistema de órdenes de compra. Cuando un cliente llama y hace una solicitud, tomas una orden de compra en blanco, completas los detalles y continúas satisfaciendo la solicitud. Cuando el empleado cumple con la solicitud, completa el formulario de orden de compra y lo devuelve al sistema. Utilizaré el término "abierta" para describir una orden de compra en proceso y "cerrada" para describir una orden de compra completada. Puedes considerar la lista cerrada como un subconjunto de la lista abierta. Aunque la mayoría de los sistemas son ahora en línea, la orden de compra en papel es cosa del pasado, pero nos proporciona un ejemplo simple.

La Figura A muestra dos objetos de tabla. En el lado izquierdo, "PODistributed" realiza un seguimiento de las órdenes de compra distribuidas al personal, es decir, las órdenes de compra abiertas. En el lado derecho, "POReceived" es una lista de órdenes de compra y su fecha de devolución, lo que significa que el trabajo está terminado, es decir, estas son órdenes de compra cerradas.

Figura A

Cómo crear una lista de estilos en Microsoft Word - Tutorial paso a paso

Tal como está, no hay una conciliación entre las dos tablas. Necesitamos una forma de resaltar las órdenes de compra abiertas cuando no haya un número coincidente en la lista cerrada, lo que significa que la orden de compra y la solicitud del cliente todavía están en proceso. Incluso con listas cortas como estas, es probable que cometas un error si simplemente confías en la visualización.

Ahora que comprendes lo que se requiere, vamos a agregar una función VLOOKUP() en Excel para mostrar las órdenes de compra abiertas.

Cómo usar VLOOKUP() para comparar dos conjuntos de datos en Microsoft Excel

La función VLOOKUP() ha sido fundamental para muchas aplicaciones de Excel. Debido a que las tablas no admiten funciones de matriz dinámicas, como la nueva función XLOOKUP(), esta solución utiliza la antigua VLOOKUP().

La sintaxis de la función VLOOKUP() de Excel es la siguiente:

VLOOKUP(valor_buscado, rango_buscar, índice_columna, [valor_predeterminado])

El argumento valor_buscado identifica el valor a buscar, rango_buscar identifica el rango en el que buscar el valor_buscado, índice_columna identifica el valor devuelto cuando la función encuentra una coincidencia, y el argumento opcional valor_predeterminado te permite especificar una coincidencia exacta. Al utilizar FALSO para este argumento opcional, VLOOKUP() devolverá el valor #N/A si valor_buscado no tiene una coincidencia, y eso es en lo que confiamos.

Cómo editar el menú contextual del clic derecho en Windows 10 File Explorer

Para continuar, ingresa una de las siguientes funciones en la celda D3 en la hoja PO Distributed, la lista abierta, y copia las funciones en las celdas restantes:

=VLOOKUP([@[PO Number]],'PO Received'!$B$3:$B$13,1,FALSE)

=VLOOKUP('PO Distributed'!B3,'PO Received'!$B$3:$B$13,1,FALSE)

Utiliza la segunda opción si no estás utilizando un objeto de tabla. La referencia !$B$3:$B$13 en ambas funciones debe ser absoluta.

En Excel, el objeto de tabla se expandirá automáticamente (Figura B). Hay varios registros donde la función no encontró una coincidencia en la lista de órdenes de compra cerradas con respecto a la lista principal de órdenes de compra. Estos registros aún están abiertos. Saber cuáles órdenes de compra están abiertas es importante, especialmente cuando han estado abiertas durante mucho tiempo.

Figura B

Cómo crear y usar perfiles diferentes en Google Chrome

La función es un poco compleja, así que veamos cómo se evalúa utilizando el registro de la fila 4:

=VLOOKUP('PO Distributed'!B3,'PO Received'!$B$3:$B$13,1,FALSE)

=VLOOKUP(102,{103;101;106;104;110;109;107;108;113;115;111},1,FALSE)

=VLOOKUP(102,{103;101;106;104;110;109;107;108;113;115;111},102,FALSE)

El conjunto de órdenes de compra cerradas no incluye el valor 102, por lo que el argumento FALSO obliga a la función a devolver el valor #N/A. Si el valor 102 estuviera en el conjunto, la función devolvería 102.

Podrías detenerte si sabes qué significan los valores #N/A, sin embargo, podemos agregar una regla de formato condicional basada en los resultados de la función VLOOKUP() de Excel para resaltar los registros donde la función VLOOKUP() devuelve este error. Sigue estos pasos para agregar la regla de formato condicional.

¡Aprovecha al máximo Google Workspace Essentials Starter sin gastar un centavo!

1. Selecciona las celdas B3:D19, que son las órdenes de compra abiertas en la tabla PODistributed. No incluyas las celdas del encabezado.
2. En la pestaña Inicio, haz clic en Formato condicional y luego elige Nueva regla en el menú desplegable.
3. En el cuadro de diálogo resultante, haz clic en la opción Usar una fórmula para determinar qué celdas se van a formatear en la parte superior.
4. En la parte inferior, ingresa la fórmula =ESNA($D3) y haz clic en Formato. La referencia de columna debe ser absoluta ($D).
5. Haz clic en la pestaña Relleno, selecciona el color rojo y luego haz clic en Aceptar una vez. Figura C muestra la función y el formato.
6. Haz clic en Aceptar para volver a la hoja. El resultado será un enfoque visual en las órdenes de compra abiertas.

Figura C

Puede que decidas ocultar la columna auxiliar, pero te recomiendo que no lo hagas. Es fácil olvidar los valores ocultos a lo largo del tiempo, lo que puede dificultar la solución de problemas de errores de dependencia. Como ambas listas son tablas, el sistema se actualizará automáticamente a medida que ingreses nuevos registros en las dos listas.

En Newsmatic nos especializamos en tecnología de vanguardia, contamos con los artículos mas novedosos sobre Software, allí encontraras muchos artículos similares a Cómo usar VLOOKUP() en Excel para encontrar registros faltantes , tenemos lo ultimo en tecnología 2023.