VLOOKUP with IF statement

[Guest]

I want to automatically produce a list of all events that will occur between
two given dates.

I am using VLOOKUP to search the column which has the date for each event. I
want to return the names of all the events that occur before the user-defined
dates.

My formula looks like this:

=IF('CH Milestones'!GM5<Sheet1!A$2,VLOOKUP('CH Milestones'!GM5,'CH
Milestones'!GL$5:GM$87,2,TRUE),"")

GM is the column with the dates.
GL is the column with the event names.
A2 has a user-defined date entered into it.

(In the above formula I was only looking for dates before A2; ultimately I
want only those between two dates.)

Many thanks...

 

[Guest]

Kay

Try

=IF('CH Milestones'!GM5>Sheet1!A$2,"",VLOOKUP('CH Milestones'!GM5,'CH

Milestones'!GL$5:GM$87,2,FALSE))

John

 

[Max]

Another option to try, using non-array formulas ...

Assume the user will enter the Start and End dates in Sheet1's A2:A3
(Start date in A2, End date in A3. If the query involves only a single
date, then the same date has to be entered in both A2 and A3)

In sheet: CH Milestones
------------------
Use an empty adjacent col, say col GN:

Put in GN5:
=IF(OR(Sheet1!$A$2="",Sheet1!$A$3="",GM5=""),"",IF(AND(GM5>=Sheet1!$A$2,GM5<
=Sheet1!$A$3),ROW(),""))

Copy GN5 down to GN87

(Ensure GN1:GN4 are left empty)

Then in a new sheet, say Sheet2
---------
we could put in say, B2:

=IF(ISERROR(SMALL('CH Milestones'!$GN$5:$GN$87,ROWS($A$1:A1))),"",INDEX('CH
Milestones'!$GL$5:$GL$87,MATCH(SMALL('CH
Milestones'!$GN$5:$GN$87,ROWS($A$1:A1)),'CH Milestones'!$GN$5:$GN$87,0)))

and copy B2 down to B84
(cover the same range size as that done in GN5:GN87 in "CH Milestones")

B2:B84 will return the desired results for the inputs in Sheet1's A2:A3, all
neatly bunched at the top

 

[Max]

Here's a link to a sample file with an implemented construct
http://www.savefile.com/files/7767107
File: VLOOKUPwithIFstatement_Kay_misc.xls

And some clarifications:

(Ensure GN1:GN4 are left empty)

Pl disregard the above refrain. It's not applicable here, sorry, since
we're not using entire col references within the SMALL(...) part in the
extract formulas.

In the sample file provided, the results of the query are now extracted
directly into Sheet1's A5:A87, just below the inputs in A2:A3, instead of
into another Sheet2 as posted earlier. The same extract formula is placed
into the starting cell A5, then copied down to A87. Makes for a better
presentation for user to see the query results ! <g>

 

[Max]

John, I'm not sure that we can use VLOOKUP here as:
a. As described in the orig. post, the lookup range (in GM5:GM87) is to the
*right* of the return range (in GL5:GL87)
b. VLOOKUP can return only the 1st match value. There is likely to be more
than 1 match involved, if I've read the post correctly