find a string and return column

I have a cover sheet and a data sheet. On the data sheet there is a set of dates on row 2, each column is a different date and that data collected for that date. On the cover sheet i would like to enter a date and have a formula spit out the column that the date is on in the data sheet.

Seems really simple to do, but i can't find an answer anywhere?!? I am currently using Address(Match(),Column()) functions for when i am looking through the rows to find the row that the date is on (on a different sheet formatted by rows not columns), but for this one i need to find the column that the date is in.

I know that the simple solution would be to just switch it, but unfortunately i can't =[

Any help would be appreciated! Thanks in advance!

Thanks for the answers, but unless i am doing something wrong it isn't giving me quite what i need..
Right now i am getting the text from the cell above the cell that i am finding, but what i need is the actual column (A, B, C, AA, AB, AC, etc.) that it is in. I tried using =columns(hlookup()) but it keeps on giving me a #VALUE!

Have you tried reversing what you have already found to work?

Instead of: Address(Match(),Column()) use Address(Row(),Match())

Yea i have tried switching it like you suggested and for some reason it always gives that date furthest to the right. The way the data is inserted is when you enter in today's numbers, i have a macro that will add columns to the left to add a new day pushing old days to the right. the date is in row 2. One thing that may be kind of screwy is the data that is recorded for each day is 5 columns wide so the date is a merged cell over the 5 columns. This is the codes that i have tried and the results that i get:

PickUpFindDate is the date that i am trying to find
PickUpSearchDate is the row of dates that i am searching in

=ADDRESS(ROW(PickUpSearchDate),MATCH(PickUpFindDate,PickUpSearchDate)) 'gives the last date on the row (oldest date)
i then thought to put the "COLUMN" in there
=ADDRESS(ROW(PickUpSearchDate),COLUMN(MATCH(PickUpFindDate,PickUpSearchDate))) 'just gives me an error and won't let me do this
=ADDRESS(ROW(PickUpSearchDate),COLUMNS(MATCH(PickUpFindDate,PickUpSearchDate))) 'gives me the A2 which is actually a blank cell just before the dates start

=ADDRESS(ROWS(MATCH(PickUpFindDate,PickUpSearchDate,1)),COLUMNS(MATCH(PickUpFindDate,PickUpSearchDate,1))) 'thought i would just go ahead and give this a try, but this gives A1 so it is nowhere near close to what i am trying to get...

what i find funny with the Match() function is that =MATCH(PickUpFindDate,PickUpSearchDate) gives a value of 42, and AP is the 42nd letter, so that is where the column is coming from. How does Match() get this number?

MATCH() gives the position of the found value.
What result do you get with just =ROW(PickUpSearchDate) ?
If that row is constant just substitute the row number for the named range so that you get: =ADDRESS(2,MATCH(PickUpFindDate,PickUpSearchDate))
(if it's row 2)

It will always be in row 2, but when i do the MATCH(PickUpFindDate,PickUpSearchDate)), it will give me the the number number 42, which turns out to be the last column of my data when i am trying to find today's. I even tried to just make an adjustment by throwing a "-40" at the end of it to give me the 2nd column, but when i change PickUpFindDate, it still pulls up the same number...? I created Named Ranges on the spreadsheet to see if that would help, and i have also done just direct cell references (i was just assuming that i had to of been typing something in wrong because it is always pulling up 42), and even with the named ranges, it pulls up the same number, 42...?

I tried putting a =column() in row 1 (since it is blank) just so that i have a cell that references the column number, and used HLOOKUP so that it could pull up the the data from the row above, and it is litterally pulling up the very last data entry from the row.

It almost seems like Match (or even HLOOKUP) is only looking for the string in a row and giving me back the last data entry in the row? if that even makes sense?!

Have you tried adding the 3rd element to the MATCH() function?

MATCH(PickUpFindDate,PickUpSearchDate,0)

rofl......

I thought i did... but i guess not
That works perfectly! =] If i am correct, this spreadsheet that i have been working on for 6 months (many revisions) will be finished! (until i find the next revision haha..)

Thanks a lot!

Good stuff. You're welcome. Don't forget to mark your thread as SOLVED (instructions found in rule #9 - Forum Rules button @ top of page).