Finding vlookup with dates (Excel 2000)

Topic

New Reply

Hi, I have a list. The first column contains dates that are one week apart (e.g. 7/7, 7/14, 7/21, 7/28). The next column is the week number (eg, 1,2,3). The next column contains the data I want. A user enters a date (any date) in cell A1. I would like to look down the row of dates and use the date just before the date that is greater than the entered date. (Boy that sounded complicated!)

7/7/03 1 Cindy
7/14/03 2 Boss
7/21/03 3 Someone

If the user entered, 7/15/03, I would want to get “Boss”.

Is there a way to do this in a formula. I couldn’t seem to get vlookup to work!
–cat

Reply | Quote

Replies

If your 3 columns are in C1:E3 (change as appropriate) you could use this to get the date:

=VLOOKUP(A1,$C$1:$E$3,3)

Steve

Reply | Quote

If your 3 columns are in C1:E3 (change as appropriate) you could use this to get the date:

=VLOOKUP(A1,$C$1:$E$3,3)

Steve

Reply | Quote

If the dates are in A1:A3, the week numbers in B1:B3, and the data is in C1:C3, an the user enters the date in D1, the following formula should give you what you want:

=VLOOKUP(D1,A1:C3,3,TRUE)

Reply | Quote

If the dates are in A1:A3, the week numbers in B1:B3, and the data is in C1:C3, an the user enters the date in D1, the following formula should give you what you want:

=VLOOKUP(D1,A1:C3,3,TRUE)

Reply | Quote