VLookup all occurrences of lookup value

[glaupie]

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.

 

[sulakvea]

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?

 

[Kaps_mr2]

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

Kaps

 

[glaupie]

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.

 

[doofusboy]

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/e...ultiple-corresponding-values-HA001226038.aspx

 

[sulakvea]

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.

 

[glaupie]

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...IxMzItMzkyODM1OWJiZmIy&hl=en&authkey=CK_d0LIK

https://docs.google.com/leaf?id=0By...MzkyODM1OWJiZmIy&sort=name&layout=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.

 

[glaupie]

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.

 

[dbearth]

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/e...ultiple-corresponding-values-HA001226038.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!