Forum Discussion
VLOOKUP help
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)
8 Replies
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.
- 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),"")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! :-)
Sorry here are the pictures
