Vlookup from different sheets

hi,

I have the below data...like this i have huge data in different sheets(which 
are named date wise) and i can not consolidate them in a single sheet as the 
lines are more than 65000.The account numbers are repeting many a times in 
different sheets.

In a consolidated sheets i have the unique account numbers where i want the 
name of the account holder... for this i need to use vlookup formula from 
alomost 20-22 sheets.is there any formula whereby i can use the vlookup from 
multiple sheets.

Account 	Name
1245485	Stewart
4654546	Alex
2598545	Dean
1548777	Robert
4541112	David
1548477	Steve

rgds
radha
0
Utf
5/31/2010 6:16:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
1178 Views

Similar Articles

[PageSpeed] 35

Try this which I got from Peo Sjoblom a few years ago.  Looks kinda mean but 
I think we can get you going with it.

Since your "consolidated sheets" have unique lookup values I think this will 
work well.

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

Somewhere on the worksheet, list the names of all the "consolidated sheets" 
worksheets you want to look up.  Now select that list and in the name box 
name that list MySheets,  OR... name it whatever you want but you will need 
to substitute MySheets in the formula with your new name.

In the formula:

A1 is the lookup_value on the sheet that has the formula in it.

The A2:A200 I cannot explain but it has to be there.

A2:B200 (near the end of the formula) is the table_array (lookup table) on 
each sheet in the list you named.  Adjust to suit the true data on each 
sheet and make sure the range is the same on each lookup sheet.

Now use Array Enter to commit the formula.  Hold down the Ctrl + Shift and 
hit Enter.  CTRL SHIFT ENTER

You will get curly brackets around the formula, { }.  Don't try to add these 
yourself, let Excel do it.  If you make changes to the formula later you 
will again use array enter to commit.

Post back if you are having trouble getting it to work.

HTH
Regards,
Howard

"Radhakant Panigrahi" <rkp.gen@gmail.com> wrote in message 
news:51BE5F81-81B4-4068-8522-149AD94790EE@microsoft.com...
> hi,
>
> I have the below data...like this i have huge data in different 
> sheets(which
> are named date wise) and i can not consolidate them in a single sheet as 
> the
> lines are more than 65000.The account numbers are repeting many a times in
> different sheets.
>
> In a consolidated sheets i have the unique account numbers where i want 
> the
> name of the account holder... for this i need to use vlookup formula from
> alomost 20-22 sheets.is there any formula whereby i can use the vlookup 
> from
> multiple sheets.
>
> Account Name
> 1245485 Stewart
> 4654546 Alex
> 2598545 Dean
> 1548777 Robert
> 4541112 David
> 1548477 Steve
>
> rgds
> radha 


0
L
5/31/2010 7:19:20 AM
Hi,

I have tried and it really helped me a lot...thanks a lot



"L. Howard Kittle" wrote:

> Try this which I got from Peo Sjoblom a few years ago.  Looks kinda mean but 
> I think we can get you going with it.
> 
> Since your "consolidated sheets" have unique lookup values I think this will 
> work well.
> 
> =VLOOKUP(A1,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A1)>0),0))&"'!A2:B200"),2,0)
> 
> Somewhere on the worksheet, list the names of all the "consolidated sheets" 
> worksheets you want to look up.  Now select that list and in the name box 
> name that list MySheets,  OR... name it whatever you want but you will need 
> to substitute MySheets in the formula with your new name.
> 
> In the formula:
> 
> A1 is the lookup_value on the sheet that has the formula in it.
> 
> The A2:A200 I cannot explain but it has to be there.
> 
> A2:B200 (near the end of the formula) is the table_array (lookup table) on 
> each sheet in the list you named.  Adjust to suit the true data on each 
> sheet and make sure the range is the same on each lookup sheet.
> 
> Now use Array Enter to commit the formula.  Hold down the Ctrl + Shift and 
> hit Enter.  CTRL SHIFT ENTER
> 
> You will get curly brackets around the formula, { }.  Don't try to add these 
> yourself, let Excel do it.  If you make changes to the formula later you 
> will again use array enter to commit.
> 
> Post back if you are having trouble getting it to work.
> 
> HTH
> Regards,
> Howard
> 
> "Radhakant Panigrahi" <rkp.gen@gmail.com> wrote in message 
> news:51BE5F81-81B4-4068-8522-149AD94790EE@microsoft.com...
> > hi,
> >
> > I have the below data...like this i have huge data in different 
> > sheets(which
> > are named date wise) and i can not consolidate them in a single sheet as 
> > the
> > lines are more than 65000.The account numbers are repeting many a times in
> > different sheets.
> >
> > In a consolidated sheets i have the unique account numbers where i want 
> > the
> > name of the account holder... for this i need to use vlookup formula from
> > alomost 20-22 sheets.is there any formula whereby i can use the vlookup 
> > from
> > multiple sheets.
> >
> > Account Name
> > 1245485 Stewart
> > 4654546 Alex
> > 2598545 Dean
> > 1548777 Robert
> > 4541112 David
> > 1548477 Steve
> >
> > rgds
> > radha 
> 
> 
> .
> 
0
Utf
5/31/2010 12:38:01 PM
Reply:

Similar Artilces:

Opening Excel Opens Too Many Sheets
Don't know what I did recently but when I open Excel, FINANCIAL MANAGER is added to the main menu at the top and several sheets/files are opened that I do not want to open. FINANCIAL MANAGER wasn't there before and I can find no way to take it off the menu. I find no "startup" folder that lists these files that I could delete the shortcuts to - I've looked in the various folders relating to Excel but not found anything that might stop this activity whenever I open Excel. Opening the shortcut I'd been using to get to Excel, it's like a macro starts off and I fin...

Subtracting lunch breaks from different shift times
I am trying to calculate the time spent by operators on the shop floor actually working on a product. I have the start times and end times but these times also include lunch breaks. Is there anyway in Excel I can actually subtract the time of the lunch break. For example if the operator works from 11:30 am to 1:30 pm I would like to subtract 30 min from it (12:00-12:30). If the operator works from 11:00 to 12:15 and then 12:30 to 2:00 pm I would like to subtract 15 min from it (12:00-12:15) and add the remaining time (12:30 -2:00). I have 1000 rows and doing this manually would take a lot of...

VLOOKUP and IF together
Hi, I trying to create a formula for this. Sheet 1 - where the formula is created Column A - name part-number Column B - name wholesaller Column C - price of that part-number in that wholesaller Sheet 2 - where info is Column A - name wholesaller Column B - name part-number Column C - price for that part-number in that wholesaller Note: the same part-number has different prices, depending on the wholesaller Sheet 1 Column A - name part-number - here is value that shall be look up for in other sheet, that I shall call Sheet 2. In the Sheet 2 the same part-number will ap...

Different formatting in a cell with a concatenated formula?
I have a concatenated formula that pulls text from 2 different cells. The 2 cells are formatted differently (i.e. 1 cell has blue text an the other cell has red text and a different font) and I want to kee these formats. However, the cell with the concatenated formula take the format of that cell. Is there anyway to do this -- andy281 ----------------------------------------------------------------------- andy2812's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1596 View this thread: http://www.excelforum.com/showthread.php?threadid=27440 You men to have th...

vlookup formula #4
This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C8FEBA.BDA26D90 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: base64 SSBoYXZlIHNvbWUgcGFydCBudW1iZXJzIHRoYXQgSSBhbSBzZWFyY2hpbmcgZm9yIGV4dGVybmFs bHkgdXNpbmcgdGhlICJGYWxzZSIgcXVhbGlmaWVyLiAgU29tZSBmaWVsZHMgcmV0dXJuIGEgdmFs dWUgb2YgIiNOL0EiICBhcyB0aGVyZSBpcyBubyB2YWx1ZS4gIENhbiBhbnlvbmUgaGVscCBtZSB3 aXRoIGEgZm9ybXVsYSB0byByZXR1cm4gYSBmaXhlZCB2YWx1ZSAoaS5lLiAxLjApIGlmIHRoZSBm b3JtdWxhIHJldHVybnMgIiNOL0EiIGFzIGEgdmFsdWU/DQoNCg0KVEhhbmtzDQoNClBhdWw= ------=_NextPart_000_0008_01C8FEBA....

sheet protection #7
why can't i protect some of my excel sheets? Is the workbook shared? Have you grouped multiple worksheets? jaci wrote: > > why can't i protect some of my excel sheets? -- Dave Peterson ...

IF and Vlookup?
Hi - is there a way to incorporate 1 more IF to this formula? I need to use this formula but add which kind of currency to it so in cell B4 they choose which kind and then I need it to go look up specific to that currency in the other tab =IF(A17="","",VLOOKUP(A17,'PN Source Code'!$A$1:$F$29,3,FALSE)) B4 (drop down list from above) USD,EURO,YEN,GBP PN Source Code tab Product Name (which is what we are looking for from A17) USD is column C EURO is Column D YEN is Column E and GBP is Column F If desired, send your file to my address below along with this...

Conditional vlookup
I have a named Range "Price" Ihave the folowing formula that works fine =IF(B3>0,VLOOKUP(B3,Price,3,FALSE),"") Except in column A I have a Manufactures name. Column B contains the part number. I want to only use vlookup on range "price" for matches to column a What is the best way to handle this? Thanks Saved from a previous post: If you want exact matches for just two columns (and return a value from a third), you could use: =index(othersheet!$c$1:$c$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100),0)) (all in one ...

2 different loan payments within 1 transaction
Hi, I'm using MS Money 2006 and I love the functionality so far, but I have recently run into this new problem. It's kind of a rare occurence, so I hope someone can help me out. I have 2 student loan accounts, one subsidized and one unsubsidized so they can't be consolidated further. Both of these loan accounts are set up separately within MS Money. The problem seems to be that only ONE combined payment (transaction) goes towards both loans at once. This one lump sum gets automatically divided at roughly 85%/15% between the loans. Now, I've split income transaction...

MultiCurrency Question
Hi: I am in a situation where a canadian based company whose Products and Product List is in Canadian Dollars wants to create a Quote in US dollars. It seems that we cannot add the products with Canadian Dollars Currency in a USD Price List when we are using a Percent method of pricing. If that is the case, how can it be handled? I cannot create 2 sets of the same Product because the exchange rate is not fixed. Typically, CRM should be calculating the currency in USD using the exchange rate that's provided, but CRM simply is refusing to allow me to add a product with a different c...

Colour code from different servers
-- I collect mail from 2 different servers and I was wondering if it is possible to have them show in the IN BOX in different colours. I know I can select different senders by colour but I want to show all senders that came from each server. Can anybody help Pete A "Pete A" <PeteA@discussions.microsoft.com> wrote in message news:EB77DCA6-A07E-47E9-AD04-62C8B6BC1193@microsoft.com... > > -- I collect mail from 2 different servers and I was wondering if it is > possible to have them show in the IN BOX in different colours. I know I > can > select different se...

Calculate difference in rows in a query
I have a query that calculates how much revenue is claimed by month per job but then I have to take the new month less the prior month. The problem is the data is in row format. I don't know how to subtract February from January, March from but don't know how. I know how to do it in Excel but not Access. Please help... Example: Order Month JTD Clm Variance 101026521 January $511,525 $0 101026521 February $511,525 $0 101029438 January $1,238 $0 101029438 February $3,713 $2,475 101033168 January $21,465 $0 101033168 February $51,460 $29,995 101034011 Janu...

How do I update links from .xls sheets to .xlms sheets
I have thousands of links to many consolidated spreadsheets created in Excel 2003. Now that I use Excel 2007, how do I convert the links without breaking them. On Apr 21, 2:06=A0pm, June <J...@discussions.microsoft.com> wrote: > I have thousands of links to many consolidated spreadsheets created in Ex= cel > 2003. =A0Now that I use Excel 2007, how do I convert the links without br= eaking > them. After making a copy of your workbook, try to search and replace ".xls" with ".xlms" Hope this helps, Chris M. It's not clear what you'v...

Sheets not getting displayed
When an excel file is opened the sheets are not getting displayed. Using windows XP. Pls help how to overcome this situation Are the sheets hidden? - - try Format, Sheets, Unhide, and unhide any required sheets listed there. Saj Francis Wrote: > When an excel file is opened the sheets are not getting displayed. > Using > windows XP. Pls help how to overcome this situation -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21059 View this thread:...

Time sheet issues
There are three questions I am preparing a time shhet. To strart with I have to prepare a blank sheet and give one sheet for each emlpoyee Here are the blank walls I am hitting 1. I want to format a cell (E2 in my time sheet) in such a way that if enter "1" it should display "January" and so on. How to do it? 2. The First cell of last row (A 34 in my of the time sheet) should have 28, 29, 30 or 31 depending the conditions of E2 and H2 which has month and year respectively. 3. The cells B4 to B34 should have days cooresponding to dates (number) in A4 to B34 They sho...

last sheet or go back
I have been trying to figure out how to go back to the last sheet that I was working on. I often navigate my spreadsheets with hyperlinks, but I haven't figured out how to go back to the sheet I was last on. Any help would be great. By the way, what is up with all of these weird posts that we have been inundated with lately? Regards, Shane > By the way, what is up with all of these weird posts that we have been > inundated with lately? Some nutter sends them out now and then - probably trying to get into the top-10 posters lists !! Pete How about a navigation bar from Debr...

I need a four sided program to print on one sheet
I need to make a four sided program, front & back to print on 8.5 x 11 sheet Okay - so what's your question? -- JoAnn Paules MVP Microsoft [Publisher] "debbie hughes" <debbie hughes@discussions.microsoft.com> wrote in message news:B9FA1ECD-6405-4690-BCA3-5AD2FE716683@microsoft.com... >I need to make a four sided program, front & back to print on 8.5 x 11 >sheet debbie hughes wrote: > I need to make a four sided program, > front & back to print on 8.5 x 11 sheet ============================== Choose one of the pre-formatted greeting ca...

I can't select cells on Excel using the touch pad, and neither the sheet not the workbook is protected
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) SOS folks, <br><br>I am in a pickle! So, as I was working on Excel, something happened because now it doesn't let me select cells with the touchpad, but only using the arrow keys. It's very strange. I went to tools to make sure that neither the sheet nor the book is protected...What can it possibly be? <br> I would be fantastic if somebody could cast some light on this technical glitch. <br><br>All the best, <br><br>Joseph Hail mates, I solved the problem by updating the software...

Error occurred while loading sheet 3011111111111111111111111111111
I get this message on one of my XL workbooks - fairly sure it's converted from XL 97. I've read all the related posts which all say that the answer is to open the file in XL 97 and do the fix. All well and good but we no longer have XL 97 - is there any other way to fix the issue and open the file? Thanks. Hi FRM If you can't open the file in 2000-2003 then send me the file and i will change the codename for you. Send the file to me private -- Regards Ron de Bruin http://www.rondebruin.nl "FMR" <fionaross99@hotmail.com> wrote in message news:c5c9f293.04080...

transporting repetitive data from another sheet
How do I transport data from another sheet in a repetitive order? For example: In Column A I would like to transport data from sheet 3, however the data in sheet 3 skips every 4th line (row 4, 8, 12, 16, etc.). I have the formula ='Sheet3'!$E4 The next row should have ='Sheet3'!$E8 etc. When I highlight a series of these cells (all typed correctly and skipping by 4's) and drag the fill tool down (cross hair at bottom right of cell) it does not fill properly. Try =INDIRECT("Sheet3!E"&ROW(A1)*4) Do not alter the A1 - this just gets serial numbers 1,2...

how do i separate cell value in four different cells? #2
Thanks for the tip but i think i should explain some more. If th amount is 250.50 (USD) it should be displayed in the last 5 cell excluding the dot(assuming that amount is no more than five digits an 50 cents). This shoud be a simple accounting operation to transfer a single cel ammount in to assignet cells to the right. If you have ideas... help pleas -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26585 ...

difference between _MAX_PATH and MAX_PATH
difference between _MAX_PATH and MAX_PATH ? The differences are historical only. One is defined in the Windows headers (such as WinDef.H), the other is part of the C Runtime Library, in StdLib.H. I believe all current, relevant versions #define them both as 260. In article <OtOOB9dGEHA.3880@TK2MSFTNGP09.phx.gbl>, jeff@aol.com says... > difference between _MAX_PATH and MAX_PATH ? ...

Converting a PDF of an Excel sheet back to Excel?
Hi Everyone, I have just been asked by my boss if it is possible to convert a PDF of an excel worksheet back into excel!I have no idea if it is possible, can anyone please help in this regard? Cheers Ash You should be able to copy and paste the values, but you definitely will lose the formulas. On Thu, 23 Sep 2004 17:31:48 -0700, "Ashley" <anonymous@discussions.microsoft.com> wrote: >Hi Everyone, > >I have just been asked by my boss if it is possible to >convert a PDF of an excel worksheet back into excel!I have >no idea if it is possible, can anyone pl...

Applications of Excel in different industries
I am working on a school project and need different Excel applications used in different industries and if possible names of companies. Thank you. Mary C. I work in the telecom industry. My department uses excel for a whole boat load of different things. From doing equipment pricing/costing, to help with calculations during engineering. One of the common functions that I use it for is to take data from disparate sources and to combine them into a workbook that others can play what-if scenarios. We take data from the AS/400 (MACPAC), mainframe (engineering files), UNIX (dumps of custome...

vlookup #23
When using the data in a cell as a reference for a vlookup how do I only consider the first three characters in that cell. ie, =VLOOKUP(A1,A1:B10,2,FALSE) but I only want to consider the first three characters in A1 Thanks, Rick =vlookup(left(a1,3),a1:b10,2,false) Although, your look up range (a1:b10) looks kind of strange to me.) Didn't you get a circular reference error when you tried this? (maybe A2:B10 or even sheet2!a1:b10???) R D S wrote: > > When using the data in a cell as a reference for a vlookup how do I only > consider the first three characters in that cell...