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:n=ListMatchingCells(Value,[LookIn],[MatchCase],[MatchEntire],[AllSheets])nValue 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). nThe 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).nIf MatchCase is FALSE (default), alphabetic case will be ignored when Value is text; if TRUE, the case of a text Value must match.nIf MatchEntire is FALSE (default), a partial match is accepted; if TRUE, the entire content must match.nIf AllSheets is FALSE (default), only the current worksheet will be evaluated; if TRUE, all worksheets in the workbook will be included.nSee 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:n=ListMatchingCells(Value,[MatchCase],[AllSheets],[LookIn],[LookAt])nThe 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).nThe first character of LookAt must be P for Partial (default) or W for Whole (match entire cell).nAlphabetic case of LookIn and LookAt is ignored.nSee https://sites.google.com/view/MyExcelToolbox/

My Excel Toolbox includes the following dynamic array function to return a list of cells matching Value:n=ListMatchingCells(Value,[MatchCase],[AllSheets])nValue can be a numeric or text constant or a formula's result or a cell (like A1) with a non-blank value.nIf MatchCase is FALSE (default), alphabetic case will be ignored when Value is text; if TRUE, the case of a text Value must match.nIf AllSheets is FALSE (default), only the current worksheet will be evaluated; if TRUE, all worksheets in the workbook will be included.nExcel 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.nHere is a simplified version:nnFunction ListMatchingCells(Value, Optional MatchCase)n    Dim C As Collection, rCell As Range, n As Longn    Dim sAddr As String, sFirst As String, sSheet As Stringn    Application.Volatilen    If Value = vbNullString Thenn        ListMatchingCells = CVErr(xlErrValue)n        Exit Functionn    End Ifn    Set C = New Collectionn    C.Add "Cells matching '" & Value & "'"n    With Application.Caller.Worksheet.UsedRangen        Set rCell = .Cells((.Rows.Count - .Row + 1), _n            (.Columns.Count - .Column + 1)) ' last celln        Set rCell = .Find(What:=Value, After:=rCell, _n            LookIn:=xlValues, LookAt:=xlWhole, _n            SearchOrder:=xlByColumns, _n            SearchDirection:=xlNext, _n            MatchCase:=MatchCase)n        If Not (rCell Is Nothing) Thenn            sAddr = rCell.Addressn            sFirst = sAddrn            sSheet = "'" & rCell.Parent.Name & "'!"n            Don                C.Add sSheet & sAddrn                ' Range.FindNext does NOT work in a UDFn                Set rCell = .Find(What:=Value, After:=rCell)n                sAddr = rCell.Addressn            Loop Until sAddr = sFirstn        End Ifn    End Withn    If C.Count = 1 Then C.Add "None"n    ReDim A(1 To C.Count, 1 To 1) As Variantn    For n = 1 To C.Countn        A(n, 1) = C(n)n    Next nn    Set C = Nothingn    ListMatchingCells = AnEnd FunctionnnSee https://sites.google.com/view/MyExcelToolbox/

The Tip's FindAddr user-defined function (UDF) can also be used in a cell formula. For example:n=FindAddr(345)n=FindAddr("my text")n=FindAddr($A$1)n=$A$1&" @ "&FindAddr($A$1)nIt 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.nThe UDF could be modified to return all matching cells as an array.