• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple Vlookup

S

shefali

New Member
Hi Chandoo,

I have a task, I have a ledger sheet & account sheet, now i need to put all the accounts under the ledger from ledger sheet. For example account sheet i have "Aditya infrastructure", which is under sundry debtors in ledger sheet. How can i correlate this under each head with total sum of each ledger in account sheet. Please help me what formula or lookup to be used
 

Attachments

  • Question.xlsx
    14.4 KB · Views: 12
Hi Shefali,

Welcome to Chandoo.org forum.

Your question is not clear enough. Kindly, elaborate more and also give manual output as there are no balance figures on Ledger Sheet.

Regards,
 
Hi Chandoo,
Let me get specific with my question, I have three sheets in my workbook, list of ledger, raw input & trial balance.
In list of ledger sheet I have ledgers under groups in cells which are in bold. In raw input sheet, I have the same ledgers but its not showing under which group. I have to show the group under which the ledger falls in the column B of trial balance sheet. And aggregate the group value (sum of figures) in subtotal for each group(in the cells colored in yellow) which I tried doing manually searching, which was too tiring & time consuming. Could you help me with any excel formula or look-up function, to look-up for the group in list of ledger sheet for the ledgers in raw input sheet. Also tell me how can I aggregate the ledgers in one group & get the sub-total of it (say all ledgers in bank account at one place with a sub-total value. I tried to do it manually, the outcome which I want is something in Trial balance sheet. If you can help me with excel functions would be great.

Thanks
shefalika
 

Attachments

  • Question.xlsx
    108.5 KB · Views: 8
Hi Shefalika

Nice name.. :)

Lil bit long process.. so please follow each step properly.
  • In List of Ledgers sheet
    • Select C2, and
    • Go to Formula, Name Manager.
    • Create a named Range.. Bold_Cell.
    • Refers To.. =GET.CELL(20,OFFSET(INDIRECT("A1"),ROW()-1,0))
    • upload_2014-4-30_12-1-54.png
    • Now in C2.. Use formula as =Bold_Cell
    • In D2, use formula.. as.. =LOOKUP(2,1/(TRUE=$C$2:C2),$A$2:A2)
    • Drag both C2:D2, downward.
    • Half road crossed.. now in Sheet "Trial Balance", B5 use formula as..=IF(A5<>"Sub-total",VLOOKUP(A5,'List of Ledgers'!$A$2:$D$204,4,0),"")
Lets us know the feedback.

PS: BTW.. Get.CELL is a part of VBA.. so dont forget to save the file as MacroEnabledWorkbook.. if you want to re-use the same formula..
upload_2014-4-30_12-11-10.png

PS: Post EDITED.. all "" (Quotes) has been removed..
 

Attachments

  • Question.xlsm
    114.7 KB · Views: 10
Last edited:
Hi Debraj,

Thank you for your help but when I put the formula in C2 of list of ledger, the formula only appears not the result of the formula. I format the cells to General but its not reflecting the required values, please help.

Thanks
shefalika
 
Hi,
Please help me, why the values are not reflected when I put the formula in the cell. Please see the file.

Thanks
shefalika
 

Attachments

  • Book1.xlsm
    18.1 KB · Views: 4
Hi,

Thank you so much but what did you do that you got the value, could you please explain me.

Thanks
shefalika
 
Hi ,

See the definition of the named range :

Before : ="""=GET.CELL(20,OFFSET(INDIRECT(""A1""),ROW()-1,0))"""

After : =GET.CELL(20,OFFSET(INDIRECT("A1"),ROW()-1,0))

The earlier version was making the named range just a text string ; the later version was a formula which Excel could evaluate.

Narayan
 
Hi,

In my second file, I wanted to have a formula in raw input sheet to look-up for the group values from list of ledger sheet & then get the total sum of each group. Could you please help as u have given the formula for the Trial balance sheet, which should come out after applying for the formula for which I am seeking your advice.

Thanks
shefalika

I
 
Hi Shefalika

Can you please Give us some more detail..

Where you want to plot the group and subtotal and in which sheet..
Please upload a sample file file with few manual calculation.. and the calculation steps(if required)
 
Hi,
I would be more clear on my question, like in the formula you said "=IF(A5<>"Sub-total",VLOOKUP(A5,'List of Ledgers'!$A$2:$D$204,4,0),"")" I get the groups for the corresponding ledger but how will i able to get the sum of each group for all the groups. I cant give the sum formula at each group end, is there any simpler way to do it. Please help.

Thanks
shefalika
 
Hi Shefalika..

YES.. now its clear..

Now please tell me..
* How you have decided.. below three are in same group.. and you have added a Sub-Total row manually..
.
upload_2014-4-30_14-53-36.png

* If you sort the Group field.. then you can say.. these are from same group.. and add a SubTotal field at the end of each group.
upload_2014-4-30_14-52-50.png

* If you create a separate area then you also need to say below.. answers as well as..
upload_2014-4-30_14-56-5.png

So.. I am asking again..

Where you want to plot the group and subtotal and in which sheet..
 
Hi Debraj,

In my file, there is a sheet, Trial balance. The yellow colored cells should show the sub-total of each group.
Is there any formula which will calculate the sub-total of each group individually. Otherwise I have to give the sum formula at each group end which is time consuming. Please advice.

Thanks
shefalika
 
Hi Debraj,

I have sort the group field as per your second option & then given the subtotal for each group. But the no.of groups are too many, so wanted if I can make it little easier than manually giving summation formula.

Thanks
shefalika
 
* Remove all the rows contain Subtotal.
* You have already done the sort section.
* Select the range.
* Go to Data > Subtotal >
* Set At Each changes in Group
* Use Function SUM
* Add Subtotal to : Column (c)

upload_2014-4-30_15-12-15.png
 
Hi,

Can anyone help me in v-lookup function. I have two sheets as seen in my file. First sheet is trial balance & second sheet is Modified inputs-1. I want to have the values of the group sub-total from trial balance to modified inputs-1. For example, I want to have the values in Modified input-1 sheet, say in cell F6, I want to have value of C40 cell of trial balance sheet, F7 - value of D40 of trial balance sheet & for F8- value of E40 of trial balance sheet. That is to say, in modified input sheet, I want to lookup for the group-wise opening, debit & credit values from trial balance sheet. Please help.

Thanks
shefalika
 
Last edited:
Hi Shefali..

Please check the attached..

=IFERROR(INDEX('Trial Balance'!$B$9:$E$195,MATCH(LOOKUP(2,1/ISBLANK($C$4:C4),$C$5:C5)&" Total",'Trial Balance'!$B$9:$B$195,0),MATCH(D6,'Trial Balance'!$B$9:$E$9,0)),0)

PS: File has few changes.. Balance has been changed to "Opening Balance" in Trial Balance > C9
"Fixed Assets " has a extra space in Modified Input 1 > C5,
 

Attachments

  • Question-2 (1).xlsm
    37.6 KB · Views: 13
Hi Debraj,

When I am putting the formula in modified input sheet, its not showing the value. Could you please explain what changes I need to do in this sheets, like you said you changed balance to opening balance, what other changes you did.

thanks
shefalika
 
Thank you so much Debraj but all the cells are not reflecting the values, say bank accounts, etc. I think we should put the opening, Debit & credit for all groups then only the values will appear. Please correct me if I am wrong.

thanks
shefalika
 
You must log in or register to reply here.
Back
Top