VLOOKUP Returning Multiple Values

[userkaf205]

Hi Everyone,


I just came across Chandoo and joined today. My name is Mike. This is a great forum and very helpful. I'm looking for a quick-fix to this data. It is 2011 and 2012 expense data with account numbers. Some account numbers occur only in 2012, some only in 2011, and some accounts have multiple netries in 2011 and only a single one in 2012 etc.


I'm going crazy trying to sort this! What I want to show is the 2011 and 2012 comparative data by account number. But when I try to use VLOOKUP I only get the one value in the case there are more than one entry for the same account. I've tried filtering, index, match etc.I want to show


[ACCOUNT No.] [All 2011 entries for that account] [All 2012 amounts for that account]


For ALL accounts going down vertically.


But I can't match up the account numbers I keep getting differences etc. Any idea how I can use VLOOKUP or other functions to combine and sort this data?


Thanks in advance,


Mike


https://www.dropbox.com/s/prpk1k6qe2hrgzm/Chandoo.xlsx

 

[bobhc]

Good day userkaf205


Try this link. The formula for the multi look up is entered as an array (ctrl+shift+enter).


https://dl.dropbox.com/u/75495784/Multi_Vlookup-1.xlsx


p.s welcome to the forum

 

[userkaf205]

Hi Bob,


Thanks but that is not what I'm looking for. I want to line up 2012 with 2011 and group them all by account number. Please refer to the file below to illustrate what I am trying to do. I tried using a vlookup in which excel takes the first 2012 account number, looks it up in the 2011 table and returns the value. Unfortunately, some accounts have multiple values and vlookup only returns one.


Thanks,


kaf


https://www.dropbox.com/s/prpk1k6qe2hrgzm/Chandoo.xlsx

 

[amrit1186]

Hi Mike


I was unable to check your file as I am in office and file sharing services are blocked here. But just wanted to suggest you can use a helper column year in the sheet and use pivot to achieve what you want.


Amritansh

 

[NARAYANK991]

Hi Mike ,


The problem you have posted is certainly genuine , but it doesn't tell the whole story.


The other problem is that there are 52 accounts in 2011 , which do not figure in 2012 ; obviously , if you just do a VLOOKUP from the accounts of 2012 into those of 2011 , you will not get the totals to match.


The only way to do this is to have a master set of account numbers , which is a consolidated list of account numbers from 2011 and 2012 , with only one entry for each account ; against each account , you can get the figures for each year , by the following formula :


=SUM(OFFSET('2011'!$A$2:$C$339,MATCH(Account_ID,'2011'!$A$2:$A$339,0)-1,,COUNTIF('2011'!$A$2:$A$339,Account_ID)))


Use a similar formula for the year 2012.


Narayan