Select ‘ Customer_Report’ and click OK. We could select the cell range, but because we named the range of data, we can simply recall the name. We are looking up the invoice number in the data on the ‘Customer Report’ worksheet. So, where are we looking up the lookup_value. We are using the invoice number as the lookup value. On the ‘ Invoice Report’ worksheet, click in cell E4. VLOOKUP(lookup_value, table_array, col_index_num, ) The last argument is optional as it’s in square brackets. Let’s now compare the lists on both worksheets using VLOOKUP. In the Name box, type ‘ Customer_Report’ to name the range of data. On the ‘ Customer Report’ worksheet, click on a cell in the data. This means that you won’t need to select the cell ranges and make them absolute. To make life easier, name the range of data on the ‘ Customer Report’ worksheet before constructing the VLOOKUP formula. We’re going to build our VLOOKUP formula in stages. USING VLOOKUP TO COMPARE TWO LISTS OR COLUMNS The lookup value should be in the first column as VLOOKUP can only look up information from left to right. It’s best practice to run a check for duplicate values on the column you are using as the lookup value. I’m going to use the Invoice number as there is a chance that the date or the invoice amount could be duplicated somewhere in the data. In this example, both workbooks have an invoice number, a date, and an amount so that I could use any of these as my lookup value. To use VLOOKUP to compare two lists, there needs to be at least one matching piece of information for each record. In the client payment record on the right, this invoice is missing. In the invoice report generated by the accountant on the left, invoice no, 2083 is listed. A more efficient approach would be to use VLOOKUP to run a comparison. 
In this scenario, the accountant could print out both lists and spend time with a big yellow highlighter, marking invoices that are missing from the client log. At the end of the year, the client sends their invoice log to their accountant for reconciliation. The client also keeps their own lists of invoices. If you want to save yourself a huge amount of time in Excel when reconciling data, it’s worth taking the time to learn how to use VLOOKUP to compare two lists or columns of data.įor example, maybe you are an accountant who keeps a record of all invoices generated by a client. OBJECTIVEĬompare two lists using the VLOOKUP function in Excel. Suitable for users of Excel 2010, 2013, 2016, 2019, and Excel for Microsoft 365. Home > Microsoft Excel > Compare Two Lists Using VLOOKUP Compare Two Lists Using VLOOKUP Power Pivot, Power Query and DAX in Excel.
0 kommentar(er)
