Can Vlookup display result location?

Microsoft Excel 2003 (full product)
November 14, 2010 at 03:39:56
Specs: Windows 7

I have always had excellent result with this site, so I am back again. Thanks you.

I was hoping you could help me with a formula that would do a vlookup accross multipul sheets then give the location/cell of the find.

i.e. Sheet1 put the formula in cell B1 to lookup a name (Smith, John) in cell A1 but search 2-5 sheets in all the same column (E).

Thanks in advance,

See More: Can Vlookup display result location?

November 15, 2010 at 16:17:02
I don't think that there is a built in function that will search across multiple sheets and return the location of the value.

However, this User Defined Function (UDF) seems to return the results you are looking for:

1 - Open the VBA editor (Alt-F11)
2 - Click Insert..Module
3 - Paste the following code into the pane that opens
4 - Enter =FindCell(A1) in B1

Function FindCell(ByVal myArg As Range)
  For nxtSht = 2 To Sheets.Count
    With Sheets(nxtSht).Columns("E")
     Set c = .Find(myArg, lookat:=xlWhole)
      If Not c Is Nothing Then
       tempString = Sheets(nxtSht).Name & "!" & c.Address
       Exit For
      End If
     End With
 FindCell = tempString
End Function

Posting Tip: Before posting Data or VBA Code, read this How-To.

Report •
Related Solutions

Ask Question