Find Todays Date & Offset to a Different Cell

I have a spreadsheet which I need to display a cell that changes weekly. For instance, Sheet1 "A1" needs to display a value from Sheet2 based on todays date. When it finds the date, say "A25", then it needs to offset over to the 6th column on Sheet2, say "F25", on the same row and display the value in the cell to Sheet1 "A1".

Sheet1 "A1" equels =IF(('Sheet2!A3:A458)=TODAY(),OFFSET(A3:A458,0,5))

=vlookup(today(),'Sheet2'!A:F,6,false)

It did not work as planned. It returned #NAME?. As I see it in your equation the VLOOKUP command goes to "Sheet2" and finds todays date in column A. Then it counts over 6 columns to column F and displays on sheet1 the value in the cell on sheet2.

Both sheet1 & 2 are in the same spreadsheet file.