Can anybody help me with a formula to lookup 2 values simultaneously like
Account No. and Sub Account No.?

Thanks in advance.

Jaleel

Say you have your database in a range called Customers, with Surnames in Col
A, Account Nrs in Col B and Sub Account numbers in col C, and you enter the
customer name in say B10 on a form. Then in C10 enter
=VLOOKUP(B10,Customers,2,FALSE)&VLOOKUP(B10,Custom ers,3,FALSE).

If you want a space between the 2 numbers, use
=VLOOKUP(B10,Customers,2,FALSE)&" "&VLOOKUP(B10,Customers,3,FALSE)

"Jaleel" wrote:

Can anybody help me with a formula to lookup 2 values simultaneously like
Account No. and Sub Account No.?

Thanks in advance.

Jaleel

=INDEX(C1:C100,MATCH(1,(A1:A100=acc_no)*(B 1:B100=sub_acc_no),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jaleel" wrote in message
...
Can anybody help me with a formula to lookup 2 values simultaneously like
Account No. and Sub Account No.?

Thanks in advance.

Jaleel

Hi Bob,

Would this formula also give a space between?
--
smither fan


"Bob Phillips" wrote:

=INDEX(C1:C100,MATCH(1,(A1:A100=acc_no)*(B 1:B100=sub_acc_no),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jaleel" wrote in message
...
Can anybody help me with a formula to lookup 2 values simultaneously like
Account No. and Sub Account No.?

Thanks in advance.

Jaleel

Between what?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Ross" wrote in message
...
Hi Bob,

Would this formula also give a space between?
--
smither fan


"Bob Phillips" wrote:

=INDEX(C1:C100,MATCH(1,(A1:A100=acc_no)*(B 1:B100=sub_acc_no),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jaleel" wrote in message
...
Can anybody help me with a formula to lookup 2 values simultaneously
like
Account No. and Sub Account No.?

Thanks in advance.

Jaleel

Sorry...between the two sets of numbers.
--
smither fan


"Bob Phillips" wrote:

Between what?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Ross" wrote in message
...
Hi Bob,

Would this formula also give a space between?
--
smither fan


"Bob Phillips" wrote:

=INDEX(C1:C100,MATCH(1,(A1:A100=acc_no)*(B 1:B100=sub_acc_no),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jaleel" wrote in message
...
Can anybody help me with a formula to lookup 2 values simultaneously
like
Account No. and Sub Account No.?

Thanks in advance.

Jaleel

Hi,

Thanks for your help. But both the formulas did not yield the result I
wanted. I should have explained better.

I have 2 sheets, Jun-06 and Aug-06 containing Account No., Sub Account No.
and Ending Balance in 3 columns A, B and C respectively. Our Auditors want
to make a comparison of the Ending Balances of both the months. So I wished
to bring the Ending Balance of Jun-06 (the range I named as Jun) to Aug-06.
In one Account No. there will be different Sub Account Nos. The data in
Jun-06 is like this:


Account Subaccount Ending Balance
118102 A29999 122608.88
118102 LAD094 7309.25
121501 A21015 12525.3
121501 A21020 1035.6
121501 A21031 2761.9
121501 A23004 575
121501 A23006 119
121501 A23007 150
121800 B10003 755.65
121800 B10009 3081
121800 Q10001 3286.69
121811 0 20504.05
121811 A23060 81615.57
121812 A23028 2950
121812 A23032 2950
121812 A23060 151867.96
121812 A23061 76227.5

I wish to bring this Ending Balance to Column D of the Sheet Aug-06. I hope
the issue is clear now. Can anyone help?

Many thanks,

Jaleel




"Jaleel" wrote:

Can anybody help me with a formula to lookup 2 values simultaneously like
Account No. and Sub Account No.?

Thanks in advance.

Jaleel

Jaleel,

That is how I originally read it, and what I think my formula does. It needs
to be adjusted to your ranges, have you done that, and what did you get?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jaleel" wrote in message
...
Hi,

Thanks for your help. But both the formulas did not yield the result I
wanted. I should have explained better.

I have 2 sheets, Jun-06 and Aug-06 containing Account No., Sub Account No.
and Ending Balance in 3 columns A, B and C respectively. Our Auditors
want
to make a comparison of the Ending Balances of both the months. So I
wished
to bring the Ending Balance of Jun-06 (the range I named as Jun) to
Aug-06.
In one Account No. there will be different Sub Account Nos. The data in
Jun-06 is like this:


Account Subaccount Ending Balance
118102 A29999 122608.88
118102 LAD094 7309.25
121501 A21015 12525.3
121501 A21020 1035.6
121501 A21031 2761.9
121501 A23004 575
121501 A23006 119
121501 A23007 150
121800 B10003 755.65
121800 B10009 3081
121800 Q10001 3286.69
121811 0 20504.05
121811 A23060 81615.57
121812 A23028 2950
121812 A23032 2950
121812 A23060 151867.96
121812 A23061 76227.5

I wish to bring this Ending Balance to Column D of the Sheet Aug-06. I
hope
the issue is clear now. Can anyone help?

Many thanks,

Jaleel




"Jaleel" wrote:

Can anybody help me with a formula to lookup 2 values simultaneously
like
Account No. and Sub Account No.?

Thanks in advance.

Jaleel

Hi Jaleel

Bob gave you a solution and with slight amendment it will produce what
you are looking for.
Just amend Bob's formula to contain the sheet name of the data you wish
to bring across for comparison..
With the formula in cell D1 of sheet Jun-06, amend to
{=INDEX(Aug06!C1:C100,MATCH(1,(Aug-06!A1:A100=A1)*(Aug-06!B1:B100=B1),0))}

Remember to commit with Control+Shift+Enter to create an array formula.
Copy down column D as far as required

As an alternative, what I normally do with Trial Balance data of your
type is to combine all months of the year to one sheet, and use a Pivot
Table on this accumulated data.
In cell D1 of each sheet in insert a heading Month
In D2 Enter the Month Jun-06 and copy down.
Copy the whole block of data to a Master Sheet.
Repeat the above procedure for each other sheet, pasting the values
immediately below the last line of data on the master sheet. (In all
months other than the first month, do not copy the header row)
Whilst this might seem a large task, doing it each month as you drop the
TB out from accounts takes a very short time.

On the Master sheet create a dynamic range.
InsertNameDefine Name TBData Refers to
=OFFSET($A$1,0,0,COUNTA($A:$A),4)
This range will grow automatically as you add each set of months data.

Then, place your cursor in cell A1 of Master sheet and
DataPivot TablesNextin Range box type =TBdataNextLayout
Drag Account to Row area
Drag Sub account to Row area
Drag Month to Column Area
Drag Balance to Data Area (if it says count of Balance, double click
and change to Sum of balance)
OKFinish

On the newly created sheet with the Pivot Table, you will now have the
values for all months side by side for comparison, along with Sub-totals
by each main Account heading.
If you only want to compare 2 months, use the dropdown on Months to
deselect all months other than those you wish to view.

I work a lot with TB's and find that the small amount of effort in
setting this up makes life much easier for me (and auditors) when
wanting to do any comparisons or analysis.


--
Regards

Roger Govier


"Jaleel" wrote in message
...
Hi,

Thanks for your help. But both the formulas did not yield the result
I
wanted. I should have explained better.

I have 2 sheets, Jun-06 and Aug-06 containing Account No., Sub Account
No.
and Ending Balance in 3 columns A, B and C respectively. Our Auditors
want
to make a comparison of the Ending Balances of both the months. So I
wished
to bring the Ending Balance of Jun-06 (the range I named as Jun) to
Aug-06.
In one Account No. there will be different Sub Account Nos. The data
in
Jun-06 is like this:


Account Subaccount Ending Balance
118102 A29999 122608.88
118102 LAD094 7309.25
121501 A21015 12525.3
121501 A21020 1035.6
121501 A21031 2761.9
121501 A23004 575
121501 A23006 119
121501 A23007 150
121800 B10003 755.65
121800 B10009 3081
121800 Q10001 3286.69
121811 0 20504.05
121811 A23060 81615.57
121812 A23028 2950
121812 A23032 2950
121812 A23060 151867.96
121812 A23061 76227.5

I wish to bring this Ending Balance to Column D of the Sheet Aug-06.
I hope
the issue is clear now. Can anyone help?

Many thanks,

Jaleel




"Jaleel" wrote:

Can anybody help me with a formula to lookup 2 values simultaneously
like
Account No. and Sub Account No.?

Thanks in advance.

Jaleel

Hello Bob,

I feel your formula will work only if both the Trial Balances are identical
in the case of Account No. and Sub Account No at par with the rows. But it is
not so. The Sub Account No. starting with €œA€� indicates a Location Code,
which may exist in Jun-06, but may not exist in Aug-06 and vice versa. If
both the sheets are identical just a copy and paste will be enough. Thats
why I requested for a Vlookup Formula. I know how to put a Vlookup formula
for a single value, but I dont know for two values. Anyways, If you wish to
help me, can I send you the file?

Thank you very much Bob.

Regards,

Jaleel


"Bob Phillips" wrote:

Jaleel,

That is how I originally read it, and what I think my formula does. It needs
to be adjusted to your ranges, have you done that, and what did you get?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jaleel" wrote in message
...
Hi,

Thanks for your help. But both the formulas did not yield the result I
wanted. I should have explained better.

I have 2 sheets, Jun-06 and Aug-06 containing Account No., Sub Account No.
and Ending Balance in 3 columns A, B and C respectively. Our Auditors
want
to make a comparison of the Ending Balances of both the months. So I
wished
to bring the Ending Balance of Jun-06 (the range I named as Jun) to
Aug-06.
In one Account No. there will be different Sub Account Nos. The data in
Jun-06 is like this:


Account Subaccount Ending Balance
118102 A29999 122608.88
118102 LAD094 7309.25
121501 A21015 12525.3
121501 A21020 1035.6
121501 A21031 2761.9
121501 A23004 575
121501 A23006 119
121501 A23007 150
121800 B10003 755.65
121800 B10009 3081
121800 Q10001 3286.69
121811 0 20504.05
121811 A23060 81615.57
121812 A23028 2950
121812 A23032 2950
121812 A23060 151867.96
121812 A23061 76227.5

I wish to bring this Ending Balance to Column D of the Sheet Aug-06. I
hope
the issue is clear now. Can anyone help?

Many thanks,

Jaleel




"Jaleel" wrote:

Can anybody help me with a formula to lookup 2 values simultaneously
like
Account No. and Sub Account No.?

Thanks in advance.

Jaleel

Hello Roger,

Still I couldnt succeed with the formula. I already replied to Bob. Your
suggestion of doing the job with Pivot Table looks better. Let me try with
that. How can I compensate the valuable time you spent for me to write such
a long reply? Thank you very much Roger. You are so magnanimous.

Sincere regards,

Jaleel


"Roger Govier" wrote:

Hi Jaleel

Bob gave you a solution and with slight amendment it will produce what
you are looking for.
Just amend Bob's formula to contain the sheet name of the data you wish
to bring across for comparison..
With the formula in cell D1 of sheet Jun-06, amend to
{=INDEX(Aug06!C1:C100,MATCH(1,(Aug-06!A1:A100=A1)*(Aug-06!B1:B100=B1),0))}

Remember to commit with Control+Shift+Enter to create an array formula.
Copy down column D as far as required

As an alternative, what I normally do with Trial Balance data of your
type is to combine all months of the year to one sheet, and use a Pivot
Table on this accumulated data.
In cell D1 of each sheet in insert a heading Month
In D2 Enter the Month Jun-06 and copy down.
Copy the whole block of data to a Master Sheet.
Repeat the above procedure for each other sheet, pasting the values
immediately below the last line of data on the master sheet. (In all
months other than the first month, do not copy the header row)
Whilst this might seem a large task, doing it each month as you drop the
TB out from accounts takes a very short time.

On the Master sheet create a dynamic range.
InsertNameDefine Name TBData Refers to
=OFFSET($A$1,0,0,COUNTA($A:$A),4)
This range will grow automatically as you add each set of months data.

Then, place your cursor in cell A1 of Master sheet and
DataPivot TablesNextin Range box type =TBdataNextLayout
Drag Account to Row area
Drag Sub account to Row area
Drag Month to Column Area
Drag Balance to Data Area (if it says count of Balance, double click
and change to Sum of balance)
OKFinish

On the newly created sheet with the Pivot Table, you will now have the
values for all months side by side for comparison, along with Sub-totals
by each main Account heading.
If you only want to compare 2 months, use the dropdown on Months to
deselect all months other than those you wish to view.

I work a lot with TB's and find that the small amount of effort in
setting this up makes life much easier for me (and auditors) when
wanting to do any comparisons or analysis.


--
Regards

Roger Govier


"Jaleel" wrote in message
...
Hi,

Thanks for your help. But both the formulas did not yield the result
I
wanted. I should have explained better.

I have 2 sheets, Jun-06 and Aug-06 containing Account No., Sub Account
No.
and Ending Balance in 3 columns A, B and C respectively. Our Auditors
want
to make a comparison of the Ending Balances of both the months. So I
wished
to bring the Ending Balance of Jun-06 (the range I named as Jun) to
Aug-06.
In one Account No. there will be different Sub Account Nos. The data
in
Jun-06 is like this:


Account Subaccount Ending Balance
118102 A29999 122608.88
118102 LAD094 7309.25
121501 A21015 12525.3
121501 A21020 1035.6
121501 A21031 2761.9
121501 A23004 575
121501 A23006 119
121501 A23007 150
121800 B10003 755.65
121800 B10009 3081
121800 Q10001 3286.69
121811 0 20504.05
121811 A23060 81615.57
121812 A23028 2950
121812 A23032 2950
121812 A23060 151867.96
121812 A23061 76227.5

I wish to bring this Ending Balance to Column D of the Sheet Aug-06.
I hope
the issue is clear now. Can anyone help?

Many thanks,

Jaleel




"Jaleel" wrote:

Can anybody help me with a formula to lookup 2 values simultaneously
like
Account No. and Sub Account No.?

Thanks in advance.

Jaleel

Hi Jaleel

You are more than welcome.
Firstly, Bob's formula will work, regardless of the order of your data.
Unfortunately there were a couple of typo's created by me when adding
your sheet names to Bob's original formula.
Also, I had not made the ranges absolute.

Try
{=INDEX(Aug06!$C$1:$C$100,
MATCH(1,(Aug06!$A$1:$A$100=A1)*
(Aug06!$B$1:$B$100=B1),0))}

Again array entered and copied down.

Pivot Tables (I find) are a useful way to go, and further help on the
use of Pivot Tables can be found at Debra Dalgleish's site
http://www.contextures.com/tiptech.html
and scroll to the area on Pivot Tables

--
Regards

Roger Govier


"Jaleel" wrote in message
...
Hello Roger,

Still I couldn't succeed with the formula. I already replied to Bob.
Your
suggestion of doing the job with Pivot Table looks better. Let me try
with
that. How can I compensate the valuable time you spent for me to
write such
a long reply? Thank you very much Roger. You are so magnanimous.

Sincere regards,

Jaleel


"Roger Govier" wrote:

Hi Jaleel

Bob gave you a solution and with slight amendment it will produce
what
you are looking for.
Just amend Bob's formula to contain the sheet name of the data you
wish
to bring across for comparison..
With the formula in cell D1 of sheet Jun-06, amend to
{=INDEX(Aug06!C1:C100,MATCH(1,(Aug-06!A1:A100=A1)*(Aug-06!B1:B100=B1),0))}

Remember to commit with Control+Shift+Enter to create an array
formula.
Copy down column D as far as required

As an alternative, what I normally do with Trial Balance data of your
type is to combine all months of the year to one sheet, and use a
Pivot
Table on this accumulated data.
In cell D1 of each sheet in insert a heading Month
In D2 Enter the Month Jun-06 and copy down.
Copy the whole block of data to a Master Sheet.
Repeat the above procedure for each other sheet, pasting the values
immediately below the last line of data on the master sheet. (In all
months other than the first month, do not copy the header row)
Whilst this might seem a large task, doing it each month as you drop
the
TB out from accounts takes a very short time.

On the Master sheet create a dynamic range.
InsertNameDefine Name TBData Refers to
=OFFSET($A$1,0,0,COUNTA($A:$A),4)
This range will grow automatically as you add each set of months
data.

Then, place your cursor in cell A1 of Master sheet and
DataPivot TablesNextin Range box type =TBdataNextLayout
Drag Account to Row area
Drag Sub account to Row area
Drag Month to Column Area
Drag Balance to Data Area (if it says count of Balance, double click
and change to Sum of balance)
OKFinish

On the newly created sheet with the Pivot Table, you will now have
the
values for all months side by side for comparison, along with
Sub-totals
by each main Account heading.
If you only want to compare 2 months, use the dropdown on Months to
deselect all months other than those you wish to view.

I work a lot with TB's and find that the small amount of effort in
setting this up makes life much easier for me (and auditors) when
wanting to do any comparisons or analysis.


--
Regards

Roger Govier


"Jaleel" wrote in message
...
Hi,

Thanks for your help. But both the formulas did not yield the
result
I
wanted. I should have explained better.

I have 2 sheets, Jun-06 and Aug-06 containing Account No., Sub
Account
No.
and Ending Balance in 3 columns A, B and C respectively. Our
Auditors
want
to make a comparison of the Ending Balances of both the months. So
I
wished
to bring the Ending Balance of Jun-06 (the range I named as Jun) to
Aug-06.
In one Account No. there will be different Sub Account Nos. The
data
in
Jun-06 is like this:


Account Subaccount Ending Balance
118102 A29999 122608.88
118102 LAD094 7309.25
121501 A21015 12525.3
121501 A21020 1035.6
121501 A21031 2761.9
121501 A23004 575
121501 A23006 119
121501 A23007 150
121800 B10003 755.65
121800 B10009 3081
121800 Q10001 3286.69
121811 0 20504.05
121811 A23060 81615.57
121812 A23028 2950
121812 A23032 2950
121812 A23060 151867.96
121812 A23061 76227.5

I wish to bring this Ending Balance to Column D of the Sheet
Aug-06.
I hope
the issue is clear now. Can anyone help?

Many thanks,

Jaleel




"Jaleel" wrote:

Can anybody help me with a formula to lookup 2 values
simultaneously
like
Account No. and Sub Account No.?

Thanks in advance.

Jaleel

Jaleel,

Has Roger's reply sorted it or do you still have a problem?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jaleel" wrote in message
...
Hello Bob,

I feel your formula will work only if both the Trial Balances are
identical
in the case of Account No. and Sub Account No at par with the rows. But it
is
not so. The Sub Account No. starting with "A" indicates a Location Code,
which may exist in Jun-06, but may not exist in Aug-06 and vice versa. If
both the sheets are identical just a copy and paste will be enough. That'
s
why I requested for a Vlookup Formula. I know how to put a Vlookup
formula
for a single value, but I don't know for two values. Anyways, If you wish
to
help me, can I send you the file?

Thank you very much Bob.

Regards,

Jaleel


"Bob Phillips" wrote:

Jaleel,

That is how I originally read it, and what I think my formula does. It
needs
to be adjusted to your ranges, have you done that, and what did you get?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jaleel" wrote in message
...
Hi,

Thanks for your help. But both the formulas did not yield the result
I
wanted. I should have explained better.

I have 2 sheets, Jun-06 and Aug-06 containing Account No., Sub Account
No.
and Ending Balance in 3 columns A, B and C respectively. Our Auditors
want
to make a comparison of the Ending Balances of both the months. So I
wished
to bring the Ending Balance of Jun-06 (the range I named as Jun) to
Aug-06.
In one Account No. there will be different Sub Account Nos. The data
in
Jun-06 is like this:


Account Subaccount Ending Balance
118102 A29999 122608.88
118102 LAD094 7309.25
121501 A21015 12525.3
121501 A21020 1035.6
121501 A21031 2761.9
121501 A23004 575
121501 A23006 119
121501 A23007 150
121800 B10003 755.65
121800 B10009 3081
121800 Q10001 3286.69
121811 0 20504.05
121811 A23060 81615.57
121812 A23028 2950
121812 A23032 2950
121812 A23060 151867.96
121812 A23061 76227.5

I wish to bring this Ending Balance to Column D of the Sheet Aug-06.
I
hope
the issue is clear now. Can anyone help?

Many thanks,

Jaleel




"Jaleel" wrote:

Can anybody help me with a formula to lookup 2 values simultaneously
like
Account No. and Sub Account No.?

Thanks in advance.

Jaleel