Prev: Excel formula to incrementally increase a value by 1 every 365 day
Next: How does a function provide a blank cell as a result
From: Radhakant Panigrahi on 31 May 2010 02:16
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
From: L. Howard Kittle on 31 May 2010 03:19
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(a)gmail.com> wrote in message
news:51BE5F81-81B4-4068-8522-149AD94790EE(a)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


From: Radhakant Panigrahi on 31 May 2010 08:38
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(a)gmail.com> wrote in message
> news:51BE5F81-81B4-4068-8522-149AD94790EE(a)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
>
>
> .
>
 | 
Pages: 1
Prev: Excel formula to incrementally increase a value by 1 every 365 day
Next: How does a function provide a blank cell as a result