How to use ARRAYFORMULA + FILTER within Google Spreadsheet?

Question

I have a Google spreadsheet with basically a dictionary of key/value pairs. Keys are unique. In a second sheet I need to add one or more rows for every key in the dictionary with some data. In this very sheet I'd need to also report the proper value for each key.

To do so I have tried to combine the ARRAYFORMULA function with a number of other functions on the very first row of the second sheet to avoid (if possible) the need to copy the same formula on all rows of the value column.

So far, neither QUERY nor FILTER worked.

QUERY requires all data, there included the arguments to the WHERE predicate, to reside on a single sheet. In my case, the filtering key would be on the second sheet while the dictionary is on the first one. So this cannot be used at all.

FILTER seems to have a weird (to me) behavior when both used in conjunction with ARRAYFORMULA and without it.

You can have a look to my test Google Sheet here or to snapshots here with ARRAYFORMULA (column B), without it (column C) and what I'd like to get (column D):

A little step further from pnuts' solution provides the "perfect" result without the "N/A" cells:

=ARRAYFORMULA(IF(A3:A="";"";VLOOKUP(A3:A;KEYS!A1:B;2;FALSE)))

Of course there is a major impact on the performances as the VLOOKUP is run once for every single line in in the second sheet (and this was also why I was trying to use FILTER). Those performances are quite low even with the currently linked example sheet, which is really skinny.

Answer 1

In Row3 please try:

=ArrayFormula(vlookup(A3:A;KEYS!A$1:B$5;2;0))

Answer 2

you can use a From spreadsheet - On change event trigger to call code like below:

function CopyPasteWastageRows() {
    var spreadsheet = SpreadsheetApp.getActive().getSheetByName("<<Sheet-Name>>");
    spreadsheet.getRange('Q2').activate();
    var currentCell = spreadsheet.getCurrentCell();
    spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
    currentCell.activateAsCurrentCell();
    spreadsheet.getRange('Q2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
    spreadsheet.getRange('Q2').activate();
};