Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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) |
#2
|
|||
|
|||
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) |
#3
|
|||
|
|||
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) |
#4
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Function with VLOOKUP | Excel Worksheet Functions | |||
make a vlookup using a variable path | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
VLOOKUP not working | Excel Worksheet Functions |