arraysgoogle-sheetspivotvlookupgoogle-query-language
Lookups based on criteria across sheets
I am trying to extrapolate data from a from response in forms/google sheets.
On the attachment, students can select 1, 2 or 3 in any of the columns. I then need the header of the 3 selections to be looked up on the second sheet in the correct column (reducing it down to everything just in the square bracket is a bonus).
This is an example, in the real thing there'd be more students in the list.
Thanks
Solution
try:
=ARRAYFORMULA(IFNA(VLOOKUP(A2:A, QUERY(SPLIT(FLATTEN(
IF('Form responses 1'!C2:1000<>"", 'Form responses 1'!C2:1000&"×"&
REGEXEXTRACT('Form responses 1'!C1:1, "\[(.*)\]")&"×"&'Form responses 1'!B2:B1000, )), "×"),
"select Col3,max(Col2) where Col3 is not null group by Col3 pivot Col1"), {2,3,4}, 0)))
- Finding min and max in array Javascript
- Python: slicing a multi-dimensional array
- How to count all objects in an array that match a condition?
- Why `[1, 2, 3]` is not showing as an `Array(3)` in Console?
- Cannot memory-free strings from arbitrary-length array of strings
- Explode delimited string into a 2d array with a specified key in each single-element row
- Why there is no F# immutable arrays?
- How to convert two longs to a byte array = how to convert UUID to byte array?
- C++ constexpr operations on each element of a constexpr array
- is there any array reverse indexing in c like python's a[-1]?
- Displaying binary images on JSP page
- Linear search in a sorted array - Java
- Convert flat array of delimited key path strings to a multidimensional array with each path terminating at an empty array
- How to iterate through an array of pointer in c
- Is there a way to create dynamic ranges given an array of values, and counting how many belong to each range
- Given an array of N integers, return the max sum of 3 NON-adjacent elements
- Parse array of strings composed of dot.separated.keys into a multidimensional array
- Explode each string in a flat array
- Parse a string with three delimiters into an associative array of indexed arrays
- Paginate Javascript array
- Page through an array
- Sum values based on three criteria and transpose result from tabular to visual layout
- Convert a JavaScript statement to a string
- Sort an array of associative arrays by column value
- Parse a multiline string with two delimiters per line into an array of associative rows
- Convert a string with 2 delimiters into a flat associative array
- How do I Split and filter 2 arrays
- PHP Array to List only values
- How to join / combine two arrays to concatenate into one array?
- Split a string by <br> tags wrapped in newlines