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

Yes, here's an example:
=VLOOKUP(A1,Sheet2!A1:D16,2)
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"J@Y" wrote:

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

Actually, I was refering to searching in more than 1 sheet. So for example, I
have sheet2, sheet3, and sheet4. Each sheet has some data that I want to
vlook at once. How would I do that.

"KC Rippstein" wrote:

Yes, here's an example:
=VLOOKUP(A1,Sheet2!A1:D16,2)
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"J@Y" wrote:

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

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!

Yes, you can. It would be impossible for me to try to explain it here and
in fact the only way I can make sense of the process is with the workbook
open and the comprehensive e-mail explanation Peo sent me.

The example workbook he sent me looks across eight worksheets. It makes
some sense if you study it.

Here are a couple formulas that both do the same thing. In the first
formula Peo named the sheets list MySheets and in the second inserted the
sheet names individually. Much longer formula of course. Perhaps you can
adapt the second one to your needs or make a list of your worksheets and
name them and adapt the first formula to suit.

=VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A2)0) ,0))&"'!A2:C200"),3,0)

=VLOOKUP(A2,INDIRECT("'"&INDEX({"Sheet1";"Sheet2"; "Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Shee t8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3"; "Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A 2:A200"),A2)0),0))&"'!A2:C200"),2,0)

If you like I can send it to you the workbook.

HTH
Regards,
Howard

"J@Y" wrote in message
...
Is there a way to use Vlookup or something like Vlookup to search multiple
worksheets?

Forgot to add use an array enter CTRL+ SHIFT + ENTER

Regards,
Howard

"J@Y" wrote in message
...
Is there a way to use Vlookup or something like Vlookup to search multiple
worksheets?

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!

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!

In article ,
"T. Valko" wrote:

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

D2:D6 = list of sheet names

Biff

Ah yes! Thanks Biff! Much appreciated! It's nice to know that someone
is paying attention... :-)

Cheers!

In article ,
"T. Valko" wrote:

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

Actually, I don't have this add-in either. Unfortunately it's not
compatible with my Mac version of Excel. So I've never actually used it.

However, I suspect that it works in much the same way as a normal 3-D
reference -- all sheets between the two named sheets, inclusive. But
don't quote me... :)

Thanks for the reply. Just a few things I need clarification.
1. Where does that D2:D6= list of sheet names go?
2. What does the $D$2:$D$6 refer to?

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

im trying to do the same thing. Except, my multiple worksheets are also in a
different workbook.

1. Is vlookup possible in this scenario?
2. If so, where does the workboook name go?

I've given it a go, but no success.

I have an brilliant example of a lookup formula that looks up over 8
worksheets in a workbook, I got from Peo Sjoblom.

Maybe you could use it in the "different" workbook and then use a cell
reference to that lookup result cell from the "other" workbook.

It assumes that in the "different" workbook that A2:A??? of each sheet will
have the lookup values, and the return values column will be to the right...
1, 2, or whatever number of columns.

I could send you an example or you could send me an example of your data lay
out.



HTH
Regards,
Howard

"sus" wrote in message
...

im trying to do the same thing. Except, my multiple worksheets are also in
a
different workbook.

1. Is vlookup possible in this scenario?
2. If so, where does the workboook name go?

I've given it a go, but no success.

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!