Perform Vlookup if within defined date range

[sudeep]

Hi Experts,

I come back to you for help with another problem.

I have 2 workbooks.

Workbook 1 contains:

Date
Group

Workbook 2 contains:

From Date
To Date
Criterion


Problem:

I want a formula that would perform a vlookup on a criterion to fetch group (text) only if the date in workbook 1 is within the range of From date and to date of workbook 2.

Explanation:

Workbook 1

Date Group Section
12/1/2012 Apple a1
3/24/2011 Grapes a1
12/1/2012 Apple b4

Workbook 2

From Date To Date Section Criterion
1/1/2012 1/31/2012 a1 Requesting a formula that will perform vlookup for given criterion (a1) in this example on data where date is within the date range given (1/1/2012 to 1/31/2012 in this example).

Expected Result:

Workbook 2
Criterion Apple (1st filter is date - We get two options: record nos. 1 and 3 in workbook 1. 2nd filter is section a1, so that leaves out record no. 3 and displays Apple in the criterion column.

As usual, I will appreciate if we can do it using functions instead of VBA.

Thanks in advance.

Regards,
Sudeep

 

[nemoy]

As a quick note, vlookup always looks in a column on left and returns a value on the right, so your current layout:
Date Group Section
You can use Date to find Group or Section
You can use Group to find Section
You CANNOT use Section to find Group

This is using vlookup, you can use index and match instead.

Next question, what if more than one piece of information matches? Are you wanting all things that match or only the first match? This will drastically change your approach.

Beyond that, thought I could work this out simply, but I'm also struggling. I look forward to answers from others!

 

[niladri2005]

which date you want to consider form workbook2? I mean the first one or the last one? can you please attach a sample file..