Dynamically offset data of multiple rows to match the header column in Google Sheets-CodePudding

I'm trying to dynamically offset data of multiple rows to match the header column in Google Sheets. The first tab contains data of multiple fruits and how many are harvested on a particular day. Each fruit starts harvesting on different dates, and the dates might not be continuous.

The second tab, "Fruit bank", shows how many fruits are harvested in total for each day. Column D is a continuous set of dates. In cell E1, a QUERY formula dynamically pulls the names of the fruits so whenever a new fruit is added, it shows up here as well. In cell E2 and the rest of the row, I use VLOOKUP formulas to pull the data from the first tab. What I need help with is to write a formula on cell E2 that expands to the rest of the row so I don't have to manually type in the lookup range every time a new fruit is added.

Also, I suspect there're better functions to use than the VLOOKUP because the way VLOOKUP pulls data is very slow. I could literally see it loading even with this small dataset.

Yellow cells contain formula.

I appreciate anyone who can take a look at my spreadsheet (linked below) and see what's the best solution for this. The 3rd tab is editable.

CodePudding user response：

try this with dates:

=ARRAYFORMULA(QUERY({
 FLATTEN(FILTER('Fruits data'!A2:100,  ISODD(COLUMN('Fruits data'!A2:2)))), 
 FLATTEN(FILTER('Fruits data'!A2:100, ISEVEN(COLUMN('Fruits data'!A2:2)))), 
 FLATTEN(IF(FILTER('Fruits data'!A2:100, ISEVEN(COLUMN('Fruits data'!A2:2)))="",,
 FILTER('Fruits data'!A1:1, ISEVEN(COLUMN('Fruits data'!A2:2)))))}, 
 "select Col1,sum(Col2) where Col2 is not null group by Col1 pivot Col3"))

which could be simplified:

=ARRAYFORMULA(LAMBDA(x, QUERY({
 FLATTEN(FILTER(x,  ISODD(COLUMN(X)))), 
 FLATTEN(FILTER(x, ISEVEN(COLUMN(x)))), 
 FLATTEN(IF(FILTER(x, ISEVEN(COLUMN(x)))="",,
 FILTER(OFFSET(x, -1,,1), ISEVEN(COLUMN(x)))))}, 
 "select Col1,sum(Col2) where Col2 is not null group by Col1 
  pivot Col3 label Col1'Date'"))('Fruits data'!A2:100))