Search for value across multiple columns, workbooks, and sheets then return a cell value?

1. Applicable scenarios: in the same workbook search in different worksheets.
excel 
=VLOOKUP("search value", INDIRECT("'"&A1&"'!A:B"), 2, FALSE) 
A1 = worksheet name (e.g., Sheet2) 
A:B = search range 
2 = return the value of the second column 
2.Applicable scenarios: in the current worksheet to find the number of columns and return the corresponding row data.
excel 
=INDEX(B:B, MATCH("search value", A:A, 0)) 
search for values in column A and return the contents of the corresponding row in column B.
3. Applicable scenarios: in a number of columns to filter the value of the conditions.
excel 
=FILTER(A:B, (A:A="search value") + (B:B="search value"), "not found") 
check both columns A and B, return the entire row of matching data.
4. Scenario: search for data in multiple Excel files.
Data > Get Data > From File > From Folder (select all workbooks).
Merge the data in the Power Query editor.
Use filters to find the target value and load it back into Excel. 
5. Scenario: Need to traverse all worksheets/workbooks for a complex search.
vba 
Function SearchAcrossSheets(SearchValue As String) As String 
 Dim ws As Worksheet 
 Dim rng As Range 
 Dim result As String 
 For Each ws In ThisWorkbook. Worksheets 
 Set rng = ws.Cells.Find(SearchValue) 
 If Not rng Is Nothing Then 
 result = "Found in " & ws.Name & " of " & rng.Address & ", the neighborhood value is: " & rng.Offset(0, 1). Value 
 Exit For 
 End If 
 Next ws 
    
 If result = "" Then result = "Not Found" 
 SearchAcrossSheets = result 
End Function 
Call =SearchAcrossSheets("Target Value") in Excel.