You left out the important part for option 2.......

D2:D6 = list of sheet names

Biff

"Domenic" wrote in message
...
In article ,
J@Y wrote:

Is there a way to use Vlookup or something like Vlookup to search
multiple
worksheets?

Here are a couple of options...

Assumptions:

Sheet1 through Sheet5 contain the tables

On each sheet, B2:C100 contains the table

A2 contains the lookup value

[Option 1]

Download an install the free add-in Morefunc.xll...

=VLOOKUP(A2,THREED('Sheet1:Sheet5'!$B$2:$C$100),2, 0)

Note that the add-in can be downloaded in the following link...

http://xcell05.free.fr/

[Option 2]

Without the add-in...

=VLOOKUP(A2,INDIRECT("'"&INDEX($D$2:$D$6,MATCH(TRU E,COUNTIF(INDIRECT("'"&
$D$2:$D$6&"'!B2:B100"),A2)0,0))&"'!B2:C100"),2,0)

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!