Getting Column Number by Searching for Text in a Worksheet - Excel 2003

[JoshExcel]

I appreciate you taking the time to look at my situation. Any help would be appreciated.

I have tried researching this issue and am coming up short.

I am trying use a formula to get the column number based on searching for text contained in a cell on a worksheet. I don't need the headings, just the column number of the cell in which the text resides. Ex: If I want to find "Setting" in the worksheet and it is in cell F32, I should get back 6 as my value. I am then going to use this number in an Index formula for the column_num portion.

Thank you,
Josh

 

[Andrew Poulsom]

Welcome to MrExcel.

Example:

=MAX(INDEX((A1:H40="Setting")*COLUMN(A1:H1),))

 

[JoshExcel]

Andrew:

That works great with my 2007. I will let you know how it goes in the morning with 2003.

Thank you,
Josh

 

[JoshExcel]

This formula is working in a new worksheet, but won't work for an existing worksheet, in Excel 2003. What would be causing this?

Thank you,
Josh

 

[JoshExcel]

It's my machine. 03 doesn't like the formula with the existing worksheet as it was created in a newer platform of Excel. Somewhere in the conversion process something is broke and causes the formula to break. If someone has any insight on this, I would appreciate it.

Thank you,
Josh

 

[Andrew Poulsom]

In what way doesn't the formula work in Excel 2003?

 

[JoshExcel]

it simply returns #N/A as the result. so confused as to why it is happening.

 

[Jonmo1]

Well there is nothing in the formula that is dependant on the version of XL

Are there formulas in the range? And do any of those formulas return the #N/A error?
That will propegate into the result of Andrew's formula.