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

Get FREE Advanced Excel Exercises with Solutions!

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

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

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

1. 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

2. Thank you for this useful article, nicely explained.

3. Very simple, good

• Thanks, FERREIRA for your feedback!

4. 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

5. 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.
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)

6. 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

7. 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)

Advanced Excel Exercises with Solutions PDF