Hi Team,
Question 1
I have 2 groups. GROUP 1 and GROUP 2. I would like to have my result for GROUP 1- BDCF *GROUP 2- BDCF for relevant month.
Question 2
I also set up a data model referring data validation cell which is from DimFacility as attached. I would like to have summary of expected result of all Accounts and Facilities (validated cell).
Please see attached masked data for reference.
Thank you so much for all your support.
Regards,
Aye
Hello,
One way to do this is to use INDEX & MATCH for group 1 and a second for group 2.
Example based on your sample file data.
=INDEX(C6:J15,MATCH(A30,A6:A15,0),MATCH(D29,C5:J5,0))*INDEX(C20:J26,MATCH(A30,A20:A26,0),MATCH(D29,C19:J19,0))
If your data structure is like in your sample file I strongly advice you to get your data in an Excel table in a tabular format. Makes it much easier to get needed data dynamically.
Br,
Anders
Many thanks for your reply, Anders.
ofcourse, Index Match works fine. I am really appreciated.
I will try to use Tubular format as per your adivice.
Regards,
Aye