Google-sheets – Arrayformula+VLOOKUP find value of non-empty reference (callback/recursion problem)

google sheetsgoogle-formsvlookup

Similar to An arrayformula to find the previous non-empty cell in another column.

I have two worksheets like this:

"" = Blank cell
() = Sheet Name

Worksheet One (Form Responses 4) is the input sheet I have values that need to be VLOOKUPed to calculate the value: 

  Key A | Key B | Value | Value | Value 
  A       A       Med      Med      Med
  A       A       ""      ""     ""
  A       A       ""       ""      "" 
  B       A       Low       Med      High

Worksheet 2 (Calculations)
Current Result: 

  Key A | Key B | Value  
  A       A       9        
  A       A       10      
  A       A       N/A       
  B       A       N/A

Expected Result:

  Key A | Key B | Value
  A       A       9        
  A       A       9       
  A       A       9        
  B       A       10

Third worksheet for as an index for the VLOOKUPs:

Worksheet Three (Data Inputs)

   Value | Word 
      1   LLow
      2   Low
      3   Med
      4   MedHi
      5   High

In worksheet 2 I have the following formula: 

==ARRAYFORMULA(IF(A3:A<>"",IF('Form Responses 4'!$B2:$B="",VLOOKUP('Form Responses 4'!B:B,'6. Data Inputs'!B44:C48,2,FALSE)+VLOOKUP('Form Responses 4'!C:C,'6. Data Inputs'!B44:C48,2,FALSE)+VLOOKUP('Form Responses 4'!D:D,'6. Data Inputs'!B51:C52,2,FALSE),VLOOKUP('Form Responses 4'!B2:B,'6. Data Inputs'!B44:C48,2,FALSE)+VLOOKUP('Form Responses 4'!C2:C,'6. Data Inputs'!B44:C48,2,FALSE)+VLOOKUP('Form Responses 4'!D2:D,'6. Data Inputs'!B51:C52,2,FALSE)),""))

I am trying to reference all the non-blank rows that have the same key and value using and reference back the values. I am using VLOOKUPs in my sheet to reference words from the Google Forms to values. Currently I get the following Values: 

9
10 
N/A

I am unsure how to a perform a callback to reference all the rows that have the same key and are not blank. In my Google Forms I am trying to have them skip a section of the questionnaire if it was previously answered so it will be blank, but unable to get it to call back the initial reference using arrayformula.

Can someone help?

Best Answer

Formula

=ArrayFormula({ 'Form Responses 4'!A2:B5, MMULT( VLOOKUP( VLOOKUP( ROW(2:5), {IF(LEN('Form Responses 4'!C2:C5)>0,ROW(2:5),""),'Form Responses 4'!C2:E5},{2,3,4}), {'Data Inputs'!B2:B6,'Data Inputs'!A2:A6}, 2, 0), TRANSPOSE(SIGN(COLUMN('Form Responses 4'!C1:E1)))) })

Input (Form Responses 4)

   |    A    |   B   |   C   |   D   |   E   |   
---+------------------------------------------  
 1 | Key A    Key B   Value   Value   Value
 2 | A        A       Med     Med     Med
 3 | A        A         
 4 | A        A         
 5 | B        A       Low     Med     High

Word/Value table (Data Input)

   |   A   |   B   |
---+----------------
 1 | Value   Word 
 2 |      1  LLow
 3 |      2  Low
 4 |      3  Med
 5 |      4  MedHi
 6 |      5  High

Result (Calculations)

The column titles are manually entered / copy/pasted. The data below them is calculated; the formula is placed on A2.

   |    A    |   B   |    C    |   
---+---------------------------  
 1 | Key A    Key B   Row Total
 2 | A        A               9
 3 | A        A               9
 4 | A        A               9
 5 | B        A              10

Explanation

In order to keep the complexity at the lowest level that I thinked that is possible for a one formula solution, I avoided to use open references. Once you understand it, you could decide to refine it or to try other thing.

The above formula includes

  1. The solution from my answer to An arrayformula to find the previous non-empty cell in another column to fill-up the blanks,
  2. A VLOOKUP to replace text by their values, taken from the Data Inputs spreadsheet
  3. MMULT to make row sums
  4. Arrays to put things together.

If you add more rows to the Form Respondes 4 worksheet, then you should update the following references by replacing the 5 on each appearance of them by the number of the last row to include.

A2:B5
2:5
C2:E5
Related Topic