Coupler.io Blog

How to Do VLOOKUP in Excel with Two Spreadsheets

How to Do VLOOKUP in Excel with Two Spreadsheets@2x

Excel VLOOKUP is one of the most useful functions as it allows you to easily navigate your data and save significant time. It can be used to calculate totals and averages, update product prices, quickly look up customer information, and much more.

In this blog post, I explain how to VLOOKUP in Excel with two spreadsheets. In particular, you will learn how to use this function with two different workbooks, with two worksheets, and also how to VLOOKUP another Excel Online file. Let’s dive in!

How to VLOOKUP another workbook in Excel 

Before diving into the details of using the VLOOKUP function, I will need a sample dataset to work with, so this will be our step 1. 

I imported a dataset from Google Sheets to Excel using Coupler.io, a reporting automation platform for business teams that automatically collects and blends data from 400+ sources to Excel and other destinations.

Read more about Microsoft Excel integrations for scheduled data exports or even try it for yourself for free.

Ensure your reports are always up-to-date

Connect your data to Excel with Coupler.io that will keep your workbooks data fresh automatically based on the schedule you set.

Try for free

The data was imported to the workbook titled “dataset” – this is our lookup range. The lookup values are stored in another spreadsheet, titled “users“.

Now, let’s VLOOKUP these two spreadsheets. 

How to VLOOKUP between two workbooks: step-by-step instructions

To VLOOKUP between two workbooks, complete the following steps:

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

So, basically, you can manually type the range to lookup using the following sample:

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

To return the matching values for other users, you can drag the formula down:

Or use an array formula:

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

Now you know how to VLOOKUP between two workbooks. You can go through the same steps to VLOOKUP between multiple workbooks as well, not just two.

How to VLOOKUP between two worksheets

But what if your datasets are not located in different workbooks and you need to VLOOKUP between two worksheets? The process is exactly the same as for using VLOOKUP with two workbooks, which I already described in detail in the section above. The only difference is that you don’t need to include the workbook name in the formula, since both worksheets are part of the same workbook.

Let’s see how this works. In this example, I used Coupler.io to transfer data from Google Analytics to Excel. Now, I have two datasets on two different sheets in the same workbook. Let’s open the first dataset. On sheet 1, I have a list of ad campaigns, and I need to pull the number of clicks for each campaign. This information is located on sheet 2.

Automate data flows to Excel with Coupler.io

Get started for free

To extract the number of clicks, you can use the following formula:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

You already know the main elements:

lookup_value – what you are looking for; in our example, it will be CampaignX

table_array – where to look for the information; here, it’s Sheet2!B2:V20

col_index_num – the column index is the number of the column to return value from; here, it’s 12 – the “Clicks” column on Sheet 2

[range_lookup]– I select FALSE to get the exact match; you can also select TRUE for an approximate match

So, our ready-to-use formula looks like this:

=VLOOKUP(Sheet2!B11; Sheet2!B2:V20; 12; FALSE)

(You can also use commas instead of semicolons – just use the separator specified in your settings).

I insert/type it into the cell where I want to get the returned values and hit enter. Here’s the result:

The formula searched for Campaign X on sheet2 and fetched the corresponding value from the specified column (in our example, column 12 – Clicks). The extracted value was then displayed on sheet1.

Then, I simply drag the formula to the other cells to return the number of clicks for all the campaigns listed in the second column. (For more details, see the GIF in the section above – dragging formulas works in the same way here).

Now, all the range of cells in the “Clicks” column is filled with the data fetched from the second sheet.

As you see, it’s pretty easy to VLOOKUP between two worksheets, especially if you are comfortable with using Excel formulas. But, of course, you can use the same formula to VLOOKUP between multiple sheets, not just two. Hope this helps!

How to VLOOKUP another Excel Online file

Unfortunately, the flow described above for VLOOKUP does not work with Excel Online workbooks. You can’t select the range to look up from another spreadsheet. The simplest way to solve this is to import the required data from another workbook (Excel to Excel) using Coupler.io and perform the vlookup as above.

Or you 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 example. We’ll use the same workbooks from above – dataset and users – but edited in Excel Online. 

The parameters I need for our VLOOKUP formula are:

=vlookup(A2,'https://d.docs.live.net/ec25d9990d879c55/Docs/[dataset.xlsx]dataset'!$A$2:$F$50,column_number,match)
=vlookup(A2,'https://d.docs.live.net/ec25d9990d879c55/Docs/[dataset.xlsx]dataset'!$A$2:$F$50,4,false)

With this syntax, you can either drag your VLOOKUP formula or apply an array formula as I described above.

That’s it! Just one more tip before you go. We all love Excel formulas because they allow us to automate tasks and save time. But using formulas is not the only way to do so. I recommend you try out a data automation solution as well – it allows you to automate even more data tasks and save a lot more time. Try Coupler.io’s Excel integrations with an online app or Excel add-on that works directly in a spreadsheet.

Connect your business data to Excel with Coupler.io

Get started for free

I hope my tutorial helps you confidently VLOOKUP between two spreadsheets, whether they’re in different workbooks, worksheets, or even Online Excel files. Or, in fact, you can VLOOKUP between any number of Excel sheets or files. Many thanks for reading!

Exit mobile version