# VLOOKUP to Return Value if date falls between two dates

1. ## VLOOKUP to Return Value if date falls between two dates

Good Morning group,

I feel like I must be missing something obvious here but I seem to be overlooking it. I thought this would be pretty simple. I've tried to find a solution on the web and in this forum with no luck.

I have a column (F) in a Table that holds a date in each cell (all formatted as "short date").

I have a column (I) in that same Table that I would like to show in which quarter that date falls.

I have the following reference Table (named "Dates") on a separate sheet that looks like the attached jpg.

I tried the following formula that I thought would work, but it keeps returning #N/A: =VLOOKUP(F2,Dates,3,FALSE)

I don't know what I'm missing.

So if anyone can either show me what I'm missing here or give me an alternate solution, it would be great!

2. ## Re: VLOOKUP to Return Value if date falls between two dates

See if this does what you want:

=VLOOKUP(F2,Dates,3)

3. ## Re: VLOOKUP to Return Value if date falls between two dates

Hi,

Are you able attach a copy of your file?

peterrc

4. ## Re: VLOOKUP to Return Value if date falls between two dates

Tony,

That returns #N/A as well.

Petrrc,

I've attached a cleaned up version (I had to remove several sheets for proprietary reasons)

I have a feeling that it's something really simple that I happen to be overlooking.

5. ## Re: VLOOKUP to Return Value if date falls between two dates

Your dates in column F are not true Excel dates, they're TEXT strings that look like dates. The LEFT function returns TEXT values.

To convert them into true Excel dates change the formula in F2 to:

=--LEFT(D2,10)

And change the formula in I2 to:

=VLOOKUP(F2,Dates,3)

6. ## Re: VLOOKUP to Return Value if date falls between two dates

Tony,

Very nice! That works great.

May I ask what the -- does in front of the LEFT function? I've never seen that before.

7. ## Re: VLOOKUP to Return Value if date falls between two dates

Tony,

Very nice! That works great.

May I ask what the -- does in front of the LEFT function? I've never seen that before.

8. ## Re: VLOOKUP to Return Value if date falls between two dates

The -- is known as double unary minus.

It will convert text numbers and Boolean values into numeric values.

--"5" = 5
--TRUE = 1
--FALSE = 0

In your application the LEFT function returned a TEXT string that just happened to look like a date.

The double unary minus can convert TEXT dates into true numeric dates.

--"6/23/2016" = 6/23/2016 (formatted as Date)

9. ## Re: VLOOKUP to Return Value if date falls between two dates

Tony,

Very nice! That works great.

May I ask what the -- does in front of the LEFT function? I've never seen that before.

##### Users Browsing this Thread

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