Excel - Where did VLOOKUP find it?

  powerless 15:19 09 Jan 11

I have an excel spreadsheet that has several worksheets. On my first sheet (Summary) I have a bunch of numbers that I need to return some information on. This information is on one of the other sheets (Data1, Data2, etc).

I can use =IF(ISNA(VLOOKUP( etc to go and find the information on the other sheets; it will look in the first sheet (Data1) and if it did not find anything it will then go and look in the second sheet (Data2) and so on until it finds it or not.

Is there a way for excel to return the sheet name where the VLOOKUP found something?

  VoG II 17:47 09 Jan 11

With a UDF

Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, Col_num As Integer, Optional Range_look As Boolean)

'Written by OzGrid.com

'click here

'Modified by VoG

'Use VLOOKUP to Look across ALL Worksheets and stops at the first match found.

Dim wSheet As Worksheet
Dim vFound
On Error Resume Next
For Each wSheet In ActiveWorkbook.Worksheets
With wSheet
Set Tble_Array = .Range(Tble_Array.Address)
vFound = WorksheetFunction.VLookup _
(Look_Value, Tble_Array, _
Col_num, Range_look)
End With
If Not IsEmpty(vFound) Then Exit For
Next wSheet
Set Tble_Array = Nothing
VLOOKAllSheets = wSheet.Name
End Function

See click here for instructions. I've modified the code to return the sheet name instead of the lookup value.

  powerless 21:58 09 Jan 11

Hi Vog,

I've got it all in and am not getting any errors but it's not working. It keeps returning the Summary page sheet with the value I am looking up.

See example: htpp://click here

  powerless 22:00 09 Jan 11
  powerless 22:02 09 Jan 11

I should say I've tried a few iterations of the VLOOKAllSheets not just what is shown in the file.

  powerless 13:42 10 Jan 11

OK I figured it out.

"Where "Dog" is the value to find"

I was pointing it to a cell on the summary sheet, so it's now working.

But I need to point it to a cell on the summary sheet, can this done?

  VoG II 16:39 10 Jan 11

Sorry, I don't know. Try asking here click here posting your full formula. One of the formula gurus can probably solve it.

  shellship 17:59 10 Jan 11

Oh dear, and I always believed you to be omnipotent and all-knowing and never stumped on things Excel. My faith has taken a severe knock.

This thread is now locked and can not be replied to.

Surface Pro (2017) vs Surface Pro 4

Where HTML5 is headed next

MacBook Pro v Surface Pro 5