Vlookup across multiple worksheets and list sheet names

onexc · Jun 3rd 2022

Hello,

I use this array formula to reveal sheetname if lookup value found in one of the sheets.

Code

{=IFERROR(INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$A:$A"),A4)>0),0)),"0")}

The problem is that, This formula finds and displays the sheet name where first occurrence of value found. For instance, value A150 is found in Sheet2. However, the same value also exists in the Sheet3. I also want to display other sheet name where lookup value found. Any advice would be great.

**Carim** · Jan 14th 2023

Hello Onexc,

Hope you are doing fine ... (long time ...)

Just stumbled upon your post ... which reminded me of the code written by Chip PEARSON to determine if a sheet exists ... or not

Code

Function SheetExists(SheetName As String, Optional WBook As Workbook) As Boolean

' Code written by Chip Pearson to return True or False
' Usage : If SheetExists("Hello") = True Then...

Dim WB As Workbook
Dim sName As String
    If WBook Is Nothing Then
        Set WB = ThisWorkbook
    Else
        Set WB = WBook
    End If
    
    On Error Resume Next
    sName = WB.Worksheets(SheetName).Name
    If Err.Number = 0 Then
        SheetExists = True
    Else
        SheetExists = False
    End If

End Function

Display More

Hope this could help

Vlookup across multiple worksheets and list sheet names

Participate now!

Share

Tags

Users Viewing This Thread