What formula should I use to bring 2 values under the same date?

[renatamelbourne]

Hi everyone!

I'm trying to make a report to work, but logically V & H lookup on their own won't make it and I can't figure out a way to mak eit work.
So, every week im completing the "database tab" as follows:

Now, on my report, which i have in a different tab, I want to change the week ending date on A1 and A2, and when I do so, then the formula or Macro will bring the values for the week im after.
This way I can easily compare the 2 weeks.
To make it a bit tidier, I would like the rows with 0 values on both weeks to be hidden.

Anyone has any clue what should I use? I believe my main problem is that Amount and GST are happening on the same date, and therefore by merging that I removed the V/Hlookup option.

Thank yoU!

 

[afonsomira]

Hello,

Soo try this formulas:

Cell B9 - =INDEX(Sheet2!$1:$1048576,MATCH(Sheet3!$A9,Sheet2!$A:$A,0),MATCH(Sheet3!$B$1,Sheet2!$1:$1,0))
Cell C9 - =INDEX(Sheet2!$1:$1048576,MATCH(Sheet3!$A9,Sheet2!$A:$A,0),MATCH(Sheet3!$B$1,Sheet2!$1:$1,0)+1)
Cell D9 - =INDEX(Sheet2!$1:$1048576,MATCH(Sheet3!$A9,Sheet2!$A:$A,0),MATCH(Sheet3!$B$2,Sheet2!$1:$1,0))
Cell E9 - =INDEX(Sheet2!$1:$1048576,MATCH(Sheet3!$A9,Sheet2!$A:$A,0),MATCH(Sheet3!$B$2,Sheet2!$1:$1,0)+1)

Just paste the formulas on the correct cells and drag and drop down.

Ps. You have to change the Sheet name ("Sheet2") and ("Sheet3") for your sheet name's

 

[renatamelbourne]

Hello,

Soo try this formulas:

Cell B9 - =INDEX(Sheet2!$1:$1048576,MATCH(Sheet3!$A9,Sheet2!$A:$A,0),MATCH(Sheet3!$B$1,Sheet2!$1:$1,0))
Cell C9 - =INDEX(Sheet2!$1:$1048576,MATCH(Sheet3!$A9,Sheet2!$A:$A,0),MATCH(Sheet3!$B$1,Sheet2!$1:$1,0)+1)
Cell D9 - =INDEX(Sheet2!$1:$1048576,MATCH(Sheet3!$A9,Sheet2!$A:$A,0),MATCH(Sheet3!$B$2,Sheet2!$1:$1,0))
Cell E9 - =INDEX(Sheet2!$1:$1048576,MATCH(Sheet3!$A9,Sheet2!$A:$A,0),MATCH(Sheet3!$B$2,Sheet2!$1:$1,0)+1)

Just paste the formulas on the correct cells and drag and drop down.

Ps. You have to change the Sheet name ("Sheet2") and ("Sheet3") for your sheet name's

Hi! Thank you Alfonso!

Unfortunately is not working, it shows "N/A" and im unsure how INDEX and MATCH work so I can't figure out were the problem is,

I only have 2 tabs, one is the database which is not Sheet3 and the Report on Sheet2.

 

[renatamelbourne]

Nailed it!! I studied a bit how it works and could fix it !!! :D works beautifully! Thank you @alfonsomira