Index Match to Return Second To Last Non-Numerical Entry
My team has to fill in their schedules in google forms each week, and basically I'm trying to do it so if they select "Maintain," I can automatically pull their last/previously filled entry.
So far I've gotten this:
=if(D23="Maintain",(INDEX('Form Responses'!$E$2:$R,MATCH($C23,'Form Responses'!$B$2:$B)-1)),(INDEX('Form Responses'!$E$2:$R,MAX(IF('Form Responses'!$B$2:$B=C23,ROW(C:C))))))
but the "True" section isn't returning the right row (with or without the "-1").
The name "JC" is supposed to show the data from row32 in the responses sheet, and "NA" is supposed to return that of row40.
The "-1" is supposed to Index/Match the second-to-last entry with that specific name, but I can't tell if it's working correctly or not.
What am I doing wrong with the "True" portion?
Sample sheet: https://docs.google.com/spreadsheets/d/1FeYzXH3spqKqZAZWpKO0PFMdYuTjrtDoZIxlW50B-ns/edit#gid=1728521224
delete everything in E3:R range and use in E3:
=ARRAYFORMULA(IFNA(IF(D3:D="Maintain",
VLOOKUP(C3:C, QUERY(SORT('Form Responses'!B2:R, ROW('Form Responses'!B2:B), 0),
"where not Col2 = 'Maintain' and Col1 is not null", 0), COLUMN(D:Q), 0),
VLOOKUP(C3:C, SORT('Form Responses'!B2:R, ROW('Form Responses'!B2:B), 0), COLUMN(D:Q), 0))))
- Why is the spread operator needed for Math.max()?
- How to access multidimensional data from a json file?
- How to access multidimensional data with loops
- Difference between np.nan and np.NaN
- JS: Filter array of objects by max value per category
- Bash array with spaces in elements
- How to display a multidimensional array
- How to join / combine two arrays to concatenate into one array?
- How do I find a partial match from a data table to an individual cell?
- Parsing a string representation of nested arrays into an Array
- Pipe complete array-objects instead of array items one at a time?
- In Rust, is there any reason to use a `const` array vs array reference?
- How to set a Rust array length dynamically?
- Swift async Loop in Task
- How to print a bash array on the same line
- Looped process only shows the first result per iteration
- How to access a column value in a 2d array?
- Format 2d array data into a string of hyphenated consecutive day ranges based on a shared time range
- Create an array with same element repeated multiple times
- Difference between List and Array types in Kotlin
- Python list with NumPy arrays as elements
- SwiftUI Mapkit: How to reference values from an array for markers?
- How do I Merge two Arrays in VBA?
- Sum of corresponding values from different arrays of the same size with Python
- 2D array in YAML
- Dynamic multidimensional array in twig
- How to append something to an array?
- How does PHP determine the key to use if I declare array elements without offering explicit keys?
- searching an unordered integer array
- PHP JSON (decode)