find a value in a range of columns

[caldara]

help this has got me !
i have a range of data a1:z300 , each range is a column eg a-> z
somewhere in there , is a value , lets say 12345 , how do i find what column that number is in , because i want to return the first number of the column eg the top of the column ??????

help please !!
cal

 

[NBVC]

I found this formula in one of Aladin's archived replies to a thread. It should work for you with some of the adaptations I did to it.

Try this:

=INDEX($A$1:$Z$1,SUMPRODUCT((ISNUMBER(SEARCH(AA1,$A$2:$Z$300)))*(COLUMN($A$2:$Z$300))))

A1:Z1 contains your column headings to return.
A2:Z300 contains the data to search within
AA1 contains the value to look for (you probably need to change this to suit)

 

[caldara]

columns and ranges

that was brilliant .. it worked first time ,, fantastic thanks a lot