Re: $$Excel-Macros$$ Need Formula for Lookup Based on a Matrix of Data

2012-02-02 Thread John A. Smith
Thank you very much Rajan. John On Mon, Jan 30, 2012 at 12:44 PM, Rajan_Verma rajanverma1...@gmail.comwrote: Use this =OFFSET(INDEX(Data!A:A,MATCH(Dashboard!E3,Data!A:A,0),1),2,MATCH(Dashboard!F2,Data!$A$1:$L$1,0)-1) ** ** *From:* excel-macros@googlegroups.com [mailto:

Re: $$Excel-Macros$$ Need Formula for Lookup Based on a Matrix of Data

2012-02-02 Thread John A. Smith
Don, thanks again for your help and guidance. John On Thu, Jan 26, 2012 at 2:46 PM, dguillett1 dguille...@gmail.com wrote: The dates are sorted so the match is looking for the LAST matching date. Look in the help index for MATCH. Don Guillett SalesAid Software dguille...@gmail.com

Re: $$Excel-Macros$$ Need Formula for Lookup Based on a Matrix of Data

2012-02-02 Thread John A. Smith
Noorain, it is no wonder you are honored as most helpful so often. Congratulations and thank you for your help. John On Thu, Jan 26, 2012 at 11:22 AM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear John, You can also use in F3...

RE: $$Excel-Macros$$ Need Formula for Lookup Based on a Matrix of Data

2012-01-30 Thread Rajan_Verma
Use this =OFFSET(INDEX(Data!A:A,MATCH(Dashboard!E3,Data!A:A,0),1),2,MATCH(Dashboard!F 2,Data!$A$1:$L$1,0)-1) From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of John A. Smith Sent: Jan/Thu/2012 08:49 To: excel-macros@googlegroups.com Subject:

Re: $$Excel-Macros$$ Need Formula for Lookup Based on a Matrix of Data

2012-01-26 Thread Sam Mathai Chacko
Use in F3 and drag down and across =SUMPRODUCT((Data!$A$2:$A$37=$E3)*(Data!$B$2:$B$37=Total)*OFFSET(Data!$A$2:$A$37,0,MATCH(Dashboard!F$2,Data!$B$1:$L$1,0))) Regards, Sam Mathai Chacko On Thu, Jan 26, 2012 at 8:48 PM, John A. Smith johnasmit...@gmail.comwrote: Thank you for your valuable

Re: $$Excel-Macros$$ Need Formula for Lookup Based on a Matrix of Data

2012-01-26 Thread NOORAIN ANSARI
Dear John, You can also use in F3... =INDEX(Data!$C$2:$C$37,MATCH(1,(Data!$A$2:$A$37=E3)*(Data!$B$2:$B$37=Total),0)) with ctrl+Shift+enter See attached sheet. -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/

Re: $$Excel-Macros$$ Need Formula for Lookup Based on a Matrix of Data

2012-01-26 Thread dguillett1
Put this in E3copy acrossdown =INDEX(Data!$A$1:$L$37,MATCH($E3,Data!$A:$A),MATCH(F$2,Data!$1:$1,0)) Don Guillett SalesAid Software dguille...@gmail.com From: John A. Smith Sent: Thursday, January 26, 2012 9:18 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Need

Re: $$Excel-Macros$$ Need Formula for Lookup Based on a Matrix of Data

2012-01-26 Thread Sourabh Salgotra
plz sir explain this formula i have confusion in this. how it can take total values from data sheet. On Thu, Jan 26, 2012 at 10:19 PM, dguillett1 dguille...@gmail.com wrote: Simpler with no CSE '=INDEX(Data!$A$1:$L$37,MATCH($E3,Data!$A:$A),MATCH(F$2,Data!$1:$1,0)) Don Guillett SalesAid

Re: $$Excel-Macros$$ Need Formula for Lookup Based on a Matrix of Data

2012-01-26 Thread John A. Smith
Don, How is it picking up only the Total number? What part of the formula does that? I understand all but that... Thank you. John On Thu, Jan 26, 2012 at 11:49 AM, dguillett1 dguille...@gmail.com wrote: Simpler with no CSE

Re: $$Excel-Macros$$ Need Formula for Lookup Based on a Matrix of Data

2012-01-26 Thread dguillett1
The dates are sorted so the match is looking for the LAST matching date. Look in the help index for MATCH. Don Guillett SalesAid Software dguille...@gmail.com From: John A. Smith Sent: Thursday, January 26, 2012 11:34 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Need

Re: $$Excel-Macros$$ Need Formula for Lookup Based on a Matrix of Data

2012-01-26 Thread dguillett1
The dates are sorted so the match is looking for the LAST matching date. Look in the help index for MATCH. Don Guillett SalesAid Software dguille...@gmail.com From: Sourabh Salgotra Sent: Thursday, January 26, 2012 11:04 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Need