Excel - Where did VLOOKUP find it?

  powerless 15:19 09 Jan 2011

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?

  [DELETED] 17:47 09 Jan 2011

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 2011

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 2011
  powerless 22:02 09 Jan 2011

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 2011

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?

  [DELETED] 16:39 10 Jan 2011

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

  [DELETED] 17:59 10 Jan 2011

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.

Elsewhere on IDG sites

How to watch the World Cup for free on TV and online

Adobe's brand-new video editing app Project Rush works across your computer, tablet and phone

Best external graphics cards (eGPUs) for Mac

Les meilleurs jeux pour Android (2018)