Do you know any other technique Let me know in the comment box.In this short article, I will show using Excel how you can get data from another worksheet based on a cell value.
![]() ![]() This is the formula that I have used in the cell D4 and then dragged down the formula for 10 cells. Youre also seeing the formulas of cells D5 and D6 cells on the right side of the cells. INDEX(Sheet1A2:E2,MATCH ( B3,Sheet1A1:E1,0 ) ) MATCH Function overview To understand this formula, you have to understand the MATCH and INDEX functions at first. ![]() Syntax of Match function: MATCH (lookupvalue, lookuparray, matchtype) Check out the following worksheet. 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, rownum, columnnum) Reference Form: INDEX (reference, rownum, columnnum, areanum) Check out the following image. Breakdown of the formula Lets now explain how this formula is working. INDEX(Sheet1A2:E2,MATCH ( B3, Sheet1A1:E1,0 ) ) This part of this formula, MATCH ( B3, Sheet1A1:E1,0 ), will find an exact match in this array (A1:E1) for the value in B3. So, the formula, MATCH(B3,Sheet1A1:E1,0), will return value 3. So, our new formula is: INDEX(Sheet1A2:E2, 3) The interpretation of this part is very simple. Think A2:E2 as an array and its 3 rd value is 75 (as there is no column to do the intersection) as youre seeing in the image below. Getting data from another sheet using HLOOKUP Function Lets 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 youre seeing in the following image. I have used this formula in cell D4 and then dragged down this formula in the cells below: HLOOKUP( B3,Sheet1A1:E11, C4 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. How Do I Link Data Between Worksheets In Excel Download Working FileDownload Working File Get-data-from-another-sheet Conclusion 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
December 2020
Categories |