Excel-Use VLOOKUP para comparar dos listas

Paso 1: Hay algunas reglas, así que comprobamos estas primeras

El cliente había enviado una hoja de cálculo de Excel muy básica que mostraba una lista de sus registros de pago por orden de compra, número de factura, cantidad y fecha.

El administrador (llamémoslo Guy) generó un informe de Excel del sistema de cuentas de su empresa. Luego procedimos a usar ambas hojas de trabajo de Excel para hacer nuestra reconciliación.

Sin embargo, antes de que pudiéramos comenzar a usar la función VLOOKUP, expliqué las complejidades de obtener sus datos en un formato que garantice que el VLOOKUP funcione.

Excel necesitaba lo siguiente:

• Dentro del libro de trabajo del cliente y el libro de trabajo del chico, debía haber al menos una pieza de información coincidente para cada registro. Por ejemplo, ambos libros de trabajo tenían el número de factura y el número de orden de compra para cada registro de transacción. Decidimos usar el número de factura en el libro de trabajo de Guy para encontrar una coincidencia con el mismo número de factura en el libro de trabajo del cliente. Esta información se denomina «Valor de búsqueda».
• El valor de búsqueda debe ser único, es decir, no se puede repetir en la lista del cliente varias veces. Afortunadamente para Guy solo había un registro para cada factura. En las facturas comerciales y los números de orden de compra casi siempre son únicos para una sola transacción, por lo que son excelentes para usar como Valores de búsqueda. Otros ejemplos son códigos de productos, números de cliente o de cuenta, números de empleados o códigos de departamento. Siempre es una buena práctica comprobar que no hay duplicados antes de realizar su VLOOKUP.
• El valor de búsqueda debía ubicarse en la primera columna de la lista en el libro de trabajo del cliente. Desafortunadamente, no lo fue. Estaba ubicado en la segunda columna (columna B), por lo que cortamos y pegamos la columna frente al número de orden de compra. columna. Esta es una regla importante cuando se utiliza la función VLOOKUP. Si el valor de búsqueda no está en la primera columna, la búsqueda no funcionará.

Una vez que decidimos nuestro Valor de búsqueda y reorganizamos la lista en el libro de trabajo del cliente, estábamos listos para crear nuestra VLOOKUP.

Paso 2: Insertar la función VLOOKUP

La ‘ V ‘en VLOOKUP significa «vertical». Una búsqueda vertical se utiliza para buscar datos específicos en la primera columna de una tabla de datos. Una vez que encuentra la fila que contiene los datos que está buscando, rebota a otra columna en la misma tabla de datos y devuelve información de ella.

Decidimos insertar la función VLOOKUP en el libro de trabajo de Guy. De esa manera, podríamos usar la función para hacer referencia al número de factura de su lista y verificar el mismo número de factura en la primera columna de la lista del cliente. Si encontrara una coincidencia, devolvería a la celda la cantidad que el cliente tenía contra el número de factura. Luego terminaríamos con la cantidad del chico y la cantidad del cliente en la misma hoja de trabajo. Entonces podríamos comparar fácilmente los dos y averiguar qué transacciones no coincidían.

Creamos un nuevo encabezado de columna llamado ‘Registro del cliente’, colocándolo al final de la lista existente de Guy. Colocando el selector de celdas en la celda E5 insertamos la función VLOOKUP.

Para insertar la función, hicimos clic en la pestaña Fórmulas y, a continuación, desde el grupo Biblioteca de funciones, hicimos clic en el botón Insertar función.

Consejo: la función VLOOKUP también se puede insertar haciendo clic en el botón Insertar función en la Barra de fórmulas.

En el cuadro seleccionar una categoría seleccionamos Buscar y Referencia. En el cuadro de lista Seleccionar una función, seleccionamos VLOOKUP y luego hicimos clic en Aceptar.

En este punto, necesitábamos ingresar los valores apropiados en los cuadros de argumentos de función. Te explicaré cada una de las discusiones.

• Valor de búsqueda: Este era el número de factura guardado en la celda C5. Una vez que ingresamos esto en el cuadro de valor de búsqueda, Excel lo guardó en memoria y esperó a que completáramos los argumentos restantes.

• Table_array – La matriz de tablas es la lista que contiene los detalles de la transacción en la hoja de trabajo del cliente. VLOOKUP buscará una coincidencia para el ‘Valor de búsqueda’ en la primera columna de la matriz de tablas. Guy navegó hasta la hoja de trabajo del cliente y luego hizo clic y arrastró por toda el área que contenía todos los detalles de las transacciones. Incluyó la columna «Fecha», pero A4:C26 también habría estado bien, ya que la columna «Cantidad» está dentro de este rango. Tenga en cuenta que Guy hizo que el rango fuera absoluto para que la fórmula se pudiera copiar fácilmente.

• Col_index_num – Esta es la columna de la que Guy quería extraer el monto de la factura del cliente. Esta era la columna 3 de la lista de transacciones del cliente. Las columnas se numeran de izquierda a derecha, siendo la primera columna en el rango de matriz de tabla la columna 1. Tenga cuidado de no confundir los números de columna y las letras de columna. A pesar de que el monto de la factura se mantiene en la columna «C», Excel solo desea la ubicación del número de índice de la columna, no la letra de la columna.

• Range_lookup – La Búsqueda de rangos controla si está buscando una coincidencia exacta con su ‘Valor de búsqueda’. Le expliqué a Guy que si ingresaba VERDADERO o dejaba el cuadro vacío, Excel buscaría su número de factura, pero si no podía encontrar una coincidencia, devolvería el siguiente valor más grande que es menor que el número de factura. Por ejemplo, si no pudiera encontrar la factura 2466, encontraría el registro de 2465 y devolvería el importe correspondiente. Para que esto funcionara, la lista de clientes debía ordenarse primero por número de factura. Sin embargo, si se introduce’ FALSE ‘ en el cuadro, Excel buscará una coincidencia exacta para el valor de búsqueda. El tipo quería una coincidencia exacta de su número de factura, así que entró en FALSO en la caja. También pasé la sugerencia de que puede ingresar 0 (cero) en lugar de la palabra ‘FALSO’ y Excel hará lo mismo.

Con todos los argumentos introducidos, Guy hizo clic en ACEPTAR.

Excel pasó a través de cada uno de los valores suministrados en el cuadro de argumentos. La función utilizó el número de factura (Valor de búsqueda) para buscar un número de factura coincidente en la columna de la izquierda de los registros del cliente (Matriz de tablas). Una vez que se encontró el número de factura, saltó a través del número especificado de columnas (número de índice de columna) en la misma fila para devolver el monto de la factura.

Afortunadamente, como puede ver a continuación, el cliente tenía el mismo valor de factura que Guy.

Guy luego copió la función VLOOKUP para todas las transacciones de facturas.
En segundos pudo ver tres errores #N / A (errores de valor no disponible). Esto mostró dónde el cliente en realidad no tenía un registro de estas facturas. Luego encontró rápidamente las diferencias en los montos de las facturas de él y del cliente. Inmediatamente pudo ver que la factura 2464 estaba corta en 1 100.00, muy probablemente un error de teclado.

Paso 3: la doble comprobación

Ahora habíamos comparado con éxito nuestros registros con los del cliente. Sin embargo, como una doble comprobación decidimos hacer una comparación a partir de los registros del cliente, en caso de que nos hubiéramos perdido algo.

Esta vez creamos el VLOOKUP en la hoja de trabajo del cliente. Lo primero que necesitábamos hacer era mover la columna factura para que fuera la primera columna de nuestra lista de transacciones (Matriz de tablas). Después de hacer esto, insertamos la función VLOOKUP en una nueva columna llamada ‘Nuestro registro’.

Guy copió la función y luego creó la columna ‘Comparación’. Nuestro doble cheque confirmó que el cliente había pagado menos de US 1 100 en la factura 2464. Nuestro control también mostró que el cliente había registrado el número de factura de la factura 2466 como 2646.

Conclusión

Guy ahora puede volver al cliente para discutir la información que puede haber sido ingresada por error y proporcionar copias de las facturas que el cliente no tenía un registro de recepción.

No hace falta decir que la función VLOOKUP ahorró a Guy y a sus compañeros de trabajo una gran cantidad de tiempo. El gerente de Capacitación y Desarrollo se puso en contacto conmigo después de la capacitación para decirme que la mejora de la eficiencia en el departamento había ahorrado a su empresa 10 de 1000 dólares.

Cubrimos la función VLOOKUP en nuestros cursos de Excel Stage 3. Si está tratando con grandes datos, este entrenamiento es definitivamente para usted.