• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to look up data with 3 criteria??

B

BK69007

New Member
I am trying to populate chart data for a specific Fiscal Year and Month based on a specific data table. Fiscal year is on one row followed by the month underneath while the data

[pre]
Code: 
FY12 FY12 FY13 ""
JAN  FEB  MAR  ""
Data 1
Data 2
Data 3
""
[/pre]

I am wating to be able to populate the data by Fiscal Year and then by month. I have tried the VLOOKUP with MATCH

"=VLOOKUP(valData1,Datatbl,MATCH(valFiscalYear,1stFiscalYear,0)+MATCH(valMonth,1stMonth,0)+1,FALSE)"


When I enter FY12 & JAN I recieve the corect data. However, when I select FY13 & JAN it returns FY13 NOV data. I am guessing its due to my FY being OCT-SEP.


How do I fix this?
 
SUMPRODUCT is a pretty awesome way to do this. There are a LOT of posts around this approach. Search in the google search under SUMPRODUCT. Chandoo even has some excellent blog posts and examples!


I'm learning more and more SUMPRODUCT is my best friend a lot of times!
 
Hi BK69007,


3G is correct. You can use SUMPRODUCT() but I think you have missed one thing in your post and that is the original table that you will be looking in. If you could provide that/or sample workbook you can be given an exact solution.


Regards,
 
HI BK69007,


Upload file to any free file hosting service and paste its downloadable link here.


Regards,
 
Hi Bk69007,


Ok, Leave that upload issue, assume your data is in A1:D10 like below:

[pre]
Code: 
FY12	Jan	10	200
FY12	Feb	20	500
FY12	Mar	25	600
FY12	Apr	36	250
FY12	May	12	230
FY13	Jan	25	211
FY13	Feb	30	260
FY13	Mar	32	289
FY13	Apr	12	789
FY13	May	36	963

Years and months are present in F1:O2 like below:

[code]FY12	FY12	FY12	FY12	FY12	FY13	FY13	FY13	FY13	FY13
Jan	Feb	Mar	Apr	May	Jan	Feb	Mar	Apr	May[/pre]
Enter this formula in F3, Press Ctrl+Shift+Enter and drag to "Right" Side


=INDEX($C$1:$C$10,MATCH(F$1&F$2,$A$1:$A$10&$B$1:$B$10,0),0)


Enter this formula in F4, Press Ctrl+Shift+Enter and drag to "Right" Side


=INDEX($D$1:$D$10,MATCH(F$1&F$2,$A$1:$A$10&$B$1:$B$10,0),0)[/code]


Hopefully that helps,


Regards,
 
http://www.filedropper.com/exampledata


Here is the data sheet I using, with the formula I am using I am coming up with incorrect numbers for FY 13 data.
 
Hi Bk69007,


I suggest you to replace the following in your table in row 22 with following:


Code: 
JAN-12	FEB-12	MAR-12	APR-12	MAY-12	JUN-12	JUL-12	AUG-12	SEP-12


...and so on. This change will make formulas really simple, Now you can use following:


For Planes in E8, enter and drag down:

[code]=SUMPRODUCT(($B$22:$V$22=$G$3&"-"&RIGHT($G$2,2))*($A$23:$A$34=A8)*($B$23:$V$34))


For Trains in F8, enter and drag down:

=SUMPRODUCT(($B$22:$V$22=$G$3&"-"&RIGHT($G$2,2))*($A$38:$A$49=A8)*($B$38:$V$49))


For automobiles in G8, enter and drag down:

=SUMPRODUCT(($B$22:$V$22=$G$3&"-"&RIGHT($G$2,2))*($A$54:$A$65=A8)*($B$54:$V$65))[/code]


...But that change is essential to execute these formula.


PS: When upload a sample file plz make sure that other donot have to login when they try to download it.


Regards,
 
I will remember that I was un aware the first site would ask for all that. I have tried using the format and formula you provided; However, on my spreadsheet it results in no values. Probably user error lol. Regarding the changes, is there any way of populating the correct data from the original format? I only ask becasue all the data I recieve is in that format and I would like to drag and drop updated spreadsheets with having to do the least amount of changes as possible. Thanks for all you help.
 
Hi ,


Can you try this ?


=VLOOKUP($A8,OFFSET($1:$1,MATCH(B$7,$A:$A,0),,1000),MATCH(Fiscal_Year,$21:$21,0)-1+MATCH(_Month,OFFSET($A$22,,MATCH(Fiscal_Year,$21:$21,0)-1):$IV$22,0),FALSE)


Fiscal_Year refers to G2 , while _Month refers to G3.


Please note that the Fiscal Year should follow a standard format , either FY 12 and FY 13 or FY12 and FY13 ; mixing up the two ways of specifying the fiscal year will create problems.


Narayan
 
You must log in or register to reply here.
Back
Top