Try this:

Input: Sheet1
Output: Sheet2

Checks if = value

HTH

Sub Find_Row2COL()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim x As Variant
Dim findRng As Range
Dim r As Long, c As Integer, rr As Long

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

Set findRng = Application.InputBox("Enter Range to be searched", Type:=8)
Findval = Application.InputBox("Enter value to be found", Type:=1)

ws2.Columns("A:B").ClearContents
rr = 1
ws2.Cells(1, 1).Resize(1, 2) = Array("Row heading", "Column heading")

x = ws1.Range(findRng.Address)

For r = 2 To UBound(x, 1)
For c = 2 To UBound(x, 2)
If x(r, c) = Findval Then
rr = rr + 1
ws2.Cells(rr, 1) = x(r, 1)
ws2.Cells(rr, 2) = x(1, c)
End If
Next c
Next r

End Sub

"Toppers" wrote:

What is the range of your data (rows/columns) and do want the output to be a
list of headings in two columns?

"JB2010" wrote:

Hi


yeah, really i do want a limitless listing going off down the page of all
those refs that are over 150. i just thought it was easier to start this
thread with just saying one cell returned!


i cant begin to imagine how i would write the code to do something like that?


jb

"Toppers" wrote:

What would you want if two (or more) cells meet your condition.


I think you will need VBA for this as the data will be random i.e. not
sorted ascending or descending order so MATCH (or "LOOKUPS") etc not would
work on "nearest" value searches.