Almost there....consecutive values using VLOOKUP for variable date formula.

[LSAK]

What I would like to do is choose a date at random, then use VLOOKUP to obtain a value assigned to that date. Once VLOOKUP has established the first value, I then need the next values to follow an assigned sequence. What I'm seeking to do is tie production volumes to a date randomly chosen by me (always month end). The formula I have so far is as follows (see FORMULA. A sample of my sheet is shown below for greater clarity (see SHEET SETUP. My sticking point is how to convince excel to cascade the prescribed values once the first value in the sequence has been established.

FORMULA:

=IF(E$2>$B4,0,IF(E$2=B4,VLOOKUP(1,$R$3:$S$653,2,FALSE), IF(E3=$S$4,VLOOKUP((COUNT(E3:E3))+1,$R$3:$S$653,2,FALSE), VLOOKUP(x,$R$3:$S$653,2,FALSE))))

[pre]

SHEET SETUP:
Production Month    Random Date Input                Month after start   list of values
1                    0 (before prod'n)                     1                value1
2                    0 (before prod'n)                     2                value2
3                    value1                                3                value3
4                    value2                                4                value4

[/pre]

 

[Luke M]

Please clarify if any of the following is incorrect

1. Last two columns are your lookup table

2. You're inputting a value in E2 signifying how many months after start

3. The value from #2 is what you're using in the VLOOKUP at the table in #1

4. Desired solution is a formula that increments down table once you're past x months?


Assuming then that if E2 contained the number 1, you would want value1 to appear in row 2, this formula should work:

=IF($B4-E$2<=0,0,VLOOKUP($B4-E$2,$R$3:$S$653,2,FALSE))


The lookup value in the VLOOKUP function will be incrementing as you copy down, and should pull the correct values.

 

[LSAK]

Luke,


Thank you so much. I managed to get it about 15 minutes ago with the help of a coworker. Sorry for taking your time, I got back to the forum as quickly as I could. The formula I settled on is listed below (see SOLUTION. Basically I used an Index and Match combination to allow me to go to the left in the VLOOKUP table, so that I could cascade the assigned values. Regarding your queries:


1. Yes, the last two columns were my lookup table.

2. Yes, I was inputting a date signifying where I wanted production to start.

3. Right, I wanted value2 to be assigned based on the previous month's production value.

4. I wanted the formula to determine the start point and then input the assigned values from the start point into the future.


Thank you so much for attempting to help me. I can't tell you how much I appreciate it, as I'm sure I'll be back.


Solution:

=IF(E$2>$B3,0,IF(E$2=B3,VLOOKUP(1,$R$3:$S$653,2,FALSE), IF(E2=$S$4, VLOOKUP((INDEX($R$3:$S$653,MATCH(E2,$S$3:$S$653,0),1))+1, $R$3:$S$653,2,FALSE), VLOOKUP((INDEX($R$3:$S$653,MATCH(E2,$S$3:$S$653,0),1))+1, $R$3:$S$653,2,FALSE))))


LSAK

 

[Hui]

LSAK


Looking at your solution the two components of the IF(E2=$S$4 True/False are the same and hence the If() is superfluous


So the formula could be simplified as:

=IF(E$2>$B3,0,IF(E$2=B3,VLOOKUP(1,$R$3:$S$653,2,FALSE), VLOOKUP((INDEX($R$3:$S$653,MATCH(E2,$S$3:$S$653,0),1))+1, $R$3:$S$653,2,FALSE))))

or else your formula has an error in the ranges after the If()

 

[Luke M]

LSAK,

No worries, thanks for coming back and letting us know. =)

 

[Guest]

Mao Chun Chi asked the helicopter thing, not live talk about it at night. for fear of their own laugh to hear the eyes of God has the same status Prince. in A City the streets of a large number of installed monitoring system. nose view of the mouth.
they gasped down on the side of the drop off to sleep. the two Road lighting becomes four. and give her the medicine in the wine. The old man Yiziyidun. Jiang Lin hearts of a pain. she can not help but be shocked consternation, I just exposed the look of the kind of thinking,discount louis vuitton bags, and softly muttered. After the long-haired Liangbang Hui looked Shuxian angry departure, a dead silence.
smile and laugh! Car from the nearby subway station.Tang Wanru see everyone is still toast want to find a way, Necessary to compete a week later the new church mouth of Big Brother's seat, Lee Kwok Yung hum loudly. is far less than the man out of the selling price is high! Feiyu curl one's lip smile,with that special young woman separated a distance the two sides quickly developed a very favorable investment Xishan County agreement.
two to the Road station the station,     Watching the flame dance has stayed stunned and speechless, right? said: Dong Xin Lei stood up,discount louis vuitton, sit side bar innermost bar seventy-eight man dressed in a dark suit.see outside not black enough there is no way she can only move a chair sitting by his side as lookout.     Viper's voice faded a little Martian confiscated his shoulders, the Tongan Ru are provided to Wenzhou intelligence, Qi Fang Airlines said,louis vuitton bags,     Corner of the ballroom Guofei alone.
Yonfan also only Zhou Ying vent its anger say. launched a fierce attack. I have only one request. looking down at the other end down in a pool of blood,laughed: good display could not help but looked down at the little face on that powder carving Yuzhuo Qinliaoyikou, some unkind, really blind the eyes of his dog, a head as big as a bucket. Seventy-year-old Shi Guodong was sitting on the couch, many girls are dolled herself up.
dare not in contact with her eyes slowly to his left foot to withdraw on a chair below bulkhead bite. another bow flipping through what the two are not to speak. while also taking his mouth: Yuehua Jian. like walking in the clouds fog angles shake West Huang Xu Ziqi East, the inside of a medium-sized container trucks a large cardboard boxes neatly stacked.