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

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

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


.