How to Extract Data from Excel Sheet (6 Effective Methods)

If you are looking to extract data from an Excel sheet to another sheet, then you are in right place. You just have to follow along with this article and practice your own Excel file or you can download our practice book. In this tutorial, you are going to learn 6 easy and effective methods to extract data from an Excel sheet.


Download Practice Book

Download the following Excel file for your practice.


6 Methods to Extract Data from an Excel Sheet

Here, we have a data set containing 5 columns and 9 rows including headings. Our mission is to extract data from an Excel worksheet to another worksheet.

Sample dataset to show how to extract data from Excel sheet

Now, let’s discuss the methods one by one.


1. Extract Data from Excel Sheet Using VLOOKUP Function

The VLOOKUP function seeks for a given data in the left-most column of a given dataset and then extracts a value in the same row from a specified column.

Steps: 

Suppose we need to extract the salaries of ID no. 103, 106, and 108 from sheet 1 to sheet 2.

1. Enter the following formula in Cell C13 of Sheet 2.

=VLOOKUP(B13,'Sheet 1'!B5:F12,5,FALSE)

Extract Data from Excel Sheet Using VLOOKUP Function

2. Drag the Fill Handle to the range you need.

Here is the output.

Extract Data from Excel Sheet with VLOOKUP Function: Output

Note: 

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

Here,

  • Lookup_value is the value you want to match
  • Table_array is the data range that you need to look for your value
  • Col_index_num is the corresponding column of the look_value
  • Range_lookup is the boolean value (True or False). 0 (false) refers to an exact match and 1 (true) refers to an approximate match.

Read More: Transfer Data from One Excel Worksheet to Another Automatically with VLOOKUP


2. Pick Data from Excel Sheet Using INDEX-MATCH Formula

INDEX-MATCH combo is a powerful and popular tool in MS Excel to extract data from a particular portion of the table. By applying this combined formula, we can extract data from sheet 1 to sheet 3 based on criteria. For this, you just need to follow the steps below.

Let’s suppose, you want to find the salary for a particular ID. We will use the combo of INDEX and MATCH functions to do so.

Steps: 

1. In cell C13, enter the following formula

=INDEX('Sheet 1'!F5:F12, MATCH(B13,'Sheet 1'!B5:B12,0))

Here,

  • MATCH(B13,’Sheet 1’!B5:B12,0) refers to cell B13 as the lookup_value in the data range B5:B12 for an exact match. It returns 3 because the value is in row number 3.
  • INDEX(‘Sheet 1′!F5:F12, MATCH(B13,’Sheet 1’!B5:B12,0)) refers to Sheet 1 as an array of F5:F12  from where we will get the value.

Extract Data from Excel Sheet Using INDEX-MATCH Combo

2. Press ENTER.

3. Drag the Fill Handle to the range you need.

Here is the output,

Read More: Extract Filtered Data in Excel to Another Sheet (4 Methods)


3. Extract Data from Excel Sheet Using Data Consolidation Tool

In many cases, there is a simpler way to extract data from an Excel sheet using Data Consolidation than VLOOKUP or INDEX-MATCH. I am using two datasets in the same Excel worksheet (Consolidation 1) as input. The result of the consolidation will be shown on a different worksheet (Consolidation 2).

Now, follow the steps below.

Steps:

1. Go to the Consolidation 2 sheet >> Select a Cell (Cell B4 in this example) where you want to put your consolidated result.

2. Then, go to the Data tab >> the Data Tools group >> Click on the Consolidate icon.

A Consolidate Dialog box will pop up.

Consolidate Dialog Box

3. Select the Function you need, then one by one select each table including the headings from the “Consolidation 1” sheet in the Reference box, and click Add.

4. All selected tables from Consolidation Sheet 1 will appear in the All References box. Ensure the Tick mark (top row and left row) in the Label box. Click OK.

Extract Data from Excel Sheet Using Data Consolidation

Here is the result,


Similar Readings


4. Extract Data from Worksheet Using Advanced Filter

You can extract data from an Excel sheet to a different sheet using an Advanced Filter. Follow the written instructions below. In this illustration, the data is on Sheet 5 and will be extracted to Sheet 6.

Steps:

1. Go to Sheet 6 >> Select a Cell (Cell B4 in this illustration)>> Data tab>> click Advanced.

An Advanced Filter window will be opened.

2. Select Copy to Another Location.

3. Click on the List Range box >> Select Sheet 5 and select the entire table with the headings.

4. Choose the criteria range.

5. Then, in Copy to Box, select the cell on sheet 6 (Cell B4 in this example).

6. Click OK.

Extract Data from Worksheet Using Advanced Filter

Here is the result, 

Read More: How to Pull Data from Multiple Worksheets in Excel VBA


5. Pull Data from Another Sheet in Excel with the Help of Name Box

To extract a cell from one Excel sheet to another, you just require knowing the sheet’s name and the cell’s name. Then, by linking them together with an exclamation sign you could copy it. When you need to change the data in one worksheet, the other worksheet where you copied it will be changed automatically.

Suppose we have two worksheets named NameBox1 and NameBox2. We want to extract data from NameBox1 to NameBox2.

Now, follow the steps below.

Steps: 

  • In any cell in NameBox2 (Cell B4 in this example), just enter =NameBox1!C9 >> Press ENTER and you will get values from Cell C9 in your new worksheet.

Pull Data from Another Sheet in Excel with the Help of Name Box

Here is the result,

Or,

  • Type ‘=’ in any cell from NameBox2, then click the NameBox1 sheet and select the cell you need and press ENTER.

Read More: How to Pull Data From Another Sheet Based on Criteria in Excel


6. Extract Data from Excel Sheet with INDEX Function

The INDEX function does the opposite action of the MATCH function and somewhat acts like the VLOOKUP function. You need to tell the function what column and row of data you need, then it will tell you the value of what’s in the cell. Suppose we have two sheets named INDEX 1 and INDEX 2. In INDEX 2 sheet, we will set the Row and Column no. of the data from the INDEX 1 sheet.

Now follow the steps below.

Steps:

  • In Cell D5, enter the following formula.
=INDEX('INDEX 1'!B4:F12,'INDEX 2'!B5,'INDEX 2'!C5)

Extract Data from Excel Sheet with INDEX Function

  • Press ENTER.

Here is the output,

Note:

=INDEX(data range, row number, [column number])

Here,

  • Data range is the entire table of the data
  • Row number of the data is not necessarily the row of the Excel worksheet. If the table starts on row 5 of the worksheet, that will be Row #1.
  • Column number of the data similarly depends on the Table. If the table range starts on column C, that will be column #1.

Read More: How to Extract Data from a List Using Excel Formula (5 Methods)


Conclusion

In this tutorial, I have discussed 6 easy methods on how to extract data from an Excel sheet. I hope you found this article helpful. You can visit our website Exceldemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Hafiz Islam
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo