SOLVED: Multiple opts. How returning a text value in a cell if date falls between 2 dates

Hello everyone,
I have a cell which should return a text value (from column C in a sheet1) if in another cell a date falls between 2 dates (which ranges are in columns A and B in sheet1).
I included an example for easy explanation!

Thanks for your help!!!

R

Hi Rober,

try this one:

Please Login or Register  to view this content.

Please regard that the macro will fail if it finds any "non-dates" in column A, B or G. If your table contains some "header" you might want to run it only from table 2 onwards by changing
For rNo = 1 To LastRow to For rNo = 2 To LastRow

Regards
Theo

How do you say that column A is later than column B?
Format is
DD MONTH YY

20-may-10 is earlier that 19-may-11
and so on!
10=2010
11=2011
12=2012

Column A and B are ranges of dates!

So if G2 is a date I input, the result in H2 should correspond to the column C of the ranges of dates it falls in.

To Everyone: Can I do it with a formula instead a Macro?

Thanks
R

Originally Posted by npamcpp

Hi,

your question is not clear. The sample data is not clear. The dates in column A are later than the dates in column B. The date in column G is way outside of the date range, so it does not meet the criteria.

Please post a sample file with data that represents what you want to achieve.

Question: If there is more than one row that meets the criteria, which cell should be returned?

Please upload a file that shows a better data set.

Hello Rober,

Try this with CTRL+SHIFT+ENTER, rather than just ENTER.

Please Login or Register  to view this content.

I can't get this to work! But it sounds promising!
R

Originally Posted by Haseeb A

Hello Rober,

Try this with CTRL+SHIFT+ENTER, rather than just ENTER.

Please Login or Register  to view this content.

It is an Array formula, so must hit CTRL+SHIFT+ENTER, rather than just ENTER. If you done successfully, you can see formula surrounded by {}

See the attached. Formula in G4

Assuming that there are no gaps between the date ranges (as per example) try

=IF(OR(G2<A1,G2>B3),"no match",LOOKUP(G2,A1:A3,C1:C3))

That will do the same thing as my LOOKUP suggestion, i.e. this formula will give the same results

=LOOKUP(G2,B1:B3,A1:A3)

....but my previous suggestion also caters for dates that might be before the earliest date range or after the latest date range