How do I return the value corresponding to an index selection in an array?

user_15573559 0
Viewed 37

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:

How to use INDEX() inside ARRAYFORMULA()?

What is an equivalent for INDEX in ARRAYFORMULA?

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)
vlookup google-query-language google-sheets-formula flatten arrays
1 Answers

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, ), )))

enter image description here

user_56326290
Related