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.If you have a date and want to convert into a day number, you can use either the Day Function or the Text Function. If you understand the HLOOKUP function appropriately, then this formula is easy to understand. I have used this formula in cell D4 and then dragged down this formula in the cells below: To use this method, we will need a helping column that you’re seeing in the following image. At first, take a look at the following image to find how the HLOOKUP function works in Excel. Let’s see how we can solve this problem using HLOOKUP excel function. Getting data from another sheet using HLOOKUP Function Think $A$2:$E$2 as an array and its 3 rd value is 75 (as there is no column to do the intersection) as you’re seeing in the image below. The interpretation of this part is very simple. So, our new formula is: =INDEX(Sheet1!$A2:$E2, 3) Match function finds this value at position 3. 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. Let’s now explain how this formula is working. The gest of the Index function is shown in this image. Reference Form: INDEX (reference, row_num,, )Ĭheck out the following image. Index function returns the intersection value of two rows and columns in an array.Īrray Form: INDEX (array, row_num, ) You will get the idea of how Match function works. Syntax of Match function: MATCH (lookup_value, lookup_array, )Ĭheck out the following worksheet. Match function returns the relative position of an item in an array that matches a specified value in a specified order. To understand this formula, you have to understand the MATCH and INDEX functions at first. You’re also seeing the formulas of cells D5 and D6 cells on the right side of the cells. This is the formula that I have used in the cell D4 and then dragged down the formula for 10 cells. All the values from the ‘Sheet1’ worksheet and under the 3/2018 column are showing under the ‘Data’ heading. In the B2 cell, you see I have input value 3/2018. In the following image, you are seeing my ‘Get’ worksheet data.

You’re seeing a worksheet ( ‘Sheet1’) below with some dummy data. Conclusion Getting Data from another worksheet (using INDEX & MATCH)

Excel extract date from field