table lookup

[JeffK]

I have a 3 Column Table: Begin Date, End Date, Interest. On a separate
worksheet, if a user enters the a specified Begin and End dates, below the
entry,I want to list that portion of the table that falls between the dates.

(assume the table is A2-C8)
Begin Date End Date Interest
1-Jan 5-Feb 2.00%
6-Feb 15-Mar 2.25%
16-Mar 4-Apr 2.50%*
5-Apr 21-Apr 2.25%
22-Apr 1-Jun 2.50%
2-Jun 7-Jul 2.75%*
8-Jul 15-Aug 3.00%

Input page

Begin Date = Mar 19
End = June 6

16-Mar 4-Apr 2.50%
5-Apr 21-Apr 2.25%
22-Apr 1-Jun 2.50%
2-Jun 7-Jul 2.75%


I appreciate your help on this one

 

[T. Valko]

Can the input begin date be earlier than the earliest begin date in your
table?

For example, the earliest begin date in your table is 1-Jan (I assume that's
1/1/2009). Can your input begin date be earlier than 1/1/2009?

 

[B. R.Ramachandran]

Hi Jeff,

There may be a more elegant approach, but the following seems to work.

In the Table page, create a helper column D that displays the row numbers
(So, D2=ROW(D2), and fill down the column).
In the Input page (I assume that the Begin and End dates are in B1 and B2
respectively). Enter the following formulas in A4, B4, and C4 respectively
(The formulas assume that the Table page's name is Sheet1. If not, enter the
sheet's actual name in place of "Sheet1" in every occurrence in the formulas.

In A4
=IF(VLOOKUP($B$2,Sheet1!$A$2:$D$8,4)>=VLOOKUP($B$1,Sheet1!$A$2:$D$8,4)+ROW(A1)-1,INDIRECT("Sheet1!$A"&VLOOKUP($B$1,Sheet1!$A$2:$D$8,4)+ROW(A1)-1),"")

In B4,
=IF(A4="","",VLOOKUP($A4,Sheet1!$A$2:$C$8,2,0))

In C4,
=IF(A4="","",VLOOKUP($A4,Sheet1!$A$2:$C$8,3,0))

Format Columns A and B for date, and Column C for percentage.

Drag the formulas down as far as needed.

Best regards,
B. R. Ramachandran

 

[JeffK]

I input the formula and adjusted the table/sheets to mirror your formula (to
get it working first and make adjustments later)

Only the first 4 rows are copied then nothing. I'm guessing by looking at
the first formula, the portion that read " +ROW(A1) " doesn't seem to
reference to sheet1 and I think that's the reason I get only 4 rows of date
(formula entered in A4 on the input sheet)

Not sure how to fix. Tried but I get errors.

 

[B. R.Ramachandran]

Hi Jeff,

I tested the formulas again, and they are working fine for me (I am using
Excel 2007, but I think that it shouldn't matter).
I even extended to Table to contain 10 more rows of Begin and End dates and
percentages, and also varied the Begin and End dates in the Input page, to
make sure that the formulas work; they do work fine.

Also, the "ROW(A1)" parts in the formula don't have to refer to Sheet1
(ROW(A1) just generates the number 1, and is not sheet-specific).

Please try again. I am cutting/pasting the formulas again from my Excel
trial (This time the formulas contain the range A218 instead of A28
posted earlier to accommodate an extended Table. Change the range
aqppropriately to suit to your worksheet.

=IF(VLOOKUP($B$2,Sheet1!$A$2:$D$18,4)>=VLOOKUP($B$1,Sheet1!$A$2:$D$18,4)+ROW(A1)-1,INDIRECT("Sheet1!$A"&VLOOKUP($B$1,Sheet1!$A$2:$D$18,4)+ROW(A1)-1),"")

=IF(A4="","",VLOOKUP($A4,Sheet1!$A$2:$C$18,2,0))

=IF(A4="","",VLOOKUP($A4,Sheet1!$A$2:$C$18,3,0))

Hope it works this time!

Thanks,
B. R. Ramachandran

 

[JeffK]

That's it! Thanks a bunch.

It was the range for the table that caused the problems.

Awesome