Google Sheets arrayformula for 2D lookup with most recent matches-CodePudding

See this spreadsheet (feel free to make copies of the 'Attempt' sheet and have a go): https://docs.google.com/spreadsheets/d/1PCaMLIyxC75QSYBg8qXrImAEIKmT3PMqzru_mJeGw8M/edit#gid=0

On the 'Input data' sheet is a list of building purchases, renovations and sales, with the year it happened, and the floor area of the floor afterwards.

What I want is on the 'Desired result' sheet... A table of years with a column for each building. The cell values are the floor areas for the given building in the given year. I want one formula in cell B3 that will complete the table and automatically update as the 'Input data' sheet is changed.

I have managed to make an arrayformula that in cell B3 that gives the desired values for that row, which can be copied down to give the desired values for all rows; however, what I want is one formula in cell B3 that does the whole table.

I have gotten very close with a couple of nested MMULT functions, but couldn't quite make it work. I'm guessing there's probably a tricky QUERY function, but I haven't got my head around the syntax of QUERY yet. I'm also interested if anyone can do it without QUERY.

Part of what makes it tricky is that the formula needs to find the most recent match up to the year in question (rather than finding an exact match).

Many thanks for your time!

CodePudding user response：

I've added a new sheet ("Erik Help") with the following formula:

=ArrayFormula(IFERROR(IF(VLOOKUP(B2:F2&A3:A,SORT({'Input data'!A2:A&'Input data'!B2:B,'Input data'!A2:A},1,1),2,TRUE)<>B2:F2,IF(A3:A="",,0),VLOOKUP(B2:F2&A3:A,SORT({'Input data'!A2:A&'Input data'!B2:B,'Input data'!C2:C},1,1),2,TRUE))))

Key Concepts:

Whenever you want to fill a grid like this, you typically concatenate elements (as I have done with & here), in both the search-key elements and the search-range elements.

When you need to look up "data between data" (like years between years), you can use VLOOKUP with the final parameter set to TRUE, which means fall back to the last entry if there isn't an exact match." However, in order for TRUE to work, all of the search-range entries must be in strict ascending order, hence the SORT wraps you see. However, when SORTed, some elements in your case may "fall back" to a year-company combination that isn't for that company; so the first IF clause makes sure that every "fall back" find matches by company. If not, null or 0 is returned (depending on whether Col A is occupied). If so, then the correct "fall back" result is returned.