Using Excel to get data from another sheet based on cell value

Getting data from another spreadsheet based on a cell value might be quite useful for some projects and reports. In this short article, I will show using Excel how you can get value from another worksheet based on a cell value. I will pull data using two ways: Using the Index and Match function combo and using VLOOKUP/HLOOKUP Excel function.

Getting Data from another worksheet (using INDEX & MATCH)

You’re seeing a worksheet (‘Sheet1’) below with some dummy data.

In the following image, you are seeing my ‘Get’ worksheet data. In the B2 cell, you see I have input value 3/2018. All the values from the ‘Sheet1’ worksheet and under the 3/2018 column are showing under the ‘Data’ heading.

This is the formula that I have used in the cell D4 and then dragged down the formula for 10 cells. You’re also seeing the formulas of cells D5 and D6 cells on the right side of the cells.

=INDEX(Sheet1!$A2:$E2,MATCH<span style="color: #ff0000;">(</span><span style="color: #0000ff;">$B$3</span>,Sheet1!$A$1:$E$1,0<span style="color: #ff0000;">)</span>)

Excel get data from another sheet based on cell value

MATCH Function overview

To understand this formula, you have to understand the MATCH and INDEX functions at first.

Match function returns the relative position of an item in an array that matches a specified value in a specified order.

Syntax of Match function: MATCH (lookup_value, lookup_array, [match_type])

Check out the following worksheet. You will get the idea of how Match function works.

Index Function Overview

Index function returns the intersection value of two rows and columns in an array.

Syntax of Index function:

Array Form: INDEX (array, row_num, [column_num])

Reference Form: INDEX (reference, row_num, [column_num], [area_num])

Check out the following image. The gest of the Index function is shown in this image.

Breakdown of the formula

Let’s now explain how this formula is working.

=INDEX(Sheet1!$A2:$E2,MATCH<span style="color: #ff0000;">(</span><span style="color: #0000ff;">$B$3,</span>Sheet1!$A$1:$E$1,0<span style="color: #ff0000;">)</span>)

This part of this formula, MATCH($B$3, Sheet1!$A$1:$E$1,0), will find an exact match in this array ($A$1:$E$1) for the value in $B$3.

Match function finds this value at position 3. So, the formula, MATCH($B$3,Sheet1!$A$1:$E$1,0), will return value 3.

So, our new formula is: =INDEX(Sheet1!$A2:$E2, 3)

The interpretation of this part is very simple. Think $A$2:$E$2 as an array and its 3rd value is 75 (as there is no column to do the intersection) as you’re seeing in the image below.

Getting data from another sheet using HLOOKUP Function

Let’s see how we can solve this problem using HLOOKUP excel function. At first, take a look at the following image to find how the HLOOKUP function works in Excel.

To use this method, we will need a helping column that you’re seeing in the following image.

I have used this formula in cell D4 and then dragged down this formula in the cells below:

=HLOOKUP(<span style="color: #0000ff;">$B$3</span>,Sheet1!$A$1:$E$11,<span style="color: #ff0000;">C4</span>+1,0)

If you understand the HLOOKUP function appropriately, then this formula is easy to understand. For your easy understanding, I have added a glimpse of the ‘Sheet1’ worksheet (bottom right corner) in the same worksheet where I am applying the formula.

Download Working File


This is a short article but there are so many things to learn from this article.

So, this is the way I can get data from another sheet based on cell value using MS Excel. Do you know any other technique? Let me know in the comment box. I am eager to learn a new technique on this topic.

Further Readings



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:

  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:



  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.

Leave a reply