VLOOKUP help

[Kianzad]

Hello! I want to make a cell in H1, where within a certain matrix I've made, whenever the number 1 appears in row E, the cell H1 should copy the number in row C. This I know how to do by now.

However the problem is that there is multiple times where the number 1 appears, therefore I would like that the first time the number 1 appears, write it in to cell H1, when it appears next time, write it into cell H2 etc. - Is there any way I can do this? Perhaps a function I can copy down the area where I would like it to fill in? Thanks in advance!

What I have so far is: =VLOOKUP(1;B:E;2;FALSE)

[#2]

Assuming your Column Labels are in Row 1 and your data are in Rows 2:10, the formula in H2, copied down to H10, is:
=IFERROR(LOOKUP(PI(),2/(1/ROW(C$2:C$10)=MAX(INDEX(
1/ROW(C$2:C$10)*(E$2:E$10=1)*(COUNTIF(H$1:H1,C$2:C$10)=0),0))),
C$2:C$10),"")

[#3]

Thanks alot for your response, I have tried this formula and it seems to be working exactly like wanted! I have just 1 problem left, it seems to be adding a value of 0 in each of the sides I've put it. (I've changed the code a tiny bit, to make it fit my needs. I have tried attaching pictures of what it looks like, with the formula where there the number 0 appears. Hope you can help me! Thanks alot again! :)

[#4]

Sorry here are the pictures

[#5]

Also, I have one last question here - I would like to do so that the Account name automatically changes depending on what account name is referenced as 1. Therefore I tried to do: "=VLOOKUP(1;A:D;1;FALSE)" Indicating it should give me the text one the same row as the number 1, however I get an error. I believe it's because the number 1 occurs more than once, and I'm only asking for one value. Therefore I would like for it to add only the text from the first time 1 occurs, or any of the times it occurs, just only once. Is that possible?

[#6]

Incidentally, your cited scenario is my “territory”! Please attach your sample file so that I will create a template for you that returns the debits and credits to a selected account, including that account’s opening and closing balances.

[#7]

How fortunate! I appreciate you taking your time to help me. 

I have attached this document, please bear in mind that I am very new at this, however I found this project quite interesting and learning. Therefore, I do not necessarily just want an answer to my solutions by alot of random codes, but would rather be able to understand what has been done, atleast at some point.

I would like to see your way of achieving this, and if possible I would appreciate alot if you would be able to change the code for me on the third tab for my vlookup function so that it instead of saying e.g. "Credit side" it would simply skip to the next value. I assume there must be some sort of function to replace instead of writing "Credit side". (I'm not searching for a new function in the whole, just a function to replace at the point where I have written "credit side" so that it skips this value). 

I hope that you can look through my mess, I am simply trying out alot of things to learn at the moment. 

[#8]

In Accounting, an entry consists of at least 1 debit and at least 1 credit. An account cannot be debited and credited in the same entry, only the net amount is entered; otherwise, you must split your compound journal entry into simple journal entries. To avoid displaying a zero, for presentation purposes, we may instead replace it with an empty text (“”).
Succinctly stated, an account may either be debited or credited for each time that it is included in a journal entry for a specific date. I will attach the template you need within 44 hours. Right now, I am using my phone to reply to you. I will study the best strategy for you when I will have access to my desktop computer in 36 hours from now.

[#9]

In the attached file, the formulas are as follows: 

1. To assign a unique Reference to each Account, the formula starting in E2 is: 

=IF(COUNTIF(B$2:B2,B2)=1,
MAX(E$1:E1)+1,
VLOOKUP(B2,B$1:E1,4,0))

2. To create a distinct list of Accounts, sorted in ascending order, the formula starting in G2 is: 

=IFERROR(LOOKUP(PI(),
1/(COUNTIF(AccountsEntered,">="&AccountsEntered)=MAX(INDEX(
COUNTIF(AccountsEntered,">="&AccountsEntered)*(COUNTIF(G$1:G1,AccountsEntered)=0),0))),
AccountsEntered),"")

3. To create a list of sequential numbers corresponding to the number of entries to the selected Account in J3, the formula starting in I5 is: 

=IF(ROW()-4>COUNTIF(AccountsEntered,J$3),
"",
SUM(I4,1))

4. To create a list of Debit entries to the selected Account in J3, the formula starting in J5 is: 

=IFERROR(IF(LOOKUP(PI(),
2/(1/ROW(AccountsEntered)=SUMPRODUCT(LARGE(
1/ROW(AccountsEntered)*(AccountsEntered=$J$3),$I5))),
DebitsEntered)=0,"",
LOOKUP(PI(),
2/(1/ROW(AccountsEntered)=SUMPRODUCT(LARGE(
1/ROW(AccountsEntered)*(AccountsEntered=$J$3),$I5))),
DebitsEntered)),"")

5. To create a list of Credit entries to the selected Account in J3, the formula starting in K5 is: 

=IFERROR(IF(LOOKUP(PI(),
2/(1/ROW(AccountsEntered)=SUMPRODUCT(LARGE(
1/ROW(AccountsEntered)*(AccountsEntered=$J$3),$I5))),
CreditsEntered)=0,"",
LOOKUP(PI(),
2/(1/ROW(AccountsEntered)=SUMPRODUCT(LARGE(
1/ROW(AccountsEntered)*(AccountsEntered=$J$3),$I5))),
CreditsEntered)),"")

6. To calculate the balance of the selected Account in J3, the formula in K3 is: 

=ABS(SUM(DebitEntries)-SUM(CreditEntries))

Note that all named ranges are dynamic, such that you can add (or delete) entries without need to modify any of the formulas.