Extended VLOOKUP to match multiple entries

**Saarang84** · 10-28-2010, 08:55 AM

Hi Wizards,

Is there any simpler way by which the VLOOKUP functionality can be enhanced for the following requirement?

For example, say there are 2 columns named ID and Account #, with multiple entries of different Account # for different IDs. There may be multiple IDs and multiple account numbers.

My requirement is to match and get all the account # for all unique IDs. By using vlookup, i can get only the first account # for an ID. How can i extend the functionality of vlookup to get all related accounts for a particular ID?

Is there some other simple formula which can be used or can this be done with VBA?
I tried to write a formula using INDEX, MATCH functions in a IF condition but since there are lots of account numbers to be matched against thousands of IDs, i'm unable to extend the formula to all IDs. The workbook gets corrupted at times. Can someone help??

Sarang

**StephenR** · 10-28-2010, 09:49 AM

What about a pivot table? Post a workbook perhaps.

**Saarang84** · 10-28-2010, 10:39 AM

Hi Stephen,

Using a Pivot table won't help my cause for further analysis on this data.

The formula works fine, my only problem is that since the data is huge, the workbook takes too long to get saved or at times gets corrupted. I need a solution or i need to simplify the formula. I use Excel 2007.

Are there any simpler ways?

Sarang

**StephenR** · 10-28-2010, 10:42 AM

Without seeing your data or your formula it's very hard to say. Perhaps it could be organised more rationally, perhaps there is a better formula, perhaps ...

**StephenR** · 10-28-2010, 11:01 AM

What's wrong with what you have on the pivot sheet?

**Saarang84** · 10-28-2010, 11:57 PM

Hi Stephen,

As said earlier, using a pivot table wouldn't serve my cause for further analysis. Here's a sample mock workbook with the formula that i tried out. I need the output in this format.

Sarang

**StephenR** · 10-29-2010, 06:02 AM

Sarang, so far all you've said is that

My requirement is to match and get all the account # for all unique IDs

. A pivot table does that.

The formula in your file seems to be working so I don't know what the problem is.

VBA is always another approach.

**Saarang84** · 11-03-2010, 01:26 AM

I got hold of this function from one of the many forums on the web:

Please Login or Register  to view this content.

It works fine as i want it to but the last parameter of the above function needs to be manually changed everytime. Can that parameter be replaced by some counter function (count, countif) or something else?

Sarang