Find what tab Value is on.

[charlee63]

I am trying to find what tab a value is on. The below is Named SheetList

Breads

Breakfast

Cakes

Cookies

Ingredients

Pastries & Sweets

Pies

Rolls

=LEFT(VLOOKUP($A2,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$A$1:$A$1000"),$A2)>0),0))&"'!$A$1:$d$1000"),4,FALSE),FIND(" - ",VLOOKUP($A2,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$A$1:$A$1000"),$A2)>0),0))&"'!$A$1:$d$1000"),4,FALSE),1)-1)
with Ctrl-Shift-Enter

The above formula is in Tab column. But it is given the wrong tab name. It is on the Bread tab not the Breakfast tab.
Can anyone see what the issue is or give me a different way to get info?
Using Excel 13.

 

[Micron]

As for the Or part of your question, maybe just use Ctrl+F and use the option to search the whole workbook as well as Find All option? You might discover that you have the value in more than one sheet.

 

[Logit]

The following is a VBA approach.

The macro presents an InputBox where the user inputs the term to be searched. When the term is located (could be on multiple sheets),
The sheet name and cell location is presented in a listbox on a userform. The same information is also printed to a sheet named "Location".

Option Explicit

Sub SearchAllSheetsAndDisplayResults()
    Dim ws As Worksheet
    Dim searchTerm As String
    Dim foundRange As Range
    Dim firstAddress As String
    Dim results As Collection
    Dim resultText As Variant
    Dim resultsSheet As Worksheet

    searchTerm = InputBox("Enter the term to search for:")
    
    ' Create a new worksheet for results
    On Error Resume Next
    Application.DisplayAlerts = False
    Set resultsSheet = Worksheets("Locations")
    If Not resultsSheet Is Nothing Then resultsSheet.Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    Set resultsSheet = Worksheets.Add
    resultsSheet.Name = "Locations"
    
    ' Create a collection to store results
    Set results = New Collection
    
    For Each ws In ThisWorkbook.Worksheets
        Set foundRange = ws.Cells.Find(What:=searchTerm, LookIn:=xlValues, LookAt:=xlPart, _
                                       SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                       MatchCase:=False)
        If Not foundRange Is Nothing Then
            firstAddress = foundRange.Address
            Do
                resultText = "Sheet: " & ws.Name & " Cell: " & foundRange.Address
                results.Add resultText
                
                ' Paste results into "Locations" sheet
                resultsSheet.Cells(results.Count, 1).Value = ws.Name
                resultsSheet.Cells(results.Count, 2).Value = foundRange.Address
                resultsSheet.Cells(results.Count, 3).Value = foundRange.Value
                
                Set foundRange = ws.Cells.FindNext(foundRange)
            Loop While Not foundRange Is Nothing And foundRange.Address <> firstAddress
        End If
    Next ws
    
    ' Show UserForm with results
    With UserForm1
        .ListBox1.Clear
        For Each resultText In results
            .ListBox1.AddItem resultText
        Next resultText
        .Show
    End With
End Sub

Paste this code in the userform :

Private Sub CommandButton1_Click()
    Unload Me
End Sub

Create the UserForm1 ... paste a Listbox and a command button on the form.

Paste a command button on your worksheet and connect it to the main macro
"SearchAllSheetsAndDisplayResults"