Google Sheets Array Function that returns a specified location of the array in one cell-CodePudding

I'm trying to create a google sheets function where I use a array function [like UNIQUE() or FILTER()]but the array is returned into one cell and I can specify the location of the value I'm trying to obtain.

I think its analogous to a List in R where you have a list full of a number of string characters and you can return the one you want by specifying where it falls in the list.

Below is a sample of the problem I'm trying to solve.

Functions are as follows: In cell B1: =UNIQUE(A1:A12) In cell E1: =CHOOSE(C1, $B$1, $B$2, $B$3, $B$4)

I'd like all of this to happen in one cell. Is that possible? I was thinking maybe Lambda functions would be helpful here but I couldn't understand it well enough to use it in this scenario.

I have tried without success a bunch of combinations of the following functions: Lambda functions, Index(), Choose()

Thanks a ton!

CodePudding user response：

I'm not sure why you're having trouble with INDEX in this situation. Given the list in A1:A12, =INDEX(UNIQUE(A1:A12),n) where n is 1-4 will give you the item at that position in the array?

For multiple ns use MAP to supply the row values to INDEX:

=map({n1;n2...},lambda(n,index(unique(A1:A12),n)))

CodePudding user response：

Try put this into E1 in your sample sheet.

This formula do exactly what you have requested.

Pack all your formulas (UNIQUE(A1:A),CHOOSE(C1)) up into one cell with the help of LAMBDA and INDEX.

The output will change according to the numbers you enter in C1:C4.

=LAMBDA(ARRAY,INDEXES,
 LAMBDA(VAL_1,VAL_2,VAL_3,VAL_4,
 BYROW(INDEXES,LAMBDA(ROW,
  CHOOSE(ROW,VAL_1,VAL_2,VAL_3,VAL_4)
 ))
 )(INDEX(ARRAY,1),INDEX(ARRAY,2),INDEX(ARRAY,3),INDEX(ARRAY,4))
)(UNIQUE(A1:A),$C$1:$C$4)