### Method 1 – Combining INDEX and MATCH Functions

We have a dataset that includes several months and the sales amount of several products. We’ll get the data in another sheet.

**Steps**

- Make a new sheet.
- Insert the month names in the new sheet in column B (B5:B16).
- Select cell
**C5**. - Insert the following formula.

`=INDEX('Dataset 1'!C5:G5,MATCH('Dataset 1'!C5,'Dataset 1'!C5:G5,0))`

- Press
**Enter**to apply the formula.

- Drag the
**Fill Handle**icon down the column.

** Breakdown of the Formula**

**INDEX(‘Dataset 1’!C5:G5,MATCH(‘Dataset 1′!C5,’Dataset 1’!C5:G5,0)):** The **MATCH** function in Excel is used to locate the position of a lookup value in a row, column, or table. Here, cell **C5** is the lookup value and the range of cells **C5** to **G5** defines the lookup array. Finally, the **MATCH** function finds the exact match of a value from the array of another sheet. Then, this returned value will act as an input value of the **INDEX** function. The **INDEX** function returns that value from the given array.

### Method 2 – Using the VLOOKUP Function

We have a sales dataset and will fetch the sales for a salesperson in a different worksheet.

**Steps**

- Make a new worksheet where you want to apply the
**VLOOKUP function.** - Insert the salespeople’s names in column B (B5:B12).
- Select cell
**C5**. - Insert the following formula.

`=VLOOKUP(B5,'Dataset 2'!$B$4:$E$12,4,0)`

- Press
**Enter**to apply the formula.

- Drag the
**Fill Handle**icon down the column.

** Breakdown of the Formula**

**VLOOKUP(B5,’Dataset 2′!$B$4:$E$12,4,0): **The **VLOOKUP function** takes the lookup value and finds the required value using the given lookup array and column number. Here, cell **B5** means Noah which is the lookup value. Then, we provide the lookup array and column number. By using this input, the **VLOOKUP** function gives us the required value that appeared in column **4**.

### Method 3 – Applying the HLOOKUP Function

We’ll use the same sales dataset.

**Steps**

- Make a new worksheet where you would like to use the
**HLOOKUP function.** - Enter the column header which you want to extract in a cell. We would like to get the sales amount.
- Make a helper column D and fill it in with a series starting from 1.

- Select cell
**E5**. - Insert the following formula.

`=HLOOKUP($B$5,'Dataset 2'!$B$4:$E$12,D5+1,0)`

- Press
**Enter**to apply the formula.

- Drag the
**Fill Handle**icon down the column.

** Breakdown of the Formula**

**HLOOKUP($B$5,’Dataset 2′!$B$4:$E$12,D5+1,0):** The **HLOOKUP function** performs a horizontal lookup. Here, we define the lookup value and lookup array. The sales amount is the lookup value. The **HLOOKUP function** search this in the given array and given row number. The helping column is used to define the row number. Finally, the **HLOOKUP function** returns **$2520 **which is the sales amount for the first case.

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

### Method 4 – Use the Advanced Filter

We’ll pull out the details of the salespeople who sold products in the north.

**Steps**

- Go to the new worksheet where you would like to put the filtered value.
- Create a new column called Region which will be used as the criteria in the advanced filter option.

- Go to the
**Data**tab on the ribbon. - Select the
**Advanced**option from the**Sort & Filter**group.

- The
**Advanced Filter**dialog box will appear. - Select
**Copy to another location**from the**Action**section. - Select the range of cells
**B4:****E12**from the**Dataset 2**worksheet.

- In the
**Criteria range**section, select the range of cells**B4**to**B5**from the**Advanced Filter**worksheet. - Select a place where you want to copy.
- Click on
**OK**.

- We will get the following result.

**Download the Practice Workbook**

## Further Readings

- Extract Filtered Data in Excel to Another Sheet
- How to Pull Values from Another Worksheet in Excel
- Pull Same Cell from Multiple Sheets into Master Column in Excel
- Extract Data from One Sheet to Another Using VBA in Excel
- How to Pull Data from Multiple Worksheets in Excel VBA
- Excel Macro: Extract Data from Multiple Excel Files

**<< Go Back To Extract Data Excel | Learn Excel**

A very powerful message and useful presentation. Thank you for presenting this.

James, thanks for your feedback.

Glad to hear that it was useful for you.

Regards

Thank you for this useful article, nicely explained.

Thanks, Surya!

Very simple, good

Thanks, FERREIRA for your feedback!

Hi Mr. Kawser!

I find your website really amazing. I am a newbie in excel and the way you present your excel is fantastic and so easy to understand. I want to compile all of them so I can easily go through them without browsing the internet everytime. I wish you have a pdf also on this. Thanks a lot!

Hi Gilbert,

I am really sorry for not having PDF formats of the Articles.

But you can make your own PDFs. Only thing is: you can use it personally. Not for commercial use, of course.

Hope you understand.

Thanks and regards

Kawser Ahmed

Hi Kawser

Have trouble in retrieving information from 3 excel, with 3 same sheet names.

In 1 excel – sheet 3 is where formula is to go, reference by name is in column A, sheet 1 is where to retrieve information from, Column A is name, Column B is date, Column C is Distance – so on across 20 columns.

Name by latest date, 2nd latest date, & third latest date.

Name appears in sheet 1 Column A 100 times

Dates in sheet 1 Column B from top B6 = 1-01-2020 — B64000 = 5-01-2020 Month/Date/Year & adding.

Some of the Formula’s tried to retrieved from one sheet eg:

Formula =VLOOKUP(A6,RESULTS!A:A,1,FALSE)

=VLOOKUP(A6,RESULTS!A:B,2,FALSE)

=VLOOKUP($A$6,RESULTS!$A$6:$C$90000,3,4)

=MAX(A6=RESULTS!$A$6:$A$90000,RESULTS!$B$6:$B$90000,””)*FALSE

=MAX(IF(A6,RESULTS!A:A6:A90000=A6,B6:B90000)+1)

=INDEX(“RESULTS!A”,MATCH(1,(RESULTS!A=A6)*(RESULTS!A=A6)*1))

Regards

Tony

Hi Tony,

I couldn’t fully understand what you need from your comment. You said you want to retrieve information from Sheet1. But you are looking for information in the “RESULTS” sheet in all of those formulas that you’ve tried.

Can you share the workbook with us? Thanks.

Regards

Md. Shamim Reza (ExcelDemy Team)

Hey Brother

I am trying to write a function that will retrieve a cell value from another sheet on the same file using the name of the sheet identified in as a cell value in the current sheet but cant get this to work. Could you offer a solution?So the name of the sheet appears in a cell as just the name and i want to reference that name to create an address to use in a lookup function

Can you send me the Excel sheet to this email [email protected]? I will take a look at your problem.

Hey

I am looking for way to pull several cells from a large excel based on a key that exists in my sheet. in other words I am looking for a replacement to perform a vlookup for each cell for the same key. In other words I have an excel sheet with some data and would like to add data from the reference excel file to retrieve cells 15,20,45,73 into my sheet to cells F3:I3 for the key in cell A3.

Hi

I couldn’t understand the problem from your comment. Please explain what you need in details and share the workbook if possible. Then we will try our best to find you the solution.

Thanks for reaching out to us. Keep in touch.

Regards

Md. Shamim Reza (ExcelDemy Team)

Good afternoon,

I am trying to move information from worksheet1 to worksheet2 if the worksheet1 has information in it. For example if CELL A2 in worksheet2 is found in worksheet1 and has information on lines B2, C2 and D2, I want that information to be placed in worksheet2 lines B2,C2 and D2.

In worksheet1 CELL A2 through A440 have different SLOT numbers (job numbers) in them. In worksheet2 CELL A2 through A440 MAY have the same SLOT numbers and I want the information in lines B2, C2 and D2 to be brought to worksheet2.

I am not very familiar with all the functions that I know Excel has to offer. I have been trying really hard to use different functions but have found it very difficult.

This is what it looks like worksheet1

Slot # Hire Date Last Name First Name

AA001 01/01/00 DOE JOHN

AA002 02/02/00 RAY JANE

AA003 01/10/00 MI OLIVE

AA004 05/11/24 SO SON

This is what it looks like in worksheet 2

Slot # Hire Date Last Name First Name

AA001

AA002

AA003

AA005

I want to move information from worksheet1 to worksheet2 if the slot # is in both worksheets.

Your help is much appreciated.

Hello

Guadalupe Valdez,To get value from another workbook you can use the VLOOKUP function.

In Worksheet2:

In cell B2 Enter the following formula:

=IFERROR(VLOOKUP($A2,[Worksheet1.xlsx]Sheet1!$A$2:$D$5, 2, FALSE), “”)This formula looks up the Slot number in A2 of Worksheet2 in the range A2

of Worksheet1. The IFERROR function will return an empty string if no match is found.

Then, drag the formula down to A440 cell.

Use the same formula by changing the column number for First and Last name.

In cell C2 of Worksheet2, enter the following formula:

=IFERROR(VLOOKUP($A2, [Worksheet1.xlsx]Sheet1!$A$2:$D$440, 3, FALSE), “”)In cell D2 of Worksheet2, enter the following formula:

=IFERROR(VLOOKUP($A2, [Worksheet1.xlsx]Sheet1!$A$2:$D$440, 4, FALSE), “”)Final Output:

Here, I am attaching the Excel Files:

Worksheet1.xlsxhttps://www.exceldemy.com/wp-content/uploads/2024/06/Worksheet2.xlsxWorksheet2.xlsxRegards

ExcelDemy