# 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

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.

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

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.

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

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

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.

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.

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

In B2

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

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

Hi Phuocam, Im getting a 0 value

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

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)

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

Thank you for the feedback.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1