Getting data from another spreadsheet based on a cell value might be quite valuable for some projects and reports. Using this process, you can make the work quite easy. In this article, we will show how to get data from another sheet based on the cell value in Excel using Several functions and Excel commands. I hope you find this article very interesting and gain lots of knowledge regarding the topic.

## Download Practice Workbook

Download the practice workbook below.

## 4 Suitable Ways to Get Data from Another Sheet Based on Cell Value in Excel

To get data from another sheet based on cell value, we have found four different methods including several Excel functions and an advanced filter option in Excel. All of these methods are fairly easy to use. Here, we will utilize the combination of **INDEX** and **MATCH** functions to get data from another sheet. Then, we would like to use **VLOOKUP** and **HLOOKUP** functions effectively. The use of advanced filter commands can get you the desired output also.

### 1. Combining INDEX and MATCH Functions

Our first method is based on using the combination of **INDEX** and **MATCH** functions to get data from another sheet based on the cell value in Excel. The **MATCH** function in Excel is used to locate the position of a lookup value in a row, column, or table. The **INDEX** function returns a value or reference of the cell at the intersection of a particular row and column, in a given range.

Using the combination of these two will get you the desired result. We take a dataset that includes several months and the sales amount of several products. Using this dataset, we would like to get the data in another sheet.

To understand the process clearly, follow the steps.

**Steps**

- First, we have to take a new sheet where we want to put the product 1 sales amount from Dataset 1 worksheet.
- Then, set the month’s name in the new sheet.
- After that. select cell
**C5**. - Then, write down the following formula.

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

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

- Then, 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.

### 2. Using VLOOKUP Function

Our second method is based on utilizing** the VLOOKUP function** to get data from another sheet based on the cell value in Excel. The lookup_value can be a single value or an array of values. If you enter an array of values, the function will look for each of the values in the leftmost column and return the same row’s values from the specified column. We would like to use this function to get the required result. We take a dataset that includes sales details.

Using the **VLOOKUP** function, we would like to get the sales amount for the corresponding salesman. Follow the steps carefully.

**Steps**

- First, take a new worksheet where you want to apply the
**VLOOKUP**function. - Select, cell
**C5**. - Then, write down the following formula.

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

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

- Then, 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**.

### 3. Applying HLOOKUP Function

Our third method is based on utilizing **the HLOOKUP function** to get data from another sheet based on the cell value in Excel. **HLOOKUP** function searches for a value in the top row of a table or array of values and returns the value in the same column from the specified row. We would like to use this function to get the required result. Here, We take a dataset that includes sales details.

Using this dataset, we would like to use the **HLOOKUP** function to get the data. Follow the steps carefully.

**Steps**

- First, take a new worksheet where you would like to use the
**HLOOKUP**function. - We would like to get the sales amount.
- Before that, we need to take a helping column which is important for calculation.

- Now, select cell
**E5**. - Then, write down the following formula.

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

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

- Then, 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 does the horizontal lookup to bring back the value from the data. 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.

### 4. Use of Advanced Filter

Our final method is based on using **the Advanced Filter** process. In Microsoft Excel, using Advanced Filter with multiple criteria allows us to add different logic and functions to execute a wide range of extractions of data. It is one of the most common and easiest ways to pull data from another sheet based on criteria. From the following dataset, we would like to pull out the details of the salesmen who sold products in the north.

**Steps**

- First, go to the new worksheet where you would like to put the filtered value.
- Then, create a new column called region which will be used as the criteria in the advanced filter option.

- Next, go to the
**Data**tab on the ribbon. - Then, select the
**Advanced**option from the**Sort & Filter**group.

- As a result, the Advanced Filter dialog box will appear.
- Then, select
**Copy to another location**from the**Action**section. - After that, select the range of cells
**B4**to**E12**from the**Dataset 2**worksheet.

- Then, in the
**Criteria range**section, select the range of cells**B4**to**B5**from the**Advanced Filter**worksheet. - After that, select a place where you want to copy.
- Finally, click on
**OK**.

- As a consequence, we will get the following result. See the screenshot.

## Conclusion

We have shown four different methods to get data from another sheet based on the cell value in Excel. In this article, we have also shown the details of using those certain functions to get data from another sheet based on the cell value. I think we covered all the possible areas of this topic. If you have further questions, feel free to ask in the comment box. Don’t forget to visit our **Exceldemy** page.

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)