vlookup formula for past dates

[exceluser9]

Hi There

I have a data like this in 2 sheets

	SHEET 1					SHEET 2
COLUMN C	COLUMN D	COLUMN E			COLUMN C	COLUMN F	COLUMN I
YEAR	AMOUNT	DATE			YEAR	AMOUNT	DATE
2005					2005	500
					2005	600	10/07/2015
					2005	700	21/09/2015

<tbody>
</tbody><colgroup><col><col><col><col span="2"><col><col><col></colgroup>

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

COLUMN C	COLUMN D	COLUMN E
YEAR	AMOUNT	DATE
2005	500	06/07/2015

<tbody>
</tbody><colgroup><col><col><col></colgroup>

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

	sheet 1					sheet 2
column c	column d	column e		column c			column f			column i
YEAR	AMOUNT	DATE		YEAR			AMOUNT			DATE
2005	0	09/07/2015		2005			500
				2005			600			10/07/2015
				2005			700			21/09/2015

<tbody>
</tbody><colgroup><col><col><col><col span="7"><col></colgroup>

 

[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

	sheet 1					sheet 2
column c	column d	column e		column c			column f			column i
YEAR	AMOUNT	DATE		YEAR			AMOUNT			DATE
2005	0	09/07/2015		2005			500
				2005			600			10/07/2015
				2005			700			21/09/2015

<tbody>
</tbody>

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