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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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)
 
Upvote 0

Forum statistics

Threads
1,211,843
Messages
6,104,311
Members
447,901
Latest member
boy3hc2004

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
Back
Top