Results 1 to 9 of 9

VLookup all occurrences of lookup value

This is a discussion on VLookup all occurrences of lookup value within the Excel Questions forums, part of the Question Forums category; Hi, So I know that with VLookup, if your Lookup Value occurs more than once in a column, it will ...

  1. #1
    New Member
    Join Date
    Sep 2010
    Posts
    45

    Default VLookup all occurrences of lookup value

    Hi,

    So I know that with VLookup, if your Lookup Value occurs more than once in a column, it will only return the value of the first one found. Is there a way around that, or another formula I can use, without having to figure out loops and macros?

    Let me explain what I want to do:

    I have a list off all accounts in one sheet. Then I have a list off all the day's transactions in another. I want to use the account number to first return the type of transaction (e.g Purchase) made by the account holder, then the value of the transaction.

    I do this in two column next to each other, using the same VLookup Formula, except of course for the Column Index Number. It works fine, but my problem is that some people do more than one transaction a day.

    So, for instance Account Number 1234 might make a payment of $100 dollars at 9am, then make a purchase of $500 at 9:05am. At the moment I obviously only get returned the first transaction. What I would ideally like to do is to concatenate the transaction type (e.g Payment/Purchase) and the amount ($100/$500). Actually that is not ideal either, because it would make later analysis even harder, but I have no other ideas and I don't have the time or brains to learn loops and programming.

    Any ideas? This has to happen on a daily basis, each time using the same account numbers, but a new Transaction list.

  2. #2
    Board Regular sulakvea's Avatar
    Join Date
    Jul 2008
    Posts
    994

    Default Re: VLookup all occurrences of lookup value

    hi, welcome to the board. do u need to concatenate? could you add the two numbers? so instead of having something like $100,$500 in one cell, you would have $600. is that an option?

  3. #3
    Board Regular
    Join Date
    Jul 2008
    Location
    Surrey, UK
    Posts
    1,473

    Default Re: VLookup all occurrences of lookup value

    In your later analysis, do you need individual transactions or the total ? If the total then look at Pivot tables. thanks

    Kaps
    Read my Excel blog on

    http://simplyspreadsheets.wordpress.com/

    For more ways I can help you with Excel :-

    www.simplyspreadsheets.co.uk

  4. #4
    New Member
    Join Date
    Sep 2010
    Posts
    45

    Default Re: VLookup all occurrences of lookup value

    Thanks for the quick responce.

    Concatenating is not the ideal solution for me, neither is adding the amounts. If both transactions are purchases then adding them would be fine, but more ofthen than not it would be a payment and then a purchase, or a purchase then a purchase reversal. So for different transaction types, I need the values separately.

    Also, I wan't the whole process to be as automated as possible. I have to do this daily for the next month (we are tracking account fluctuations before and after a text message campaign), so ideally I don't want to go to each day's transaction list and look for duplicate account numbers first and then do them differently to the others.

    So in truth, I'm not really sure what to do about the multiple transactions by one customer.

    If there were only one transaction, it would look like this:

    account number Transaction Type Amount
    xxxxxxxxxxxxxxx yyyyyyyyyyyyyy zzzzzzzz

    with the date in a merged cell above the type and the amount.

    Maybe if it could add two extra columns (Type2 and Amount2, etc) for multiple transactions?

    i know this is a hard one without using a macro or loop of some kind, I just have no experience with those. But if that's the only way I'll try it.

  5. #5
    Board Regular
    Join Date
    Oct 2003
    Posts
    1,319

    Default Re: VLookup all occurrences of lookup value

    Some time ago I had to do something similar wherein I needed multiple return values from vlookup. Finally got it all working and it involves VLOOKUP, INDEX, SMALL and ROW. Here's a link that explains it better than I ever could. A little confusing at first, but stay with it, once you get it working it's great!

    http://office.microsoft.com/en-us/ex...001226038.aspx

  6. #6
    Board Regular sulakvea's Avatar
    Join Date
    Jul 2008
    Posts
    994

    Default Re: VLookup all occurrences of lookup value

    could you upload a sample of the file, also indicate what the potential maximum size of the file could be? if the list is reasonably large, you dont wanna be storing too many formulas in it - VBA would be better. if it's no more than couple hundred, or 1-2 thousand, rows long - then you could get away with the formulas.
    Work: XP, Xls 2003. Home: Win 7, Xls 2010

  7. #7
    New Member
    Join Date
    Sep 2010
    Posts
    45

    Default Re: VLookup all occurrences of lookup value

    sulakvea: The file is enormous, with about 50 000 rows. Then I also import as new sheets the 60 daily transaction extracts (30 before the Text was sent, 30 after), from which I get the data I need for the VLookupSo the file might eventually be as large as 100mb. Here is a sample:


    https://docs.google.com/leaf?id=0By_...ut=list&num=50

    doofusboy: Thanks for that link, it looks promising, but I will need some time to see if I can make that work somehow. The files I use are very large, and I need it to be in an understandable format, which can later be used for further analysis. But I'll get back to you, maybe I can make that work, thanks.

  8. #8
    New Member
    Join Date
    Sep 2010
    Posts
    45

    Default Re: VLookup all occurrences of lookup value

    Sorry if that upload doesn't work - I almost always find what I'm looking for in a previous post, so this is my first time actually having to ask a question.
    The link for that document should be accessable by anyone, but let me know if there is an easier way of uploading files, I'm sure I'm missing it.

  9. #9
    New Member
    Join Date
    Jul 2012
    Posts
    1

    Default Re: VLookup all occurrences of lookup value

    Quote Originally Posted by doofusboy View Post
    Some time ago I had to do something similar wherein I needed multiple return values from vlookup. Finally got it all working and it involves VLOOKUP, INDEX, SMALL and ROW. Here's a link that explains it better than I ever could. A little confusing at first, but stay with it, once you get it working it's great!

    http://office.microsoft.com/en-us/ex...001226038.aspx

    Just getting acquainted with these types of formulas, not good at trouble shooting or adjusting. How can I do this except have the additional return values post in new columns instead of rows? Thanks for any assistance!

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com