Excel: Find value in column(s)

Question

I've got an Excel spreadsheet with multiple columns of different lengths, each filled with unsorted numbers. Each column has a header.

Is there a way to determine which column(s) contain that number?

For instance, I'd love to be able to do =WHICHCOLS( 123, A, Z ) and have Excel tell me columns [B, C, and K] contain cells with a value of 123. Though, ideally, I'm after a comma separated list of headers.

Equally adequate would be to find all cells, in a range (or ranges), that have that value; e.g. [B19, C32, and K908].

I feel like I'm overlooking some obvious built-in function.

Answer 1

There might be an built-in way to do this but you can also write your own function.

Put this code in a VBA Module:

Public Function WHICHCOLS(searchValue As Double, srcRange As Range) As String
    Dim rangeColumn As Range
    Dim columnCell As Range

    Dim headerRow As Long
    headerRow = 1 ' HeaderInformation is in RowNr 1 '

    WHICHCOLS = vbNullString
    For Each rangeColumn In srcRange.Columns
        For Each columnCell In rangeColumn.Cells
            If columnCell = searchValue Then
                If WHICHCOLS <> vbNullString Then WHICHCOLS = WHICHCOLS & ", "
                WHICHCOLS = WHICHCOLS & srcRange.Parent.Cells(headerRow, columnCell.Column)
                Exit For
            End If
        Next columnCell
    Next rangeColumn
End Function

An example call in Excel would be:

=WHICHCOLS(7,A2:F3)