arraysgoogle-sheets-formulavlookupflattengoogle-query-language
How do I return the value corresponding to an index selection in an array?
I've got form users from different companies making a selection from 1 to 10 and I need to get the corresponding value in an array unique to each user's company.
Effectively, I think this needs the equivalent of INDEX() in an ARRAYFORMULA(), so I've had a look at the answers to similar questions:
While these look similar, I can't figure out how to apply the proposed formulas to my challenge - I've tried applying them but get errors I don't understand how to fix.
I've created a sample spreadsheet to demonstrate the desired results:
- Responses sheet has the form responses
- Reference sheet has the reference values
- Results sheet has the desired result (corresponding value returned based on the index number selected by the user)
Solution
try:
=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A&"×"&B2:B, TRIM(SPLIT(QUERY(IFERROR(FLATTEN(SPLIT(VLOOKUP(Reference!A2:A,
TRIM(SPLIT(FLATTEN(QUERY(TRANSPOSE(QUERY(QUERY({Responses!A2:A, Responses!B2:B&"×", Responses!A2:A&"¤"},
"select Col2,max(Col3) where Col1 <>'×' group by Col2 pivot Col1"), "offset 1", )),,9^9)), "×")), 2, ), "¤")&"×"&
FLATTEN(QUERY(TRANSPOSE({Reference!A2:A, "¤"&Reference!B2:K}),,9^9)))),
"where not Col1 starts with '×' and Col1 is not null", ), "¤")),
VLOOKUP(A2:A&"×"&B2:B, {Responses!A2:A&"×"&Responses!B2:B, Responses!C2:C+1}, 2, ), )))
- 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
- Add prefix to array elements then join into a string
- Parse square brace tagged text into an associative array
- Parse an SQL CALL statement and create an array with its parameters
- How to insert an item at the beginning of an array in PHP?
- Parse inline style declaration string to array
- How to swap the positions of Min and Max in an array?
- Combining two arrays to form a JavaScript object
- Cannot memory-free strings from arbitrary-length array of strings
- Is a char array more efficient than a char pointer in C?
- Split the comma-separated string of a specified column in each row of a multidimensional array