vlookup

  • Thread starter Thread starter TPratt
  • Start date Start date
T

TPratt

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,COUNTIF(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!
 
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
 
Hi Biff!

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