This is brilliant, I've used the option without the add-in (it wouldn't
download) and it does exactly what it's supposed to. However, as I think is
the case with vlookup, it only returns the first match it finds. Is there any
way to adapt this to deal with situations when there are multiple matches?

(Slight aside, does using '0' as the Range_lookup work exactly the same as
'FALSE'?)


"Domenic" wrote:

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!