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:

Answer 2

If I'm understanding the problem correctly, and that the Column is consistent with , and no spacing, the formula that I made is the following:

=IF(ISNUMBER(SEARCH(",23,",A1)),"23,b,d,e",IF(ISNUMBER(SEARCH(",2,",A1)),"2,b,c","N/A"))

Assuming the a,6,10,22,35,46 is in A1, you would for example paste this in B1 and drag the formula down. This would get you the results that you are looking for in your example.

Answer I got with this formula:

Documentation on IF(ISNUMBER(SEARCH formula: https://exceljet.net/formula/if-cell-contains

Hope this helps,

-Maykid