Let’s say you have two columns with some textual or numeric values and you need to identify which values are present in both columns and which aren’t. The VLOOKUP function will help you complete this task.
How to VLOOKUP two columns in Excel
We imported a dataset from Google Sheets to Excel using Coupler.io, a solution for automatic data exports from multiple apps and sources.
Read more about Microsoft Excel integrations for data export on a schedule.
On the dataset, we have two columns: Old users and New users. What we need is to compare the values from these columns to identify duplicates – values that are present in both columns.
We can do this using the VLOOKUP function applied as an array formula. Select an array, which will be not less than the arrays in your VLOOKUP formula, insert the following formula to the formula bar and press Ctrl+Shift+Enter for Windows (Command+Return for Mac) – this will apply an array formula in Excel:
=VLOOKUP(A2:A66,C2:C66,1,FALSE)
A2:A66– a lookup arrayC2:C66– the range to look up1– the column to return the matching values from
To get rid of #N/A, let’s nest our VLOOKUP formula with IFERROR as follows:
=IFERROR(VLOOKUP(A2:A66,C2:C66,1,FALSE),"")
It would also be great to exclude empty cells in the array. You can do this using the UNIQUE function, which is available in Excel 365 or Excel Online. Here is how the formula will look:
=UNIQUE(IFERROR(VLOOKUP(A2:A66,C2:C66,1,FALSE);""),FALSE,TRUE)
How to automate data import to Excel
Now you know how to compare two columns in Excel using VLOOKUP. To get data to Excel, you no longer need to copy and paste it. You can automate your data import to Excel with Coupler.io and save at least 3 hours per week. In addition to 50+ supported data sources, Coupler.io allows you to transform your data on the go:
- Filter and sort data
- Hide and edit columns
- Add formula-based columns to calculate values out of existing data or make inconsistently formatted data more unified.
- Blend data from multiple sources
- Automate data refresh on a schedule as frequently as every 15 minutes
Try it yourself – select the needed source app in the form below and click Proceed.
Start for free. Load data from third-party apps, import data ranges from other workbooks or data warehouses, and more!
