G
Guest
Is there a way to use Vlookup or something like Vlookup to search multiple
worksheets?
worksheets?
G
Guest
Yes, here's an example:
=VLOOKUP(A1,Sheet2!A1
16,2)
=VLOOKUP(A1,Sheet2!A1
16,2)
G
Guest
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.
have sheet2, sheet3, and sheet4. Each sheet has some data that I want to
vlook at once. How would I do that.
D
Domenic
J@Y said:
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(TRUE,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!
L
L. Howard Kittle
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";"Sheet8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),2,0)
If you like I can send it to you the workbook.
HTH
Regards,
Howard
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";"Sheet8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),2,0)
If you like I can send it to you the workbook.
HTH
Regards,
Howard
L
L. Howard Kittle
Forgot to add use an array enter CTRL+ SHIFT + ENTER
Regards,
Howard
Regards,
Howard
T
T. Valko
You left out the important part for option 2.......
D26 = list of sheet names
Biff
D2
6 = list of sheet namesBiff
Domenic said:
T
T. Valko
Download an install the free add-in Morefunc.xll...
I don't have this add-in. Does the THREED function require the sheets to be
in a contiguous order?
Biff
I don't have this add-in. Does the THREED function require the sheets to be
in a contiguous order?
Biff
Domenic said:
D
Domenic
T. Valko said:You left out the important part for option 2.......
D2
Biff
Ah yes! Thanks Biff! Much appreciated! It's nice to know that someone
is paying attention...
Cheers!
D
Domenic
I don't have this add-in. Does the THREED function require the sheets to be
in a contiguous order?
Biff[/QUOTE]
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...
G
Guest
Thanks for the reply. Just a few things I need clarification.
1. Where does that D26= list of sheet names go?
2. What does the $D$2:$D$6 refer to?
1. Where does that D2
6= list of sheet names go?2. What does the $D$2:$D$6 refer to?
Domenic said:
G
Guest
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.
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.
L
L. Howard Kittle
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.
(e-mail address removed)
HTH
Regards,
Howard
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.
(e-mail address removed)
HTH
Regards,
Howard
N
nanook
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'?)
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 said: