find a value in a range of columns

caldara

New Member
Joined
Nov 30, 2005
Messages
2
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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

New Member
Joined
Nov 30, 2005
Messages
2
columns and ranges

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

Watch MrExcel Video

Forum statistics

Threads
1,118,234
Messages
5,571,045
Members
412,359
Latest member
misstoffeepenny
Top