VLookup with multiple values

[CurtR]

I am trying to match up account numbers based on a map number where the map number could have more than one account number.
Vlookup works great where there is one account number for each map number, but when it gets to a map number with more than one account it assigns the same value ( normally the first in the column) to all the like map numbers.

Lookup data (based on map#) Returns this

Map Acct Map Acct
068.05-1-3 123 068.05-1-3 123
069.12-2-3 254 069.12-2-3 254
069.12-2-3 255 069.12-2-3 254


Is there anyway of getting excel to understand what I want it to do rather than what it is suppose to do ?
Thanks
Curt

 

[bkpchs237]

CurtR,

You haven't provided enough information for assistance here. The vlookup formula will work if you use it in combination (first and second columns) to accomplish your task (i.e. =vlookup(a1&b1,range,1,false), but some modifications would need to be made to your file first. Or there may be other methods more suitable. What exactly are you trying to do? A little more detail would help.

Hope this helps.

 

[LeKirk]

Like bkpchs237, I'm not sure exactly what you're trying to do and how you've arranged your formulae but VLOOKUP will do exactly what you say depending on whether you've sorted your data, sorted it up, sorted it down or quite frankly my dear, don't give a darn which way it is sorted. It will also depend upon whether you've told VLOOKUP to look for an exact match or nearest value.

You might be better off using a formula called MATCH which will run down your list of, presumably, map numbers and every time it finds a MATCH, will give it a number depending upon it's relative position in the list. If it doesn't find a match it returns a #N/A. You could then use an IF statement that said, IF I find a number in cell B1, print the corresponding number in cell C1 else, leave it blank.

 

[CurtR]

Sorry for the confusion,
Column A contains tax map numbers and Column B contains Water account numbers. A tax map can contain more than one water account ( ie: appartment complex) So the same tax map number may be repeated several times in the column depending on how many water accounts the property has.The list is not sorted and there in may lie the problem. When I use
Vlookup(A1,Range,2,False) When a multiple tax map number is encountered the water account number assigned to the first tax map number in the column is repeated for each like tax map number, rather than reporting each different water account number.
I will try "Match" and also try and sort the data to see what happens.

Thanks

 

[rmikesmith]

CurtR,

How are you using the Water account values that are to be returned? Dumping into cells on the worksheet or do you just need to see a list?

Regards,
Mike

 

[CurtR]

I am creating a list on a seperate worksheet that shows Tax Map Number,and each corrisponding- Account Number,Water Consumption for Each Account,and the Name and Address for each account.
This list is then used to create a text string for each account that is fed into a mainframe to generate the yearly bills.
Lots of Fun!

 

[rmikesmith]

CurtR,

I have put together a demo workbook that does what I think you are looking for. It should point you in the right direction. Post your email address and I'll be happy to forward it to you.

Regards,
Mike

 

[CurtR]

Thanks
croberts@monroecounty.gov

I'll report back
Have a Good Weekend
Curt

 

[mudstuffin]

Mike,

I have a similar predicament.

Could you send me a copy of the demo workbook, as it sounds like it might solve my problem too.

themudstuffin@yahoo.com

Many Thanks,


mudstuffin.

 

[mudstuffin]

Curt,

Thanks for sending the files. I'll have a look later and see how I do...

Thanks again,


mudstuffin.