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?