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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,552
Messages
5,838,063
Members
430,527
Latest member
MyFace2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top