Vlookup? Referencing cells?

[Guest]

Collumn A has a selection of dates
i.e
1/3/2005
2/3/2005
7/3/2005
9/3/2005

Note: They are not consecutive (otherwise this would be too easy!)

On a sheet 2, I want to be able to type a date in a cell. Then have a
function by which all the cells beneath are populated by the corresponding
dates on the first sheet.

In other words excel needs to lookup a date in a specified collumn with the
result then coming from the row beneath where the original number is.

I hope this makes sense! Please help!

 

[Guest]

.. In other words excel needs to lookup a date in a specified col with the

result then coming from the row beneath where the original number is.

Not very sure how best to interp the above line,
but anyway, here's a play to try ..

Assume source dates are in Sheet1, in A2 down

In Sheet2, the date will be input in A1

Put in A2:
=IF(ROW(A1)>COUNT(B:B),"",INDEX(Sheet1!A:A,MATCH(SMALL(B:B,ROW(A1)),B:B,0)))

Format A2 as date

Put in B2:
=IF(OR(Sheet1!A2="",$A$1=""),"",IF(Sheet1!A2>=$A$1,ROW(),""))

(Leave B1 empty)

Select A2:B2, fill down to cover the max expected extent of data in Sheet1's
col A. The required results* will be returned in A2 down, all neatly bunched
at the top.
*dates that are more than or equal to the date input in A1

If however, what you're after are dates corresponding (ie equal to) to the
date input in A1, then just use instead in B2, and copy down:
=IF(OR(Sheet1!A2="",$A$1=""),"",IF(Sheet1!A2=$A$1,ROW(),""))
(no change to formulas in col A)

---

 

[Guest]

Line:

*dates that are more than or equal to the date input in A1

perhaps reads better as:

*dates that are later than or equal to the date input in A1

---

 

[Guest]

Sooo helpful! Thank You!

Line:

perhaps reads better as:

---

 

[Guest]

Welcome, Karen !