How can I use a VLOOKUP function to search a multi-page workbook?
 

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.

How can I use a VLOOKUP function to search a multi-page workbo
 

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.

How can I use a VLOOKUP function to search a multi-page workbo
 

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.

How can I use a VLOOKUP function to search a multi-page workbook?
 

You need workarounds to do 3D-lookups. One way is to define a name "Refs"
which refers to the list of references:

="'[CONTROLMASTER_AUGUST_07]"&TEXT(ROW($1:$31),"00")&"-08'!C1:AE999"

Then enter the lookup formula below in the other spreadsheet making sure to
use the same headings as in the table. Extend the formula by selecting the
range e.g. [A2:B999] and choosing Data Table Column Input Cell: A2 OK:

A B
1 No. Value
2 112 =LOOKUP(REPT("z",99),DGET(INDIRECT(Refs),29,A1:A2) &"")
3 234 =TABLE(,A2)
4 456 =TABLE(,A2)
....

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

How can I use a VLOOKUP function to search a multi-page workbo
 

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