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.
=INDEX('Dataset 1'!C5:G5,MATCH('Dataset 1'!C5,'Dataset 1'!C5:G5,0))

Combining INDEX and MATCH Functions to Get Data from Another Sheet Based on Cell Value in Excel

  • Press Enter to apply the formula.

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

Gathering INDEX and MATCH Functions to Get Data from Another Sheet Based on Cell Value in Excel

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

Using VLOOKUP Function to Get Data from Another Sheet Based on Cell Value in Excel

  • Press Enter to apply the formula.

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

Applying VLOOKUP Function to Get Data from Another Sheet Based on Cell Value in Excel

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

Applying HLOOKUP Function to Get Data from Another Sheet Based on Cell Value in Excel

  • Press Enter to apply the formula.

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

Using HLOOKUP Function to Get Data from Another Sheet Based on Cell Value in Excel

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

Use of Advanced Filter to Get Data from Another Sheet Based on Cell Value in Excel

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

Utilizing Advanced Filter to Get Data from Another Sheet Based on Cell Value in Excel


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

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

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

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

  3. Very simple, good

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

  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)

Leave a reply

ExcelDemy
Logo