How To Compare Data In Two Excel Sheets Using Vlookup

If your lookup value and lookup range are stored in dissimilar Excel workbooks (spreadsheets), VLOOKUP volition exercise the job easily. However, if you need to vlookup another Excel Online file, this will create some difficulties. Read on to learn more than.

How to vlookup another workbook in Excel

We imported a dataset from Google Sheets to Excel using Coupler.io, a solution for automated data exports from multiple apps and sources.

Read more nearly Microsoft Excel integrations for data consign on a schedule.

The data was imported to the workbook titled "dataset" – this is our lookup range. The lookup values are stored in some other spreadsheet, titled "users".

Let's vlookup these two spreadsheets. Complete the following steps:

Type =vlookup( in the B2 cell of the users workbook.

Type "=vlookup(" in the B2 cell of the users workbook.

Specify the lookup value. You lot tin enter a string wrapped in quotes or reference a jail cell only like we did:

Enter a comma/semicolon (depending on the listing separator defined under your regional settings), click on the spreadsheet with the range you desire to look up and select the desired range. In our case, the unfinished formula looks like this:

=vlookup(A2,[dataset.xlsx]dataset!$A$2:$F$101

Then, basically, you can manually type the range to lookup using the post-obit sample:

[dataset.xlsx]dataset!$A$2:$F$101

[dataset.xlsx] – proper noun of the spreadsheet
dataset! – name of the worksheet
$A$2:$F$101 – locked range to lookup

In the formula bar, add a comma/semicolon and enter the column number, which contains the matching value to return. In our example, the number is 4 (column D).

=vlookup(A2,[dataset.xlsx]dataset!$A$2:$F$101,4

Add a comma/semicolon and enter Simulated to return the verbal friction match. Close the bracket and striking enter

=vlookup(A2,[dataset.xlsx]dataset!$A$2:$F$101,4,False)

To return the matching values for other users, we can drag the formula downwardly:

Or utilise an array formula:

You need to specify an array as a lookup value

=VLOOKUP(A2:A66,[dataset.xlsx]dataset!$A$ii:$F$101,iv,FALSE)

Then select an array where to return the matching values, insert the VLOOKUP formula into the formula bar and printing Ctrl+Shift+Enter for Windows (Command+Render for Mac)

How to vlookup another Excel Online file

Unfortunately, the catamenia described above for VLOOKUP does not piece of work with Excel Online workbooks. You can't select the range to look up from another spreadsheet. The simplest style to solve this is to import the required data from another workbook (Excel to Excel) using Coupler.io and perform the vlookup every bit above.

Or you lot can look up from another spreadsheet using this syntax:

                =VLOOKUP("lookup_value",'https://d.docs.live.net/spreadsheet-id/Docs/[spreadsheet-name.xlsx]sheet-name'!locked-lookup-range,column_number,match)

Let's see how it works in the instance. We'll use the same workbooks from above – dataset and users – but edited in Excel Online.

The parameters we demand for our VLOOKUP formula are:

"lookup_value" – A2

You can find the spreadsheet-id of your Excel Online workbook in the URL bar.

The spreadsheet name and the range should not crusade whatever trouble. But do not forget to lock the lookup_range using $ symbols. Now the VLOOKUP formula looks like this:

=vlookup(A2,'https://d.docs.alive.internet/ec25d9990d879c55/Docs/[dataset.xlsx]dataset'!$A$two:$F$50,column_number,friction match)

Equally the last step, specify the column number and the match parameters. Now we tin run our VLOOKUP formula:

=vlookup(A2,'https://d.docs.alive.net/ec25d9990d879c55/Docs/[dataset.xlsx]dataset'!$A$ii:$F$50,four,false)

12-vlookup-another-spreadsheet-excel-online

With this syntax, you can either drag your VLOOKUP formula or utilise an assortment formula as we described in a higher place.

Back to Blog

Access your data
in a simple format for free!

Start Free