J
Jaleel
Can anybody help me with a formula to lookup 2 values simultaneously like
Account No. and Sub Account No.?
Thanks in advance.
Jaleel
Account No. and Sub Account No.?
Thanks in advance.
Jaleel
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
K
kassie
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,Customers,3,FALSE).
If you want a space between the 2 numbers, use
=VLOOKUP(B10,Customers,2,FALSE)&" "&VLOOKUP(B10,Customers,3,FALSE)
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,Customers,3,FALSE).
If you want a space between the 2 numbers, use
=VLOOKUP(B10,Customers,2,FALSE)&" "&VLOOKUP(B10,Customers,3,FALSE)
B
Bob Phillips
=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)
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)
R
Ross
Hi Bob,
Would this formula also give a space between?
Would this formula also give a space between?
B
Bob Phillips
Between what?
--
HTH
Bob Phillips
(replace xxxx in the email address with gmail if mailing direct)
--
HTH
Bob Phillips
(replace xxxx in the email address with gmail if mailing direct)
R
Ross
Sorry...between the two sets of numbers.
J
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
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
B
Bob Phillips
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)
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)
R
Roger Govier
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.
Insert>Name>Define> 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
Data>Pivot Tables>Next>in Range box type =TBdata>Next>Layout
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)
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.
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.
Insert>Name>Define> 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
Data>Pivot Tables>Next>in Range box type =TBdata>Next>Layout
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)
OK>Finish
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.
J
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. 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
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
J
Jaleel
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
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
R
Roger Govier
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
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
B
Bob Phillips
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)
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)