"Toppers" wrote...
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
....

More delightful hard-coding.

This doesn't require udfs if one's willing to use volatile functions. The
following array formula would work.

=VLOOKUP(val,INDIRECT("'"&IF(wbname<"","["&wbname&"]","")&INDEX(wslst,
MATCH(TRUE,COUNTIF(INDIRECT("'"&IF(wbname<"","["&wbname&"]","")&wslst
&IF(rng<"","'!"&rng,"")),val)0,0))&IF(rng<"","' !"&rng,"")),col,0)

where val is the lookup value, wbname is an optional common workbook name,
wslst is a list of worksheet names, rng is an optional common range address,
and col is the column index in the table from which to return the result.

If only worksheets within the workbook containing this formula need to be
searched, leave wbname blank. If different worksheets in different workbooks
need to be searched, leave wbname blank and include the workbook names with
worksheet names in wslst. If different ranges would need to be searched in
different worksheets, leave rng blank and include the range addresses with
worksheet names in wslst. For example, with wbname and rng both blank and
wslst containing

[foo.xls]A'!A2:D21
[bar.xls]B'!X99:AA2000
[ugh.xls]C'!IS10000:IV50000

(yes, with single quotes before the exclamation points), the formula would
search each in turn for val.

As for the udf approach, might as well make it general by adding an array
argument that would hold the textrefs for the ranges to be searched in
sequence.