In sheet 1, I have a list of dates in Column A in chronological order. There are values corresponding to this list in Column B. There is another list of dates in sheet 2 and I want to add values from sheet 1 to these dates.
Sheet 1.
**Column A Column B
DATE Amount**
1. 10/01/2015 25,60,000
2. 10/02/2015 26,80,000
3. 01/03/2015 21,55,000
4. 30/03/2015 24,60,500
5. 30/04/2015 28,20,000
6. 30/06/2015 19,00,000
Sheet 2.
Column A Column B
1. 21/02/2015 21,55,000
2. 15/01/2015
3. 20/05/2015
4. 25/04/2015
For example: I need to look up 21/02/2015 in sheet 1 and column A and return the value corresponding to the next available date. So for 21/02/2015 I need the value corresponding to the next date available which is 01/03/2015 and the value is 21,55,000. If its 15/01/2015 I need the value of 10/02/2015 i.e. 26,80,000
What formula could I use for this?
You could use VLOOKUP, but it has some issues. So it is better to use INDEX and MATCH combination. In your case try this
=INDEX('Sheet 1'!$B:$B,MATCH(A1,'Sheet 1'!$A:$A,-1))
Sorry, my previous answer works only for descending order. Try this instead
=INDEX('Sheet 1'!$B:$B,MATCH(TRUE,('Sheet 1'!$A:$A-A1)=MIN(IF('Sheet 1'!$A:$A-A1>=0,'Sheet 1'!$A:$A-A1)),0))
Explanation: I hope that INDEX and MATCH are well explained in Office Support.
About the condition:
('Sheet 1'!$A:$A-A1)=MIN(IF('Sheet 1'!$A:$A-A1>=0,'Sheet 1'!$A:$A-A1))
What it means?
'Sheet 1'!$A:$A-A1
results in a difference between the value in the cell A1 and the cell in A column in Sheet 1.
MIN(IF('Sheet 1'!$A:$A-A1>=0,'Sheet 1'!$A:$A-A1))
says that if the difference is non-negative ('Sheet 1'!$A:$A-A1>=0), find the minimum of such numbers (MIN function).
And if these numbers are equal (MATCH function), then pick the corresponding number in column B (INDEX('Sheet 1'!$B:$B,...)).
Apology: In my previous answers I swapped the columns of your example. I hope it is now correct.
You can use vlookup with True rather than the widely used form with False
As ExcelEfendisi said you can use vlookup with range lookup enabled. A simple way to get the value at the next date rather than the prior one would be to push all the amount values down one row, but to avoid that it might be better to repeat the index values - like this
1 10/01/15 25,60,000 1
2 10/02/15 26,80,000 2
3 01/03/15 21,55,000 3
4 30/03/15 24,60,500 4
5 30/04/15 28,20,000 5
6 30/06/15 19,00,000 6
Then you can use two vlookups, the first one to get the index of the row with the date prior to the date you are interested in and a second one to extract the balance value for the subsequent date - not very elegant but it would work
Try this formula (enter in Sheet2 cell B2 then copy till the last record)
=INDEX(Sheet1!$B:$B,1+MATCH($A2,Sheet1!$A:$A,1),1)
As data is sorted in ascending order use MATCH with match type 1 (less than) to obtain the row above the high next item, then add 1 and the result is the high next row, use this row to get the corresponding record from the column B with formula INDEX
