Hlookup question (date sensitive)

**Stubrok** · 07-30-2009, 01:52 PM

Afternoon,

Enclosed is a spreadsheet with a formula I’m looking for a little help with. This is my second question since joining this site and my first took about one hour to answer (thanks again by the way!). So instead of stubbornly wasting hour after hour trying to figure this one out on my own I figured I would just ask the question.

The formula is in “M7” in the “need range” tab:

=HLOOKUP(SUM($D$6+$L$5),$C$6:$K$21,2,FALSE)

I’m running across 2 issues.

1. Rather than the above formula looking at the date in “D6” then add the order lead time (5 in this case), can excel find today’s date first, then add 5 days?
2. The “Row_Index_Number”. Instead of manually entering the row number myself is it possible to return the row number that corresponds to the item numbers in column “C”?

I hope this makes sense. Maybe I’m not using the ideal formula for what I’m trying to do? Thanks in advance for any feedback, it's much appreciated

**daddylonglegs** · 07-30-2009, 02:03 PM

To revise the formula like you say you could perhaps use this version in M7 copied down

=HLOOKUP(TODAY()+$L$5,$C$6:$K$21,ROWS(M$7:M7)+1,FALSE)

although this would do the same

=LOOKUP(TODAY()+L$5,D$6:K$6,D7:K7)