0

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?

  • With Google Forms, form owners/editors are able to let responders to edit their previously submitted answers. Maybe you will save yourself from a lot of headaches if you use this feature. – Rubén May 3 '17 at 12:23
0

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
  • Thanks!!! So I kinda follow but i currently get the following error: Error Function ARRAY_ROW parameter 2 has mismatched row size. Expected: 4. Actual: 1. – Alex May 2 '17 at 21:13
  • @Alex: Thanks for let me know about the error. It's fixed now. – Rubén May 2 '17 at 21:15
  • I still get an error. Once I get it working I will need some time to digest and may ping you to help refine – Alex May 2 '17 at 21:30
  • @Alex: Check this spreadsheet – Rubén May 2 '17 at 21:34
  • @Alex: Fixed the sharing settings, (i'm using a G Suite account; the default setting is share files only with users from my organization. I always forgot this, I'm sorry) – Rubén May 2 '17 at 21:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.