Download an install the free add-in Morefunc.xll...
=VLOOKUP(A2,THREED('Sheet1:Sheet5'!$B$2:$C$100),2 ,0)

I don't have this add-in. Does the THREED function require the sheets to be
in a contiguous order?

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!