How to Get Data from Another Sheet Based on Cell Value in Excel

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.

• Press Enter to apply the formula.

• Then, drag the Fill Handle icon down the column.

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.

• Press Enter to apply the formula.

• Then, drag the Fill Handle icon down the column.

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.

• Press Enter to apply the formula.

• Then, drag the Fill Handle icon down the column.

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.

Further Readings

• How to Update One Excel Worksheet from Another Sheet Automatically
• Macro to Copy Data from One Workbook to Another Based on Criteria