TODAY, VLOOKUP and INDIRECT

[mightyeskimo]

Hello All! Hope someone can help me with this. I'm trying to write a formula that uses todays date to determine a column reference and a VLOOKUP to determine a row reference (both in the same sheet) to have a certain cell displayed in another sheet.

I'll try to talk it through.

If todays date falls into the range of 'Schedule'!V5:W5 (column v is a start date and column w is an end date) then make the column reference 'Schedule'!B. If todays date falls between 'Schedule'!V6:W6 then make the column reference 'Schedule'!C. There are 17 date ranges so the possible column references are B through R.

I believe I have the VLOOKUP portion of the formula correct. This portion will return the row reference for the desired cell in 'Schedule'!. This part of the formula looks at a cell in column 'Data'!S and, based upon the text that is in that cell, returns the appropriate row reference.

For example, if todays date falls into the date range 'Schedule'!V7:W7 and the contents of cell 'Data'!$S8 is "CCC" then the cell the formula resides in will reference 'Schedule'!$D$7.

=INDIRECT("'Schedule'!TODAY(>={$V$5:$W$5,B;$V$6:$W$6,C;$V$7:$W$7,D;$V$8:$W$8,E;$V$9:$W$9,F;$V$10:$W$10,G;$V$11:$W$11,H;$V$12:$W$12,I;$V$13:$W$13,J;$V$14:$W$14,K;$V$15:$W$15,L;$V$16:$W$16,L;$V$17:$W$17,M;$V$18:$W$18,N;$V$19:$W$19,O;$V$20:$W$20,P;$V$21:$W$21,Q;$V$22:$W$22,R},2,FALSE))"&VLOOKUP($S8,{"AAA",5;"BBB",6;"CCC",7;"DDD",8;"EEE",9;"FFF",10;"GGG",11;"HHH",12;"III",13;"JJJ",14;"KKK",15;"LLL",16;"MMM",17;"NNN",18;"OOO",19;"PPP",20;"QQQ",21;"RRR",22;"SSS",23;"TTT",24;"UUU",25;"VVV",26;"WWW",27;"XXX",28;"YYY",29;"ZZZ",30;"111",31;"222",32;"333",33;"444",34;"555",35;"666",36},2,FALSE))

I tried to enter this but the computer said there were too many characters and suggested I use CONCATENATE. What am I missing? Is there a simpler way to to this?

[teylyn]

That formula makes no sense at all. Today() does not take arguments. INDIRECT() requires a string that resolves to a range. This one does not. Why hard-code the lookup table values instead of referencing a two-column table in the sheet?

Post a workbook with sample data and mock up the expected results. Explain the rules that lead to the results.

[JBeaucaire]

Create a sheet called Lists (you can hide this later, it's a resource sheet).

In columns A & B create lookup table of your date ranges. You don't need the end dates, just the beginning dates, because I presume the ranges are all-inclusive, meaning when range 1 ends range 2 begins, yes? In column A list starting dates starting with the earliest date and going more recent as you go down column (sorted ascending), in column B list the matching column letters (B,C,D, etc).

Then in column D list all your text codes (AAA,BBB,CCC,DDD,EEE,etc) starting in D5 and going downward.

Now, this version of your formula may do what you need:

=INDIRECT("Schedule!'" & VLOOKUP(TODAY(), Lists!$A$2:$B$19, 2, 1) & MATCH($S$8, Lists!$D:$D, 0))

[mightyeskimo]

I've attached a sample workbook to illustrate what I'm trying to do. Please disregard the formula I attempted to write above. I just realized I was also trying to combine another action into that formula that was unneccesary.

Thanks very much for your help!

[JBeaucaire]

This is a standard INDEX/MATCH/MATCH problem and solution. In AI5, then copy down:

=INDEX(Schedule!$B$5:$R$36, MATCH($S5, Schedule!$A$5:$A$36, 0), MATCH(TODAY(), Schedule!$V$5:$V$21, 1))

[mightyeskimo]

Thanks Jerry & teylyn! I was really over complicating things! How do I mark this as solved?

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

[mightyeskimo]

Got it! Thanks again!