# Return value if in date range

1. ## Return value if in date range

Newbie here, all all.

I have a list of dates in a column i.e 03/06/2014 (A2)

I then have a table which has a start date in the first column Z1, an end date in the second column Z2 and a year in the third column Z3.

I want to look at the date in A2 and see where this falls within the 2 dates within Z1 and Z2. If it does return the value in Z3 into A3

Note: that there are 5 date ranges and 5 years that they can fall against  Register To Reply

2. ## Re: Return value if in date range

Z1 is not a column it is a cell.
Z2 and Z3 are not columns, they are cells.
Z is a column
A is a column as well.

You need to define your problem in more detail, maybe post a sample file or create a mock up.  Register To Reply

3. ## Re: Return value if in date range

Sorry I didn't explain that at all well. Would have helped if id have been looking at my spreadsheet at the time.

I wish to see if the date in A2, falls between the dates in table Z and AA, if so to return the value in AB into B2.

So any date that falls between 30/09/13 and 31/10/14 would equal 2013, any date that falls between 30/09/14 and 31/10/15 equals 2014 etc

Yeardate.jpg  Register To Reply

4. ## Re: Return value if in date range

Can you post a SMALL sample file and show us what result you expect?

A SMALL file will have about 20 rows worth of data.  Register To Reply

5. ## Re: Return value if in date range

Hope this makes sense

Yeardate2.jpg

If the date falls between the date range in the table, it should return the year value from AB into B  Register To Reply

6. ## Re: Return value if in date range

Assuming the date ranges cover a full year as your example suggests then
in B2
=VLOOKUP(Z2,A2:B20,2,1)
and copy down the column  Register To Reply

7. ## Re: Return value if in date range

Im getting an #N/A error. Im not sure that looks right to me.

Just to be clear the dates in column B would usually be blank, I just populated these as an example. I want the formula to look at the date in A, work out if it falls between the date range of Z to AA and then provide the corresponding year from AB into B.  Register To Reply

8. ## Re: Return value if in date range

Try this in cell AB2:

=IFERROR(LOOKUP(2,1/(\$A\$2:\$A\$1000>=Z2)/(\$A\$2:\$A\$1000<=AA2),(\$B\$2:\$B\$1000)),"")

Copy down.  Register To Reply

9. ## Re: Return value if in date range

In B2

=IFERROR(VLOOKUP(\$A2,\$Z\$2:\$AB\$5,3,1),"")  Register To Reply

10. ## Re: Return value if in date range

Hi Phuocam, Im getting a 0 value  Register To Reply

11. ## Re: Return value if in date range Originally Posted by JohnTopley In B2

=IFERROR(VLOOKUP(\$A2,\$Z\$2:\$AB\$5,3,1),"")

You my friend are a star!! Thank you   Register To Reply

12. ## Re: Return value if in date range Originally Posted by Special-K Assuming the date ranges cover a full year as your example suggests then
in B2
=VLOOKUP(Z2,A2:B20,2,1)
and copy down the column
This should have been

in B2
=VLOOKUP(A2,Z\$2:AB\$5,3,1)  Register To Reply

13. ## Re: Return value if in date range

Thank you for the feedback.  Register To Reply