How to pull Data from another Sheet in Excel

In this post, we will show you how to pull data from another sheet in Excel. While copying and pasting data is simple, pulling data from one sheet to another offers significant advantages in Excel.

When you pull data using formulas, the data is linked to the source. This ensures that when the data in one sheet changes, the linked data in the other sheet updates automatically. This saves time and reduces the risk of mistakes, keeping the data consistent across all worksheets without the need for manual updates.

How to pull Data from another Sheet in Excel

To pull data from another sheet in Excel, you may use one of the following methods depending on your needs:

Use Cell References
Use the VLOOKUP function
Use the INDEX and MATCH functions

These methods provide versatile options for pulling data between sheets, regardless of which Excel version you are using.

Let us see this in detail

1] Use Cell References

Using cell references is the simplest method for pulling data from another sheet in Excel and is ideal for small datasets where you need to reference a few cells rather than managing large amounts of data.

Let’s say we have a workbook with 2 sheets. Sheet 1 contains the source dataset, as shown in the image below:

To pull data from Sheet 1 into Sheet 2 using cell references, click on the tab for Sheet 2, and set up your column headers (in cells A1, B1, and C1)

In cell A2 of Sheet 2, enter the formula to reference the first row of data from Sheet 1:

=Sheet1!A2

In cell B2 of Sheet 2, enter:

=Sheet1!B2

In cell C2 of Sheet 2, enter:

=Sheet1!C2

Now select cells A2, B2, and C2, and drag the fill handle down to copy the formulas for the remaining rows (up to row 11, since we have 10 rows of data).

After following these steps, Sheet 2 will display the data from Sheet 1. If any changes are made to the data in Sheet 1, they will automatically update in Sheet 2 as well.

2] Use the VLOOKUP function

Using VLOOKUP is ideal if you’re searching for specific data in a column and want to pull corresponding information from another sheet.

Now let’s say we want to retrieve employee information, specifically the ‘Gender’, from Sheet 1 based on a list of employee codes provided in Sheet 2, as shown in the image below:

In cell B2 of Sheet 2, enter the following formula to get the Gender:

=VLOOKUP(A2,Sheet1!B2:C11,2,FALSE)

In the above formula:

B2 is the value we’re looking up (the Employee Code).
Sheet1!B$2:C$11 is the range in Sheet 1 that contains the data.
2 indicates which column from the range we want to return (2 returns the Gender – column C in the range).
FALSE: This means we want an exact match

Next, select cell B2 and drag the fill handle down to copy the formulas for all the Employee Codes listed in column A.

This will dynamically pull data based on the Employee Codes listed in Sheet 2. If any changes are made to Sheet 1, the information in Sheet 2 will automatically update when the Employee Codes are referenced.

3] Use the INDEX and MATCH functions

The INDEX and MATCH functions are ideal for situations where your dataset grows or when you need to perform lookups in different column orders. For example, if you want to pull the Gender based on the Employee Code from Sheet 1 into Sheet 2, you will enter the following formula in cell B2 of Sheet 2:

=INDEX(Sheet1!C$2:C$11, MATCH(A2, Sheet1!B$2:B$11, 0))

In the above formula,

The INDEX(Sheet1!C$2:C$11, …) part specifies that you want to return a value from column C (Gender) in Sheet 1.
The MATCH(A2, Sheet1!B$2:B$11, 0) part looks for the Employee Code in cell A2 within the range of Employee Codes in Sheet 1.
Zero (0) indicates you want an exact match.

The MATCH function returns the relative row number of the matching Employee Code, which the INDEX function then uses to retrieve the corresponding Gender.

Similarly, you may select cell B2 and drag the fill handle down to copy the formula for all the Employee Codes listed in column A.

That’s all! I hope you find this useful.

Read: How to delete Multiple Rows in Excel in one go.

How do I pull specific data from one Excel sheet to another?

One of the simplest ways to pull specific data from one Excel sheet to another is to use cell references, where you directly link cells from the source sheet to the destination sheet by entering a formula in the target cell. For more complex data retrieval, you may use the VLOOKUP function or a combination of INDEX and MATCH functions.

How to copy specific rows in Excel to another sheet automatically?

If you have Excel 365 or Excel 2021, you can use the FILTER function to automatically copy specific rows from one sheet to another based on certain criteria. If you have an earlier version like Excel 2019 or 2016, you can utilize the Advanced Filter feature to set criteria and copy matching rows to another sheet. You can also add an extra column in your dataset where you use formulas (such as IF statements) to flag specific rows that meet certain criteria and then apply the AutoFilter feature to display only the rows that meet your criteria.