SOLVED

How to pull out dynamically values from a day and previous day from table simultaneously

%3CLINGO-SUB%20id%3D%22lingo-sub-2150263%22%20slang%3D%22en-US%22%3EHow%20to%20pull%20out%20dynamically%20values%20from%20a%20day%20and%20previous%20day%20from%20table%20simultaneously%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2150263%22%20slang%3D%22en-US%22%3EHello%2C%3CBR%20%2F%3EI%20need%20assistance%20for%20this.%3CBR%20%2F%3EI%20created%20a%20dashboard%20to%20track%20power%20and%20energy%20Consumption%20with%20daily%2C%20weekly%20and%20yearly%20summary.%20However%20i%20have%20one%20challenge%20to%20achieve%20my%20final%20result%20as%20shown%20below%20which%20i%20need%20your%20assistance%3B%3CBR%20%2F%3E%3CBR%20%2F%3EI%20can%20select%20from%20a%20year%2C%20month%20and%20day%20in%20that%20month%20to%20display%20the%20values%20of%20parameters%20for%20that%20selection%3B%3CBR%20%2F%3E%3CBR%20%2F%3EMy%20major%20problem%20is%20this%3B%3CBR%20%2F%3E%3CBR%20%2F%3EI%20want%20when%20i%20select%20any%20day%20in%20a%20particular%20month%20and%20year%2C%20example%20in%20the%20attached%2C%20I%20selected%3CBR%20%2F%3E%3CBR%20%2F%3EYear%3A%202018%3CBR%20%2F%3E%3CBR%20%2F%3EMonth%3A%20May%3CBR%20%2F%3E%3CBR%20%2F%3EDay%3A%2026%3CBR%20%2F%3E%3CBR%20%2F%3EI%20want%20it%20to%20be%20able%20to%20display%20the%20value%20for%20Day%20%3D25%20and%20Day%20%3D26%20in%20separate%20columns%20Dynamically%20so%20that%20I%20can%20compute%20%25Change%20day-on-day%3CBR%20%2F%3E%3CBR%20%2F%3EUnfortunately%2C%20I%20couldn't%20write%20out%20the%20formulae%20to%20make%20it%20pull%20out%20the%20previous%20day%3D25%20values%20at%20the%20same%20time.%3CBR%20%2F%3E%3CBR%20%2F%3EKindly%20help%20for%20my%20project%20work%20at%20office%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3ESee%20attached%20Image%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2150263%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2150420%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20pull%20out%20dynamically%20values%20from%20a%20day%20and%20previous%20day%20from%20table%20simultaneously%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2150420%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F972677%22%20target%3D%22_blank%22%3E%40mayowa360%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20E9%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIFERROR(INDEX(INDIRECT(%24P%2414)%2CMATCH(Sheet1!B9%2CINDIRECT(%24Q%2414)%2C0)%2CMATCH(Sheet1!%24C%246%26amp%3BSheet1!%24C%247%26amp%3BSheet1!%24C%248-1%2CINDIRECT(%24R%2414)%2C0))%2C%22-%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20down%20to%20E17%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2151959%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20pull%20out%20dynamically%20values%20from%20a%20day%20and%20previous%20day%20from%20table%20simultaneously%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2151959%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F972677%22%20target%3D%22_blank%22%3E%40mayowa360%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20are%20on%20Excel%20with%20dynamic%20arrays%2C%20that%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFERROR(INDEX(INDIRECT(%24P%2414)%2CMATCH(B%249%3AB%2417%2CINDIRECT(%24Q%2414)%2C0)%2CMATCH(%24C%246%26amp%3B%24C%247%26amp%3B%24E%248%3A%24F%248%2CINDIRECT(%24R%2414)%2C0))%2C%22-%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20453px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F256314i80156D8650881622%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Visitor
Hello,
I need assistance for this.
I created a dashboard to track power and energy Consumption with daily, weekly and yearly summary. However i have one challenge to achieve my final result as shown below which i need your assistance;

I can select from a year, month and day in that month to display the values of parameters for that selection;

My major problem is this;

I want when i select any day in a particular month and year, example in the attached, I selected

Year: 2018

Month: May

Day: 26

I want it to be able to display the value for Day =25 and Day =26 in separate columns Dynamically so that I can compute %Change day-on-day

Unfortunately, I couldn't write out the formulae to make it pull out the previous day=25 values at the same time.

Kindly help for my project work at office



See attached Image
2 Replies
best response confirmed by mayowa360 (Visitor)
Solution

@mayowa360 

In E9:

 

=IFERROR(INDEX(INDIRECT($P$14),MATCH(Sheet1!B9,INDIRECT($Q$14),0),MATCH(Sheet1!$C$6&Sheet1!$C$7&Sheet1!$C$8-1,INDIRECT($R$14),0)),"-")

 

Fill down to E17

@mayowa360 

If you are on Excel with dynamic arrays, that could be

=IFERROR(INDEX(INDIRECT($P$14),MATCH(B$9:B$17,INDIRECT($Q$14),0),MATCH($C$6&$C$7&$E$8:$F$8,INDIRECT($R$14),0)),"-")

 image.png