Lookup across multiple worksheets

**DCSwearingen** · 08-26-2005, 05:22 PM

Is it possible to use a lookup function across multiple sheets?

Example:
A workbook contains 26 sheets. Can a function on Sheet1 look across sheet2 through sheet26 and if sheet9 cell A1 contains a specific value (only one of the sheets can contain the value) return the contents of sheet9 cell A2.

Biff · 08-26-2005, 11:05 PM

Hi!

Yes, it's possible if you're meaning a V or H lookup. Sounds like you need a
HLOOKUP.

The lookup range must be EXACTLY the same on ALL of the sheets.

Create a lst of your sheet names and give that list a name. Something like
SheetList

Enter this formula using the key combo of CTRL,SHIFT,ENTER:

=HLOOKUP(A1,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A1:E1"),A1)>0,0))&"'!A1:E5"),2,0)

The lookup value is in cell A1. The lookup range on each sheet is A1:E5. The
row_index_number is 2.

Biff

"DCSwearingen" <DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com>
wrote in message
news:DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com...
>
> Is it possible to use a lookup function across multiple sheets?
>
> Example:
> A workbook contains 26 sheets. Can a function on Sheet1 look across
> sheet2 through sheet26 and if sheet9 cell A1 contains a specific value
> (only one of the sheets can contain the value) return the contents of
> sheet9 cell A2.
>
>
> --
> DCSwearingen
>
>
> ------------------------------------------------------------------------
> DCSwearingen's Profile:
> http://www.excelforum.com/member.php...o&userid=21506
> View this thread: http://www.excelforum.com/showthread...hreadid=399630
>

**DCSwearingen** · 08-29-2005, 10:03 AM

Thanks for the tip!

Sounds like an array formula using a named list.

I am not familiar with list names, but I am going to see what I can find and try to make this work for me.

Thanks for the help!!

Biff · 09-05-2005, 10:05 PM

Hi!

Yes, it's possible if you're meaning a V or H lookup. Sounds like you need a
HLOOKUP.

The lookup range must be EXACTLY the same on ALL of the sheets.

Create a lst of your sheet names and give that list a name. Something like
SheetList

Enter this formula using the key combo of CTRL,SHIFT,ENTER:

=HLOOKUP(A1,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A1:E1"),A1)>0,0))&"'!A1:E5"),2,0)

The lookup value is in cell A1. The lookup range on each sheet is A1:E5. The
row_index_number is 2.

Biff

"DCSwearingen" <DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com>
wrote in message
news:DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com...
>
> Is it possible to use a lookup function across multiple sheets?
>
> Example:
> A workbook contains 26 sheets. Can a function on Sheet1 look across
> sheet2 through sheet26 and if sheet9 cell A1 contains a specific value
> (only one of the sheets can contain the value) return the contents of
> sheet9 cell A2.
>
>
> --
> DCSwearingen
>
>
> ------------------------------------------------------------------------
> DCSwearingen's Profile:
> http://www.excelforum.com/member.php...o&userid=21506
> View this thread: http://www.excelforum.com/showthread...hreadid=399630
>

Biff · 09-05-2005, 11:05 PM

Hi!

Yes, it's possible if you're meaning a V or H lookup. Sounds like you need a
HLOOKUP.

The lookup range must be EXACTLY the same on ALL of the sheets.

Create a lst of your sheet names and give that list a name. Something like
SheetList

Enter this formula using the key combo of CTRL,SHIFT,ENTER:

=HLOOKUP(A1,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A1:E1"),A1)>0,0))&"'!A1:E5"),2,0)

The lookup value is in cell A1. The lookup range on each sheet is A1:E5. The
row_index_number is 2.

Biff

"DCSwearingen" <DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com>
wrote in message
news:DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com...
>
> Is it possible to use a lookup function across multiple sheets?
>
> Example:
> A workbook contains 26 sheets. Can a function on Sheet1 look across
> sheet2 through sheet26 and if sheet9 cell A1 contains a specific value
> (only one of the sheets can contain the value) return the contents of
> sheet9 cell A2.
>
>
> --
> DCSwearingen
>
>
> ------------------------------------------------------------------------
> DCSwearingen's Profile:
> http://www.excelforum.com/member.php...o&userid=21506
> View this thread: http://www.excelforum.com/showthread...hreadid=399630
>

Biff · 09-06-2005, 12:05 AM

Hi!

Yes, it's possible if you're meaning a V or H lookup. Sounds like you need a
HLOOKUP.

The lookup range must be EXACTLY the same on ALL of the sheets.

Create a lst of your sheet names and give that list a name. Something like
SheetList

Enter this formula using the key combo of CTRL,SHIFT,ENTER:

=HLOOKUP(A1,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A1:E1"),A1)>0,0))&"'!A1:E5"),2,0)

The lookup value is in cell A1. The lookup range on each sheet is A1:E5. The
row_index_number is 2.

Biff

"DCSwearingen" <DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com>
wrote in message
news:DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com...
>
> Is it possible to use a lookup function across multiple sheets?
>
> Example:
> A workbook contains 26 sheets. Can a function on Sheet1 look across
> sheet2 through sheet26 and if sheet9 cell A1 contains a specific value
> (only one of the sheets can contain the value) return the contents of
> sheet9 cell A2.
>
>
> --
> DCSwearingen
>
>
> ------------------------------------------------------------------------
> DCSwearingen's Profile:
> http://www.excelforum.com/member.php...o&userid=21506
> View this thread: http://www.excelforum.com/showthread...hreadid=399630
>

Biff · 09-06-2005, 01:05 AM

Hi!

Yes, it's possible if you're meaning a V or H lookup. Sounds like you need a
HLOOKUP.

The lookup range must be EXACTLY the same on ALL of the sheets.

Create a lst of your sheet names and give that list a name. Something like
SheetList

Enter this formula using the key combo of CTRL,SHIFT,ENTER:

=HLOOKUP(A1,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A1:E1"),A1)>0,0))&"'!A1:E5"),2,0)

The lookup value is in cell A1. The lookup range on each sheet is A1:E5. The
row_index_number is 2.

Biff

"DCSwearingen" <DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com>
wrote in message
news:DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com...
>
> Is it possible to use a lookup function across multiple sheets?
>
> Example:
> A workbook contains 26 sheets. Can a function on Sheet1 look across
> sheet2 through sheet26 and if sheet9 cell A1 contains a specific value
> (only one of the sheets can contain the value) return the contents of
> sheet9 cell A2.
>
>
> --
> DCSwearingen
>
>
> ------------------------------------------------------------------------
> DCSwearingen's Profile:
> http://www.excelforum.com/member.php...o&userid=21506
> View this thread: http://www.excelforum.com/showthread...hreadid=399630
>

Biff · 09-06-2005, 02:05 AM

Hi!

Yes, it's possible if you're meaning a V or H lookup. Sounds like you need a
HLOOKUP.

The lookup range must be EXACTLY the same on ALL of the sheets.

Create a lst of your sheet names and give that list a name. Something like
SheetList

Enter this formula using the key combo of CTRL,SHIFT,ENTER:

=HLOOKUP(A1,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A1:E1"),A1)>0,0))&"'!A1:E5"),2,0)

The lookup value is in cell A1. The lookup range on each sheet is A1:E5. The
row_index_number is 2.

Biff

"DCSwearingen" <DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com>
wrote in message
news:DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com...
>
> Is it possible to use a lookup function across multiple sheets?
>
> Example:
> A workbook contains 26 sheets. Can a function on Sheet1 look across
> sheet2 through sheet26 and if sheet9 cell A1 contains a specific value
> (only one of the sheets can contain the value) return the contents of
> sheet9 cell A2.
>
>
> --
> DCSwearingen
>
>
> ------------------------------------------------------------------------
> DCSwearingen's Profile:
> http://www.excelforum.com/member.php...o&userid=21506
> View this thread: http://www.excelforum.com/showthread...hreadid=399630
>

Biff · 09-06-2005, 03:05 AM

Hi!

Yes, it's possible if you're meaning a V or H lookup. Sounds like you need a
HLOOKUP.

The lookup range must be EXACTLY the same on ALL of the sheets.

Create a lst of your sheet names and give that list a name. Something like
SheetList

Enter this formula using the key combo of CTRL,SHIFT,ENTER:

=HLOOKUP(A1,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A1:E1"),A1)>0,0))&"'!A1:E5"),2,0)

The lookup value is in cell A1. The lookup range on each sheet is A1:E5. The
row_index_number is 2.

Biff

"DCSwearingen" <DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com>
wrote in message
news:DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com...
>
> Is it possible to use a lookup function across multiple sheets?
>
> Example:
> A workbook contains 26 sheets. Can a function on Sheet1 look across
> sheet2 through sheet26 and if sheet9 cell A1 contains a specific value
> (only one of the sheets can contain the value) return the contents of
> sheet9 cell A2.
>
>
> --
> DCSwearingen
>
>
> ------------------------------------------------------------------------
> DCSwearingen's Profile:
> http://www.excelforum.com/member.php...o&userid=21506
> View this thread: http://www.excelforum.com/showthread...hreadid=399630
>

Biff · 09-06-2005, 04:05 AM

Hi!

Yes, it's possible if you're meaning a V or H lookup. Sounds like you need a
HLOOKUP.

The lookup range must be EXACTLY the same on ALL of the sheets.

Create a lst of your sheet names and give that list a name. Something like
SheetList

Enter this formula using the key combo of CTRL,SHIFT,ENTER:

=HLOOKUP(A1,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A1:E1"),A1)>0,0))&"'!A1:E5"),2,0)

The lookup value is in cell A1. The lookup range on each sheet is A1:E5. The
row_index_number is 2.

Biff

"DCSwearingen" <DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com>
wrote in message
news:DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com...
>
> Is it possible to use a lookup function across multiple sheets?
>
> Example:
> A workbook contains 26 sheets. Can a function on Sheet1 look across
> sheet2 through sheet26 and if sheet9 cell A1 contains a specific value
> (only one of the sheets can contain the value) return the contents of
> sheet9 cell A2.
>
>
> --
> DCSwearingen
>
>
> ------------------------------------------------------------------------
> DCSwearingen's Profile:
> http://www.excelforum.com/member.php...o&userid=21506
> View this thread: http://www.excelforum.com/showthread...hreadid=399630
>

Biff · 09-06-2005, 05:05 AM

Hi!

Yes, it's possible if you're meaning a V or H lookup. Sounds like you need a
HLOOKUP.

The lookup range must be EXACTLY the same on ALL of the sheets.

Create a lst of your sheet names and give that list a name. Something like
SheetList

Enter this formula using the key combo of CTRL,SHIFT,ENTER:

=HLOOKUP(A1,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A1:E1"),A1)>0,0))&"'!A1:E5"),2,0)

The lookup value is in cell A1. The lookup range on each sheet is A1:E5. The
row_index_number is 2.

Biff

"DCSwearingen" <DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com>
wrote in message
news:DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com...
>
> Is it possible to use a lookup function across multiple sheets?
>
> Example:
> A workbook contains 26 sheets. Can a function on Sheet1 look across
> sheet2 through sheet26 and if sheet9 cell A1 contains a specific value
> (only one of the sheets can contain the value) return the contents of
> sheet9 cell A2.
>
>
> --
> DCSwearingen
>
>
> ------------------------------------------------------------------------
> DCSwearingen's Profile:
> http://www.excelforum.com/member.php...o&userid=21506
> View this thread: http://www.excelforum.com/showthread...hreadid=399630
>

Biff · 09-06-2005, 06:05 AM

Hi!

Yes, it's possible if you're meaning a V or H lookup. Sounds like you need a
HLOOKUP.

The lookup range must be EXACTLY the same on ALL of the sheets.

Create a lst of your sheet names and give that list a name. Something like
SheetList

Enter this formula using the key combo of CTRL,SHIFT,ENTER:

=HLOOKUP(A1,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A1:E1"),A1)>0,0))&"'!A1:E5"),2,0)

The lookup value is in cell A1. The lookup range on each sheet is A1:E5. The
row_index_number is 2.

Biff

"DCSwearingen" <DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com>
wrote in message
news:DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com...
>
> Is it possible to use a lookup function across multiple sheets?
>
> Example:
> A workbook contains 26 sheets. Can a function on Sheet1 look across
> sheet2 through sheet26 and if sheet9 cell A1 contains a specific value
> (only one of the sheets can contain the value) return the contents of
> sheet9 cell A2.
>
>
> --
> DCSwearingen
>
>
> ------------------------------------------------------------------------
> DCSwearingen's Profile:
> http://www.excelforum.com/member.php...o&userid=21506
> View this thread: http://www.excelforum.com/showthread...hreadid=399630
>

Biff · 09-06-2005, 07:05 AM

Hi!

Yes, it's possible if you're meaning a V or H lookup. Sounds like you need a
HLOOKUP.

The lookup range must be EXACTLY the same on ALL of the sheets.

Create a lst of your sheet names and give that list a name. Something like
SheetList

Enter this formula using the key combo of CTRL,SHIFT,ENTER:

=HLOOKUP(A1,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A1:E1"),A1)>0,0))&"'!A1:E5"),2,0)

The lookup value is in cell A1. The lookup range on each sheet is A1:E5. The
row_index_number is 2.

Biff

"DCSwearingen" <DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com>
wrote in message
news:DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com...
>
> Is it possible to use a lookup function across multiple sheets?
>
> Example:
> A workbook contains 26 sheets. Can a function on Sheet1 look across
> sheet2 through sheet26 and if sheet9 cell A1 contains a specific value
> (only one of the sheets can contain the value) return the contents of
> sheet9 cell A2.
>
>
> --
> DCSwearingen
>
>
> ------------------------------------------------------------------------
> DCSwearingen's Profile:
> http://www.excelforum.com/member.php...o&userid=21506
> View this thread: http://www.excelforum.com/showthread...hreadid=399630
>

Biff · 09-06-2005, 09:05 AM

Hi!

Yes, it's possible if you're meaning a V or H lookup. Sounds like you need a
HLOOKUP.

The lookup range must be EXACTLY the same on ALL of the sheets.

Create a lst of your sheet names and give that list a name. Something like
SheetList

Enter this formula using the key combo of CTRL,SHIFT,ENTER:

=HLOOKUP(A1,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A1:E1"),A1)>0,0))&"'!A1:E5"),2,0)

The lookup value is in cell A1. The lookup range on each sheet is A1:E5. The
row_index_number is 2.

Biff

"DCSwearingen" <DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com>
wrote in message
news:DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com...
>
> Is it possible to use a lookup function across multiple sheets?
>
> Example:
> A workbook contains 26 sheets. Can a function on Sheet1 look across
> sheet2 through sheet26 and if sheet9 cell A1 contains a specific value
> (only one of the sheets can contain the value) return the contents of
> sheet9 cell A2.
>
>
> --
> DCSwearingen
>
>
> ------------------------------------------------------------------------
> DCSwearingen's Profile:
> http://www.excelforum.com/member.php...o&userid=21506
> View this thread: http://www.excelforum.com/showthread...hreadid=399630
>

Biff · 09-06-2005, 10:05 AM

Hi!

Yes, it's possible if you're meaning a V or H lookup. Sounds like you need a
HLOOKUP.

The lookup range must be EXACTLY the same on ALL of the sheets.

Create a lst of your sheet names and give that list a name. Something like
SheetList

Enter this formula using the key combo of CTRL,SHIFT,ENTER:

=HLOOKUP(A1,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A1:E1"),A1)>0,0))&"'!A1:E5"),2,0)

The lookup value is in cell A1. The lookup range on each sheet is A1:E5. The
row_index_number is 2.

Biff

"DCSwearingen" <DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com>
wrote in message
news:DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com...
>
> Is it possible to use a lookup function across multiple sheets?
>
> Example:
> A workbook contains 26 sheets. Can a function on Sheet1 look across
> sheet2 through sheet26 and if sheet9 cell A1 contains a specific value
> (only one of the sheets can contain the value) return the contents of
> sheet9 cell A2.
>
>
> --
> DCSwearingen
>
>
> ------------------------------------------------------------------------
> DCSwearingen's Profile:
> http://www.excelforum.com/member.php...o&userid=21506
> View this thread: http://www.excelforum.com/showthread...hreadid=399630
>

Biff · 09-06-2005, 11:05 AM

Hi!

Yes, it's possible if you're meaning a V or H lookup. Sounds like you need a
HLOOKUP.

The lookup range must be EXACTLY the same on ALL of the sheets.

Create a lst of your sheet names and give that list a name. Something like
SheetList

Enter this formula using the key combo of CTRL,SHIFT,ENTER:

=HLOOKUP(A1,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A1:E1"),A1)>0,0))&"'!A1:E5"),2,0)

The lookup value is in cell A1. The lookup range on each sheet is A1:E5. The
row_index_number is 2.

Biff

"DCSwearingen" <DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com>
wrote in message
news:DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com...
>
> Is it possible to use a lookup function across multiple sheets?
>
> Example:
> A workbook contains 26 sheets. Can a function on Sheet1 look across
> sheet2 through sheet26 and if sheet9 cell A1 contains a specific value
> (only one of the sheets can contain the value) return the contents of
> sheet9 cell A2.
>
>
> --
> DCSwearingen
>
>
> ------------------------------------------------------------------------
> DCSwearingen's Profile:
> http://www.excelforum.com/member.php...o&userid=21506
> View this thread: http://www.excelforum.com/showthread...hreadid=399630
>

Biff · 09-06-2005, 12:05 PM

Hi!

Yes, it's possible if you're meaning a V or H lookup. Sounds like you need a
HLOOKUP.

The lookup range must be EXACTLY the same on ALL of the sheets.

Create a lst of your sheet names and give that list a name. Something like
SheetList

Enter this formula using the key combo of CTRL,SHIFT,ENTER:

=HLOOKUP(A1,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A1:E1"),A1)>0,0))&"'!A1:E5"),2,0)

The lookup value is in cell A1. The lookup range on each sheet is A1:E5. The
row_index_number is 2.

Biff

"DCSwearingen" <DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com>
wrote in message
news:DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com...
>
> Is it possible to use a lookup function across multiple sheets?
>
> Example:
> A workbook contains 26 sheets. Can a function on Sheet1 look across
> sheet2 through sheet26 and if sheet9 cell A1 contains a specific value
> (only one of the sheets can contain the value) return the contents of
> sheet9 cell A2.
>
>
> --
> DCSwearingen
>
>
> ------------------------------------------------------------------------
> DCSwearingen's Profile:
> http://www.excelforum.com/member.php...o&userid=21506
> View this thread: http://www.excelforum.com/showthread...hreadid=399630
>

Biff · 09-06-2005, 02:05 PM

Hi!

Yes, it's possible if you're meaning a V or H lookup. Sounds like you need a
HLOOKUP.

The lookup range must be EXACTLY the same on ALL of the sheets.

Create a lst of your sheet names and give that list a name. Something like
SheetList

Enter this formula using the key combo of CTRL,SHIFT,ENTER:

=HLOOKUP(A1,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A1:E1"),A1)>0,0))&"'!A1:E5"),2,0)

The lookup value is in cell A1. The lookup range on each sheet is A1:E5. The
row_index_number is 2.

Biff

"DCSwearingen" <DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com>
wrote in message
news:DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com...
>
> Is it possible to use a lookup function across multiple sheets?
>
> Example:
> A workbook contains 26 sheets. Can a function on Sheet1 look across
> sheet2 through sheet26 and if sheet9 cell A1 contains a specific value
> (only one of the sheets can contain the value) return the contents of
> sheet9 cell A2.
>
>
> --
> DCSwearingen
>
>
> ------------------------------------------------------------------------
> DCSwearingen's Profile:
> http://www.excelforum.com/member.php...o&userid=21506
> View this thread: http://www.excelforum.com/showthread...hreadid=399630
>

Biff · 09-06-2005, 03:05 PM

Hi!

Yes, it's possible if you're meaning a V or H lookup. Sounds like you need a
HLOOKUP.

The lookup range must be EXACTLY the same on ALL of the sheets.

Create a lst of your sheet names and give that list a name. Something like
SheetList

Enter this formula using the key combo of CTRL,SHIFT,ENTER:

=HLOOKUP(A1,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A1:E1"),A1)>0,0))&"'!A1:E5"),2,0)

The lookup value is in cell A1. The lookup range on each sheet is A1:E5. The
row_index_number is 2.

Biff

"DCSwearingen" <DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com>
wrote in message
news:DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com...
>
> Is it possible to use a lookup function across multiple sheets?
>
> Example:
> A workbook contains 26 sheets. Can a function on Sheet1 look across
> sheet2 through sheet26 and if sheet9 cell A1 contains a specific value
> (only one of the sheets can contain the value) return the contents of
> sheet9 cell A2.
>
>
> --
> DCSwearingen
>
>
> ------------------------------------------------------------------------
> DCSwearingen's Profile:
> http://www.excelforum.com/member.php...o&userid=21506
> View this thread: http://www.excelforum.com/showthread...hreadid=399630
>

Biff · 09-06-2005, 06:05 PM

Hi!

Yes, it's possible if you're meaning a V or H lookup. Sounds like you need a
HLOOKUP.

The lookup range must be EXACTLY the same on ALL of the sheets.

Create a lst of your sheet names and give that list a name. Something like
SheetList

Enter this formula using the key combo of CTRL,SHIFT,ENTER:

=HLOOKUP(A1,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A1:E1"),A1)>0,0))&"'!A1:E5"),2,0)

The lookup value is in cell A1. The lookup range on each sheet is A1:E5. The
row_index_number is 2.

Biff

"DCSwearingen" <DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com>
wrote in message
news:DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com...
>
> Is it possible to use a lookup function across multiple sheets?
>
> Example:
> A workbook contains 26 sheets. Can a function on Sheet1 look across
> sheet2 through sheet26 and if sheet9 cell A1 contains a specific value
> (only one of the sheets can contain the value) return the contents of
> sheet9 cell A2.
>
>
> --
> DCSwearingen
>
>
> ------------------------------------------------------------------------
> DCSwearingen's Profile:
> http://www.excelforum.com/member.php...o&userid=21506
> View this thread: http://www.excelforum.com/showthread...hreadid=399630
>

Biff · 09-06-2005, 07:05 PM

Hi!

Yes, it's possible if you're meaning a V or H lookup. Sounds like you need a
HLOOKUP.

The lookup range must be EXACTLY the same on ALL of the sheets.

Create a lst of your sheet names and give that list a name. Something like
SheetList

Enter this formula using the key combo of CTRL,SHIFT,ENTER:

=HLOOKUP(A1,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A1:E1"),A1)>0,0))&"'!A1:E5"),2,0)

The lookup value is in cell A1. The lookup range on each sheet is A1:E5. The
row_index_number is 2.

Biff

"DCSwearingen" <DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com>
wrote in message
news:DCSwearingen.1ue6ej_1125093940.7609@excelforum-nospam.com...
>
> Is it possible to use a lookup function across multiple sheets?
>
> Example:
> A workbook contains 26 sheets. Can a function on Sheet1 look across
> sheet2 through sheet26 and if sheet9 cell A1 contains a specific value
> (only one of the sheets can contain the value) return the contents of
> sheet9 cell A2.
>
>
> --
> DCSwearingen
>
>
> ------------------------------------------------------------------------
> DCSwearingen's Profile:
> http://www.excelforum.com/member.php...o&userid=21506
> View this thread: http://www.excelforum.com/showthread...hreadid=399630
>

Lookup across multiple worksheets

LinkBack

Thread Tools

Rate This Thread

Display

Lookup across multiple worksheets

Re: Lookup across multiple worksheets

Thanks!!

Re: Lookup across multiple worksheets

Re: Lookup across multiple worksheets

Re: Lookup across multiple worksheets

Re: Lookup across multiple worksheets

Re: Lookup across multiple worksheets

Re: Lookup across multiple worksheets

Re: Lookup across multiple worksheets

Re: Lookup across multiple worksheets

Re: Lookup across multiple worksheets

Re: Lookup across multiple worksheets

Re: Lookup across multiple worksheets

Re: Lookup across multiple worksheets

Re: Lookup across multiple worksheets

Re: Lookup across multiple worksheets

Re: Lookup across multiple worksheets

Re: Lookup across multiple worksheets

Re: Lookup across multiple worksheets

Re: Lookup across multiple worksheets

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions