Can VLOOKUP be used to search columns in multiple sheets in a workbook?

Yes, if the sheets are identical to each other.

If you have the lookup value in A2 on a summary sheet and the sheets you
want to lookup are Sheet1:Sheet8,
the table is A1:C200 and you want to return the value in the second column
(B)


=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 1:A200"),A2)0),0))&"'!A1:C200"),2,0)

entered with ctrl + shift & enter

if you put all sheet names in a range of cells and give it a name it is less
ugly

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

where MySheets would hold the names

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"dford" wrote in message
...
Can VLOOKUP be used to search columns in multiple sheets in a workbook?

This is the formula that I would like to search on 8 different sheets. The
sheets will all be the same. The range to search is A1:E1000 on each sheet.

=IF(ISERROR(VLOOKUP(A9,'[Okeene raw
materials.xls]Sheet1'!$A$1:$E$1000,4,FALSE)),0,VLOOKUP(A9,'[Okeene raw
materials.xls]Sheet1'!$A$1:$E$1000,4,FALSE))

"Peo Sjoblom" wrote:

Yes, if the sheets are identical to each other.

If you have the lookup value in A2 on a summary sheet and the sheets you
want to lookup are Sheet1:Sheet8,
the table is A1:C200 and you want to return the value in the second column
(B)


=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 1:A200"),A2)0),0))&"'!A1:C200"),2,0)

entered with ctrl + shift & enter

if you put all sheet names in a range of cells and give it a name it is less
ugly

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

where MySheets would hold the names

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"dford" wrote in message
...
Can VLOOKUP be used to search columns in multiple sheets in a workbook?

Hi again Peo,



Thanks

"Peo Sjoblom" wrote in message
...
Yes, if the sheets are identical to each other.

If you have the lookup value in A2 on a summary sheet and the sheets you
want to lookup are Sheet1:Sheet8,
the table is A1:C200 and you want to return the value in the second column
(B)


=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 1:A200"),A2)0),0))&"'!A1:C200"),2,0)

entered with ctrl + shift & enter

if you put all sheet names in a range of cells and give it a name it is
less ugly

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

where MySheets would hold the names

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"dford" wrote in message
...
Can VLOOKUP be used to search columns in multiple sheets in a workbook?

Hmmmm,

Whatever this post has to do with anything...?

sorry

Howard

"L. Howard Kittle" wrote in message
...
Hi again Peo,



Thanks

"Peo Sjoblom" wrote in message
...
Yes, if the sheets are identical to each other.

If you have the lookup value in A2 on a summary sheet and the sheets you
want to lookup are Sheet1:Sheet8,
the table is A1:C200 and you want to return the value in the second
column (B)


=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 1:A200"),A2)0),0))&"'!A1:C200"),2,0)

entered with ctrl + shift & enter

if you put all sheet names in a range of cells and give it a name it is
less ugly

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

where MySheets would hold the names

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"dford" wrote in message
...
Can VLOOKUP be used to search columns in multiple sheets in a workbook?

Hi Peo,

Your formula appears to work without making it an array. Is the
"entered with ctrl + shift & enter" instruction necessary?

Peo Sjoblom Wrote:

=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 1:A200"),A2)0),0))&"'!A1:C200"),2,0)

entered with ctrl + shift & enter




--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=531235

Hi John,

the hardcoded formula works but if you use

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

you need to array enter it. OTOH the one using a sheet list and a defined
name is smaller and has better flexibility since you don't have to edit the
formula if you add sheets

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"John James" wrote
in message ...

Hi Peo,

Your formula appears to work without making it an array. Is the
"entered with ctrl + shift & enter" instruction necessary?

Peo Sjoblom Wrote:

=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 1:A200"),A2)0),0))&"'!A1:C200"),2,0)

entered with ctrl + shift & enter




--
John James
------------------------------------------------------------------------
John James's Profile:
http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=531235

Hi again Peo,

You are, of course, correct.

I don't understand why these two formulae behave fundamentally
differently (hardcoded array versus entering array within the
spreadsheet cells), with the first returning a value and the second
returning an array.

Also, when I examine the outer "Index" array within your formula (by
selecting cells over columns and rows and entering with ctrl + shift &
enter, I can as expected see the entire array (all sheets) for the
spreadheet cells option, but surprisingly only the sheet1 array for the
hard-coded option:

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

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

Do you know why the different behaviour applies?


Peo Sjoblom Wrote:
Hi John,

the hardcoded formula works but if you use

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

you need to array enter it.


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=531235

Hi Peo,

WOW....!!

Could you send me an example workbook that demonstrates that lookup formula,
please?

Maybe with some description of some of the details..?

Many thanks, as always, for your contributions.

Regards,
Howard

"dford" wrote in message
...
Can VLOOKUP be used to search columns in multiple sheets in a workbook?

On its way

Peo



"L. Howard Kittle" wrote in message
...
Hi Peo,

WOW....!!

Could you send me an example workbook that demonstrates that lookup
formula, please?

Maybe with some description of some of the details..?

Many thanks, as always, for your contributions.

Regards,
Howard

"dford" wrote in message
...
Can VLOOKUP be used to search columns in multiple sheets in a workbook?

I use the formula below to search 1 worksheet. What is the best way to be
able to search multiple worksheets?

=IF(ISERROR(VLOOKUP(A9,'[Ingredients Spanish
Fork.xls]Sheet1'!$B$1:$E$1000,4,FALSE)),0,VLOOKUP(A9,'[Ingredients Spanish
Fork.xls]Sheet1'!$B$1:$E$1000,4,FALSE))


"Peo Sjoblom" wrote:

On its way

Peo



"L. Howard Kittle" wrote in message
...
Hi Peo,

WOW....!!

Could you send me an example workbook that demonstrates that lookup
formula, please?

Maybe with some description of some of the details..?

Many thanks, as always, for your contributions.

Regards,
Howard

"dford" wrote in message
...
Can VLOOKUP be used to search columns in multiple sheets in a workbook?

Are your worksheets identical in layout like table construction where they
all are using

$B$1:$E$1000

?

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"dford" wrote in message
...
I use the formula below to search 1 worksheet. What is the best way to be
able to search multiple worksheets?

=IF(ISERROR(VLOOKUP(A9,'[Ingredients Spanish
Fork.xls]Sheet1'!$B$1:$E$1000,4,FALSE)),0,VLOOKUP(A9,'[Ingredients Spanish
Fork.xls]Sheet1'!$B$1:$E$1000,4,FALSE))


"Peo Sjoblom" wrote:

On its way

Peo



"L. Howard Kittle" wrote in message
...
Hi Peo,

WOW....!!

Could you send me an example workbook that demonstrates that lookup
formula, please?

Maybe with some description of some of the details..?

Many thanks, as always, for your contributions.

Regards,
Howard

"dford" wrote in message
...
Can VLOOKUP be used to search columns in multiple sheets in a
workbook?

Yes. All sheets are identical.

"Peo Sjoblom" wrote:

Are your worksheets identical in layout like table construction where they
all are using

$B$1:$E$1000

?

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"dford" wrote in message
...
I use the formula below to search 1 worksheet. What is the best way to be
able to search multiple worksheets?

=IF(ISERROR(VLOOKUP(A9,'[Ingredients Spanish
Fork.xls]Sheet1'!$B$1:$E$1000,4,FALSE)),0,VLOOKUP(A9,'[Ingredients Spanish
Fork.xls]Sheet1'!$B$1:$E$1000,4,FALSE))


"Peo Sjoblom" wrote:

On its way

Peo



"L. Howard Kittle" wrote in message
...
Hi Peo,

WOW....!!

Could you send me an example workbook that demonstrates that lookup
formula, please?

Maybe with some description of some of the details..?

Many thanks, as always, for your contributions.

Regards,
Howard

"dford" wrote in message
...
Can VLOOKUP be used to search columns in multiple sheets in a
workbook?