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.
How to Get Data from Another Sheet Based on Cell Value in Excel: 4 Suitable Ways
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 the 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 VLOOKUPfunction.
- 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 HLOOKUPfunction.
- 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.
Read More: How to Pull Data from Multiple Worksheets in Excel
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 & Filtergroup.
- As a result, the Advanced Filter dialog box will appear.
- Then, select Copy to another location from the Actionsection.
- After that, select the range of cells B4 to E12 from the Dataset 2worksheet.
- Then, in the Criteria range section, select the range of cells B4 to B5 from the Advanced Filterworksheet.
- 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.
Download Practice Workbook
Download the practice workbook below.
Get Data from Another Sheet Based on Cell Value.xlsx
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 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.
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 ToExtract Data Excel|Learn Excel
Get FREE Advanced Excel Exercises with Solutions!