vlookup formula for past dates

[exceluser9]

Hi There

I have a data like this in 2 sheets

[TABLE="width: 568"]
<tbody>[TR]
[TD][/TD]
[TD]SHEET 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SHEET 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]COLUMN C[/TD]
[TD]COLUMN D[/TD]
[TD]COLUMN E[/TD]
[TD][/TD]
[TD][/TD]
[TD]COLUMN C[/TD]
[TD]COLUMN F[/TD]
[TD]COLUMN I[/TD]
[/TR]
[TR]
[TD]YEAR[/TD]
[TD]AMOUNT[/TD]
[TD]DATE[/TD]
[TD][/TD]
[TD][/TD]
[TD]YEAR[/TD]
[TD]AMOUNT[/TD]
[TD]DATE[/TD]
[/TR]
[TR]
[TD]2005[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2005[/TD]
[TD]500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2005[/TD]
[TD]600[/TD]
[TD="align: right"]10/07/2015[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2005[/TD]
[TD]700[/TD]
[TD="align: right"]21/09/2015[/TD]
[/TR]
</tbody><colgroup><col><col><col><col span="2"><col><col><col></colgroup>[/TABLE]

In sheet 1 if i give any date before 10/7/2015 i should get the amount 500 is it possible?

For example like this

[TABLE="width: 220"]
<tbody>[TR]
[TD]COLUMN C[/TD]
[TD]COLUMN D[/TD]
[TD]COLUMN E[/TD]
[/TR]
[TR]
[TD]YEAR[/TD]
[TD]AMOUNT[/TD]
[TD]DATE[/TD]
[/TR]
[TR]
[TD]2005[/TD]
[TD]500[/TD]
[TD="align: right"]06/07/2015[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]


Thank you in Advance

 

[gaz_chops]

Try

=SUMIF(Sheet2!$I$2:$I$100,"<="&E2,Sheet2!$C$2:$C$100)

 

[exceluser9]

Try

=SUMIF(Sheet2!$I$2:$I$100,"<="&E2,Sheet2!$C$2:$C$100)

Hi Sir,

If i give the date 09/07/2015 it says 0. Any suggestions


Below is how my data looks, if i input year as 2005 and date as 09/07/2015 amount should display as 500, i.e my date row for 2005 and 500 is blank in sheet 2. if i give any date before 10/7/2015 it should result 500
[TABLE="width: 736"]
<tbody>[TR]
[TD][/TD]
[TD]sheet 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]sheet 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]column c[/TD]
[TD]column d[/TD]
[TD]column e[/TD]
[TD][/TD]
[TD]column c[/TD]
[TD][/TD]
[TD][/TD]
[TD]column f[/TD]
[TD][/TD]
[TD][/TD]
[TD]column i[/TD]
[/TR]
[TR]
[TD]YEAR[/TD]
[TD]AMOUNT[/TD]
[TD]DATE[/TD]
[TD][/TD]
[TD]YEAR[/TD]
[TD][/TD]
[TD][/TD]
[TD]AMOUNT[/TD]
[TD][/TD]
[TD][/TD]
[TD]DATE[/TD]
[/TR]
[TR]
[TD]2005[/TD]
[TD]0[/TD]
[TD="align: right"]09/07/2015[/TD]
[TD][/TD]
[TD]2005[/TD]
[TD][/TD]
[TD][/TD]
[TD]500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2005[/TD]
[TD][/TD]
[TD][/TD]
[TD]600[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10/07/2015[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2005[/TD]
[TD][/TD]
[TD][/TD]
[TD]700[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]21/09/2015[/TD]
[/TR]
</tbody><colgroup><col><col><col><col span="7"><col></colgroup>[/TABLE]

 

[gaz_chops]

Are the dates sorted ascending with blanks in between certain dates? Can you post a slightly larger sample of Sheet2.

 

[exceluser9]

No sates are not sorted ascending, its random, there can be future dates as well

 

[gaz_chops]

No sates are not sorted ascending, its random, there can be future dates as well

You need to explain a bit more.
Do you want to sum all the amounts where the dates are less than E2 (9/7/15)?
Do you want to just sum the blanks?
Can you post a slightly larger sample of Sheet2?

 

[exceluser9]

You need to explain a bit more.
Do you want to sum all the amounts where the dates are less than E2 (9/7/15)?
Do you want to just sum the blanks?
Can you post a slightly larger sample of Sheet2?

Hi Sir, Below is how my data looks,

in sheet 1 if I put year and past date, it should give me the data of the cell where in the date is blank. For example if I put 2009 and give date 03-06-2015 I should get amount 900


SHEET 1 SHEET 2
COLUMN C COLUMN D COLUMN E COLUMN C COLUMN F COLUMN I
YEAR AMOUNT DATE YEAR AMOUNT DATE
2005 600 06-07-2015 2005 500
2006 800 16-07-2015 2005 600 10-07-2015
2007 100 09-07-2015 2005 700 15-07-2015
2009 900 03-06-2015 2006 800
2006 900 23-08-2015
2007 100
2009 900
2009 200 01-08-2015
2009 300 05-08-2015
2009 400 10-09-2015

 

[admiral100]

Hi Sir,

If i give the date 09/07/2015 it says 0. Any suggestions


Below is how my data looks, if i input year as 2005 and date as 09/07/2015 amount should display as 500, i.e my date row for 2005 and 500 is blank in sheet 2. if i give any date before 10/7/2015 it should result 500
[TABLE="width: 736"]
<tbody>[TR]
[TD][/TD]
[TD]sheet 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]sheet 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]column c[/TD]
[TD]column d[/TD]
[TD]column e[/TD]
[TD][/TD]
[TD]column c[/TD]
[TD][/TD]
[TD][/TD]
[TD]column f[/TD]
[TD][/TD]
[TD][/TD]
[TD]column i[/TD]
[/TR]
[TR]
[TD]YEAR[/TD]
[TD]AMOUNT[/TD]
[TD]DATE[/TD]
[TD][/TD]
[TD]YEAR[/TD]
[TD][/TD]
[TD][/TD]
[TD]AMOUNT[/TD]
[TD][/TD]
[TD][/TD]
[TD]DATE[/TD]
[/TR]
[TR]
[TD]2005[/TD]
[TD]0[/TD]
[TD="align: right"]09/07/2015[/TD]
[TD][/TD]
[TD]2005[/TD]
[TD][/TD]
[TD][/TD]
[TD]500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2005[/TD]
[TD][/TD]
[TD][/TD]
[TD]600[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10/07/2015[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2005[/TD]
[TD][/TD]
[TD][/TD]
[TD]700[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]21/09/2015[/TD]
[/TR]
</tbody>[/TABLE]

Hi,

try that formula on sheet1 column D :

=IF(IFERROR(OR(ISNA(INDEX(sheet2!F:F,MATCH(sheet1!E:E,sheet2!I:I,0))),INDEX(sheet2!F:F,MATCH(sheet1!E:E,sheet2!I:I,0))<10/7/2015),500),500,INDEX(sheet2!F:F,MATCH(sheet1!E:E,sheet2!I:I,0)))

 

[gaz_chops]

What if there is more than 1 blank for the year, that are less than the date?

 

[exceluser9]

It will not be there sir in my data, there will be only one blank