Just saw Iain's reply which is nice and compact. But just incase anyone else wants to use autofilter through VBA, or you want to eliminate the problem with having more data below your list in column A, you could use this in a module:
Sub use_autofilter()
' Specify the day to filter and specify the range of your data
DayToGet = ActiveSheet.Range("D1").Value
FinalRow = Range("A15000").End(xlUp).Row
ActiveSheet.Range("A1:A" & FinalRow).Select
' Turn on AutoFilter, if it is not on
If ActiveSheet.AutoFilterMode = False Then Selection.AutoFilter
' Filter the data to just this day of the week
Selection.AutoFilter Field:=1, Criteria1:=DayToGet
' Select visible cell and copy to Cell B1
DaySelection = Range("A15000").End(xlUp)
ActiveSheet.Range("B1").Value = DaySelection
' Turn off the Autofilter and go back to cell A1
Selection.AutoFilter
ActiveSheet.Range("A1").Select
End Sub
THis assumes the information you want to lookup, you type into cell D1. The output is then sent to cell B1. You could run this from a button next to D1 maybe?
Nibbles