How to search for values in Excel "table"?

Question

If I've for example this table:

a,6,10,22,35,46
b,2,7,11,23,44,78
c,2,10,15,16,32,66,98
d,7,8,10,11,23,25,30
e,23,24

Now I want to search for occurances of a certain value and returns the value of the first column of each corresponding row.

So 2 gives: b,c. And 23 gives b,d,e.

Like:

2,b,c
23,b,d,e

Is it possible to achieve this by using the VLOOPUP- or another function?

Thanks!

Answer 1

If you're open to a vba solution, you can create your own function:

just make sure to insert it into a new module

Option Explicit

Public Function INCOLUMNS(ByVal value As String, ByVal searchrange As Range) As String

    Dim res As String
    Dim i As Long
    Dim temp As Range

    For i = 1 To searchrange.Columns.Count
        Set temp = Range(Cells(1, i), Cells(searchrange.Rows.Count, i)). _ 
                   Find(value, LookIn:=xlValues, LookAt:=xlWhole)
        If Not temp Is Nothing Then
            If res = "" Then
                res = Split(Cells(1, i).Address, "$")(1)
            Else
                res = res & ", " & Split(Cells(1, i).Address, "$")(1)
            End If
        End If
    Next i

    INCOLUMNS = res
End Function

And then you can use it inside the Worksheet like so: