Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- 470K Members
- 5,073 Online
- 568K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- Re: VLOOKUP help

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-06-2019 02:45 PM

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)

Labels:

8 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-06-2019 07:45 PM

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),"")

=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),"")

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-07-2019 06:02 AM

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! :-)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-07-2019 06:48 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-07-2019 07:06 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-07-2019 07:57 AM

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.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-07-2019 08:43 AM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-08-2019 08:01 PM

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.

Related Conversations

Stable version of Edge insider browser

HotCakeX
in
Discussions
on
10-12-2019
36.2K
Views

7 Likes

35 Replies

How to Prevent Teams from Auto-Launch

chenrylee
in
Microsoft Teams
on
06-27-2019
184K
Views

8 Likes

30 Replies

flashing a white screen while open new tab

Deleted
in
Discussions
on
10-05-2019
31.6K
Views

14 Likes

14 Replies

Security Community Webinars

Valon_Kolica
in
Security, Privacy & Compliance
on
10-22-2019
17.4K
Views

12 Likes

13 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © Microsoft