Trouble with ARRAYFORMULA for auto-filling formulas in Google Sheets

0
votes

I'm attempting to automatically populate a Google Sheets column in a "Results" sheet with the following formula, which draws data from a "Source" spreadsheet:

=IFERROR(INDEX(Source!$B$2:$D,MATCH($A2&C$1,Source!$B$2:$B&Source!$C$2:$C,0),3)," ")

When I place that formula in ARRAYFORMULA, it does not auto-populate, and changing the MATCH search_key to a range (i.e., $A2:$A&C$1) does not fix the problem.

I.e., neither of these work:

=ArrayFormula(IFERROR(INDEX(Source!$B$2:$D,MATCH($A2&C$1,Source!$B$2:$B&Source!$C$2:$C,0),3)," ")) =ArrayFormula(IFERROR(INDEX(Source!$B$2:$D,MATCH($A2:$A&C$1,Source!$B$2:$B&Source!$C$2:$C,0),3)," "))

Is there a way to fix this? Or an alternative way to automatically fill a formula? I'm also open to using a function other than INDEX/MATCH, so long as it can do a lookup based on two vertical criteria. Unfortunately I don't think a pivot table would suit my purposes, since my "Results" page also queries another spreadsheet to provide other information.

Example here: https://docs.google.com/spreadsheets/d/1dsaMYsJeZl2o_rNTLwaVhnEehFvAKRMXghAJeEK220s/edit?usp=sharing

2
arraysgoogle-sheetsgoogle-spreadsheet-api

2 Answers

1
votes

This is the formula you are looking for: 

=ARRAYFORMULA(IFERROR(HLOOKUP(Source!$D$2,Source!$D$2:$D$11,MATCH($A2:$A4&C$1,ARRAYFORMULA(Source!$B$2:$B&Source!$C$2:$C),0),0),0))

I tested this and it works perfectly fine.

TL;DR

To answer why your formula is wrong,

  1. The second argument in MATCH should have an ARRAYFORMULA otherwise you are basically matching only with first element.
  2. INDEX doesn't work with ARRAYFORMULA. It can only search for a single value in a given range.

The first part can be solved by adding ARRAYFORMULA inside MATCH.

A simple VLOOKUPcould have solved the second problem if you weren't searching for a combination of cells (Date and Name). A twisted HLOOKUP comes to your rescue.

Without the IFERROR part, the formula looks like this:

=ARRAYFORMULA(HLOOKUP(Source!$D$2,Source!$D$2:$D$11,MATCH($A2:$A4&C$1,ARRAYFORMULA(Source!$B$2:$B&Source!$C$2:$C),0),0))
0
votes

This formula produces the same output (see cell I1):

=QUERY({Source!A:D,ARRAYFORMULA(VLOOKUP(Source!B:B,'Project Lookup'!A:B,2,0))},"select Col2,Col5,sum(Col4) where Col1 is not null group by Col2,Col5 pivot Col3")