A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Vlookup from different sheets



 
 
Thread Tools Display Modes
  #1  
Old May 31st, 2010, 07:16 AM posted to microsoft.public.excel.worksheet.functions
Radhakant Panigrahi
external usenet poster
 
Posts: 43
Default 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
  #2  
Old May 31st, 2010, 08:19 AM posted to microsoft.public.excel.worksheet.functions
L. Howard Kittle
external usenet poster
 
Posts: 516
Default Vlookup from different sheets

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" wrote in message
...
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



  #3  
Old May 31st, 2010, 01:38 PM posted to microsoft.public.excel.worksheet.functions
Radhakant Panigrahi
external usenet poster
 
Posts: 43
Default Vlookup from different sheets

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" wrote in message
...
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



.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:54 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.