Put this code in your "other w/book" (not "Control_Master_August_07" which
must be open) .

Function mvlookup(srchval, srchindex)
Dim sh As Worksheet
Dim srchrng As Range

Set wb = Workbooks("Control_Master_August_07")

For Each sh In wb.Worksheets
Set srchrng = sh.Range("C:AE")
res = Application.VLookup(srchval, srchrng, srchindex, 0)
If Not IsError(res) Then
mvlookup = res
Exit Function
End If
Next sh
mvlookup = ""
End Function


in say B2 put:

=MVLOOKUP(A2,29)

this will search on A2 value and return result in B2.

Copy down if/as required

HTH

"Chrisl147" wrote:

Hi

Looks very promising... Need to give you more detail I think as I'm very
new to UDFs:

I'm trying to return a value from a spreadsheet called "control master
august 07" and the sheets are for each working day "01-08", "02-08" etc. The
reference value is in column C

I will be in a different spreadsheet and the refence value will be in column
A.

I have tried to create the function, detailed below:

Function mvlookup(srchval, srchindex)
Dim sh As Worksheet
Dim srchrng As Range
For Each sh In CONTROLMASTER_AUGUST_07(1 - 8, 2 - 8, 3 - 8)
Set srchrng = sh.Range("C:AE")
res = Application.VLookup("A:A", "C:AE", 29, False)
If Not IsError(res) Then
mvlookup = res
Exit Function
End If
Next sh
mvlookup = ""
End Function

Thank-you for your help, this is way above me at the moment...

"Toppers" wrote:

Perhaps this UDF will help:

Function mvlookup(srchval, srchindex)
Dim sh As Worksheet
Dim srchrng As Range
For Each sh In Sheets(Array("Sheet1", "Sheet2", "Sheet3")) '<== change
Set srchrng = sh.Range("A:B") '<=== change range
res = Application.VLookup(srchval, srchrng, srchindex, 0)
If Not IsError(res) Then
mvlookup = res
Exit Function
End If
Next sh
mvlookup = ""
End Function

e.g in a cell put:

=MVLOOKUP("abc",2)

will find "abc" and return value from column B

"Chrisl147" wrote:

I regularily use VLOOKUP but want to create a LOOKUP that will search all
pages of a workbook and return the required data. I am using a distinct
customer number that will only appear once.