Searching for a Value Using a Function

Sorry, but I rearranged parameters for the ListMatchingCells function described in my previous comments below. Here is the new syntax:
=ListMatchingCells(Value,[LookIn],[MatchCase],[MatchEntire],[AllSheets])
Value can be a numeric or text constant or a formula's result or a cell (like A1) with a non-blank value; * and ? are wild card characters and ~ is the escape character, the same as Find (Ctrl+F).
The first character of LookIn (case ignored) must be V for Values (default), F for Formulas, N for Notes (legacy unthreaded comments), C for Comments (threaded), or A for All (V, F, N, and C).
If MatchCase is FALSE (default), alphabetic case will be ignored when Value is text; if TRUE, the case of a text Value must match.
If MatchEntire is FALSE (default), a partial match is accepted; if TRUE, the entire content must match.
If AllSheets is FALSE (default), only the current worksheet will be evaluated; if TRUE, all worksheets in the workbook will be included.
See https://sites.google.com/view/MyExcelToolbox/

Two optional parameters have been added to the ListMatchingCells function described in my previous comment below. Here is the new syntax:
=ListMatchingCells(Value,[MatchCase],[AllSheets],[LookIn],[LookAt])
The first character of LookIn must be V for Values (default), F for Formulas, N for Notes (legacy unthreaded comments), C for Comments (threaded), or A for All (V, F, N, and C).
The first character of LookAt must be P for Partial (default) or W for Whole (match entire cell).
Alphabetic case of LookIn and LookAt is ignored.
See https://sites.google.com/view/MyExcelToolbox/

My Excel Toolbox includes the following dynamic array function to return a list of cells matching Value:
=ListMatchingCells(Value,[MatchCase],[AllSheets])
Value can be a numeric or text constant or a formula's result or a cell (like A1) with a non-blank value.
If MatchCase is FALSE (default), alphabetic case will be ignored when Value is text; if TRUE, the case of a text Value must match.
If AllSheets is FALSE (default), only the current worksheet will be evaluated; if TRUE, all worksheets in the workbook will be included.
Excel 365 or 2021+ is required when this function is used in a cell formula; it must NOT be used with My Excel Toolbox's SpillArray function (directly or indirectly). In older versions of Excel, it can be called by a macro (Sub) with results in a MsgBox or posted to a worksheet.
Here is a simplified version:

Function ListMatchingCells(Value, Optional MatchCase)
    Dim C As Collection, rCell As Range, n As Long
    Dim sAddr As String, sFirst As String, sSheet As String
    Application.Volatile
    If Value = vbNullString Then
        ListMatchingCells = CVErr(xlErrValue)
        Exit Function
    End If
    Set C = New Collection
    C.Add "Cells matching '" & Value & "'"
    With Application.Caller.Worksheet.UsedRange
        Set rCell = .Cells((.Rows.Count - .Row + 1), _
            (.Columns.Count - .Column + 1)) ' last cell
        Set rCell = .Find(What:=Value, After:=rCell, _
            LookIn:=xlValues, LookAt:=xlWhole, _
            SearchOrder:=xlByColumns, _
            SearchDirection:=xlNext, _
            MatchCase:=MatchCase)
        If Not (rCell Is Nothing) Then
            sAddr = rCell.Address
            sFirst = sAddr
            sSheet = "'" & rCell.Parent.Name & "'!"
            Do
                C.Add sSheet & sAddr
                ' Range.FindNext does NOT work in a UDF
                Set rCell = .Find(What:=Value, After:=rCell)
                sAddr = rCell.Address
            Loop Until sAddr = sFirst
        End If
    End With
    If C.Count = 1 Then C.Add "None"
    ReDim A(1 To C.Count, 1 To 1) As Variant
    For n = 1 To C.Count
        A(n, 1) = C(n)
    Next n
    Set C = Nothing
    ListMatchingCells = A
End Function

See https://sites.google.com/view/MyExcelToolbox/

The Tip's FindAddr user-defined function (UDF) can also be used in a cell formula. For example:
=FindAddr(345)
=FindAddr("my text")
=FindAddr($A$1)
=$A$1&" @ "&FindAddr($A$1)
It searches cells in each worksheet 1 to Worksheets.Count, columns left-to-right, rows top-to-bottom, and returns only the first cell with a value that wholly matches the searched value ignoring alphabetic case and cell format. For each worksheet, cell $A$1 is searched last.
The UDF could be modified to return all matching cells as an array.