Return column reference of value in a range

[nightracer]

Hi

I'm trying to return the column number of a specific value from a range.
I can get it to work on a single row using Match, but as soon as I move to a multi-row range it doesn't work.

So in A1:F12 I have:

130.293 131.782 126.154 132.584 126.015 129.799
131.086 131.595 124.231 130.213 123.110 128.141
130.365 130.063 121.647 128.116 122.565 129.057
132.887 133.115 126.164 133.080 126.137 129.236
131.868
133.256 133.962 126.391 133.001 126.605 131.345
130.945 130.391
128.354 129.803
129.698 129.730 123.366 130.680 123.593 129.812
129.883 130.151 123.997 130.665 123.509 129.667
130.664 130.543 123.882 131.163 124.462 129.599
130.371 130.073 123.148 128.525 121.408 128.680

I then want to return (in columns J & K) how many columns across a value is:
131.782 2
131.345 6
130.371 1

Any help appreciated.

Thank you.

 

[Aladin Akyurek]

In K1 control+shift+enter, not just enter, and copy down:

=MIN(IF($A$1:$F$12=J1,COLUMN($A$1:$F$12)))

 

[alansidman]

A VBA alternative:

Option Explicit


Sub FindNum()
    Dim c As Range
    Dim rng As Range
    Dim lr As Long, lc As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
    Set rng = Range("A1:F12")
    Dim iFind As Single
    iFind = InputBox("What number to search?")
    For Each c In rng
        If c.Value = iFind Then
            MsgBox ("Your column is " & c.Column)
        End If
    Next c


End Sub

 

[nightracer]

Brilliant, thank you Aladin and Alan, I would never have found those solutions!

Thanks gain
Nightracer