Forum Discussion

qplsn9's avatar
qplsn9
Copper Contributor
Dec 05, 2023

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

Hello, I want to search for the Label ID in multiple sheets within a workbook. For example, I want to search for "WD734895" in cell A2 in Book1 within the contents of the workbook 2024.01.xlsx and in all sheets within that workbook. Once it is found, I would like to return the cell value that contains the Z#. For example, "WD734895" is searched in 2024.01.xlsx, and if found (located in 2024.01.xlsx cell F14), it will return Z2. I would like to do this will the tray and slot as well. I want to return the tray and slot (located in A2 and B2 respectively for Z1) if "WD734895" is found within the workbook.

Along with that, is it possible to do the same above but instead of returning the Z#, if found, it will return the workbook name "2024.01" with the sheet named concatenated on "30" with a "." in between? So search "WD734895" in 2024.01.xlsx and, if found, it will return the value "2024.01.30" in cell C2?

 

excel
Formulas and Functions
Macros and VBA

1 Reply

  • PenelopeAdams's avatar
    PenelopeAdams
    Iron Contributor
    Apr 05, 2025

    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.

Resources