vlookup
 

Is there a way to use a vlookup to look across multiple tabs within an excel
file?

Example
Tab 1 contains sales data from salesmen in State A

Salesman Sales
1 100
2 200
3 500

Tab 2 contains sales data from salesmen in State B

Salesman Sales
5 1000
6 600
7 800

From another tab, I would like to be able to type in a salesman's number and
get their sales, regardless of which tab they're on, without having to first
combine the data onto one sheet.

* The data is in the exact same place on each tab. (ie. Salesman 1 and
Salesman 5 are each in Cell A2 on their respective tabs)

Try...

=VLOOKUP(A1,INDIRECT("'"&INDEX({"Sheet1","Sheet2"} ,MATCH(TRUE,COUNTIF(IND
IRECT("'"&{"Sheet1","Sheet2"}&"'!A2:A4"),A1)0,0)) &"'!A2:B4"),2,0)

OR

=VLOOKUP(A1,INDIRECT("'"&INDEX(B1:B2,MATCH(TRUE,CO UNTIF(INDIRECT("'"&B1:B
2&"'!A2:A4"),A1)0,0))&"'!A2:B4"),2,0)

....confirmed with CONTROL+SHIFT+ENTER, where A1 contains the salesman of
interest, and B1:B2 contains your sheet names. Adjust the ranges
accordingly.

Hope this helps!

In article ,
"TPratt" wrote:

Is there a way to use a vlookup to look across multiple tabs within an excel
file?

Example
Tab 1 contains sales data from salesmen in State A

Salesman Sales
1 100
2 200
3 500

Tab 2 contains sales data from salesmen in State B

Salesman Sales
5 1000
6 600
7 800

From another tab, I would like to be able to type in a salesman's number and
get their sales, regardless of which tab they're on, without having to first
combine the data onto one sheet.

* The data is in the exact same place on each tab. (ie. Salesman 1 and
Salesman 5 are each in Cell A2 on their respective tabs)

Hi!

Answered this earlier in .Functions without any details included in the OP.

After seeing what the data looks like perhaps something a little less
complicated is in order.

List the sheet names in a range, say H1:H10.

Normally entered:

=SUMPRODUCT((SUMIF(INDIRECT("'"&H1:H10&"'!A2:A10") ,A1,INDIRECT("'"&H1:H10&"'!B2:B10"))))

Biff

"Domenic" wrote in message
...
Try...

=VLOOKUP(A1,INDIRECT("'"&INDEX({"Sheet1","Sheet2"} ,MATCH(TRUE,COUNTIF(IND
IRECT("'"&{"Sheet1","Sheet2"}&"'!A2:A4"),A1)0,0)) &"'!A2:B4"),2,0)

OR

=VLOOKUP(A1,INDIRECT("'"&INDEX(B1:B2,MATCH(TRUE,CO UNTIF(INDIRECT("'"&B1:B
2&"'!A2:A4"),A1)0,0))&"'!A2:B4"),2,0)

...confirmed with CONTROL+SHIFT+ENTER, where A1 contains the salesman of
interest, and B1:B2 contains your sheet names. Adjust the ranges
accordingly.

Hope this helps!

In article ,
"TPratt" wrote:

Is there a way to use a vlookup to look across multiple tabs within an
excel
file?

Example
Tab 1 contains sales data from salesmen in State A

Salesman Sales
1 100
2 200
3 500

Tab 2 contains sales data from salesmen in State B

Salesman Sales
5 1000
6 600
7 800

From another tab, I would like to be able to type in a salesman's number
and
get their sales, regardless of which tab they're on, without having to
first
combine the data onto one sheet.

* The data is in the exact same place on each tab. (ie. Salesman 1 and
Salesman 5 are each in Cell A2 on their respective tabs)

Hi Biff!

Yes, definitely. No need to complicate things. But it does take all
the fun out of it! :)

In article ,
"Biff" wrote:

Hi!

Answered this earlier in .Functions without any details included in the OP.

After seeing what the data looks like perhaps something a little less
complicated is in order.

List the sheet names in a range, say H1:H10.

Normally entered:

=SUMPRODUCT((SUMIF(INDIRECT("'"&H1:H10&"'!A2:A10") ,A1,INDIRECT("'"&H1:H10&"'!B
2:B10"))))

Biff

"Domenic" wrote in message
...
Try...

=VLOOKUP(A1,INDIRECT("'"&INDEX({"Sheet1","Sheet2"} ,MATCH(TRUE,COUNTIF(IND
IRECT("'"&{"Sheet1","Sheet2"}&"'!A2:A4"),A1)0,0)) &"'!A2:B4"),2,0)

OR

=VLOOKUP(A1,INDIRECT("'"&INDEX(B1:B2,MATCH(TRUE,CO UNTIF(INDIRECT("'"&B1:B
2&"'!A2:A4"),A1)0,0))&"'!A2:B4"),2,0)

...confirmed with CONTROL+SHIFT+ENTER, where A1 contains the salesman of
interest, and B1:B2 contains your sheet names. Adjust the ranges
accordingly.

Hope this helps!

In article ,
"TPratt" wrote:

Is there a way to use a vlookup to look across multiple tabs within an
excel
file?

Example
Tab 1 contains sales data from salesmen in State A

Salesman Sales
1 100
2 200
3 500

Tab 2 contains sales data from salesmen in State B

Salesman Sales
5 1000
6 600
7 800

From another tab, I would like to be able to type in a salesman's number
and
get their sales, regardless of which tab they're on, without having to
first
combine the data onto one sheet.

* The data is in the exact same place on each tab. (ie. Salesman 1 and
Salesman 5 are each in Cell A2 on their respective tabs)