Vlookup - another way

[kizzie37]

Hi

I need to know if perhaps this is possible through either a v lookup or a Match/index

I have values in column A (say 44403) I want to look this number up (an return a value in a later column), the catch being that there are 10 pages within the workbook that may have it listed (only once though). It will always be located in the first column of the page.

I have only ever used lookups and matches for a singular databse, never potentialy 10 or more on different pages.

Does anyone have any ideas or suggestions if its odable?.

 

[Aladin Akyurek]

Hi

I need to know if perhaps this is possible through either a v lookup or a Match/index

I have values in column A (say 44403) I want to look this number up (an return a value in a later column), the catch being that there are 10 pages within the workbook that may have it listed (only once though). It will always be located in the first column of the page.

I have only ever used lookups and matches for a singular databse, never potentialy 10 or more on different pages.

Does anyone have any ideas or suggestions if its odable?.

What would be the formula if you just had one page, and what result is expected to obtain - text or a number?

 

[kizzie37]

Thanks, solved it


used OZ grid:

Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, _

Col_num as Integer, Optional Range_look as Boolean)



''''''''''''''''''''''''''''''''''''''''''''''''

'Written by OzGrid.com



'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 = vFound

End Function