VLOOKUP with IF statement
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... |
Kay
Try =IF('CH Milestones'!GM5Sheet1!A$2,"",VLOOKUP('CH Milestones'!GM5,'CH Milestones'!GL$5:GM$87,2,FALSE)) John "Kay" wrote: 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... |
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 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Kay" wrote in message ... 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... |
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 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
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 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
All times are GMT +1. The time now is 03:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com