INDEX.Nth returns (every) nth element(s) (starting at the desired position) from a one-dimensional array and provides output options and full control over data type(s) inclusion/exclusion
(The code is modular (as all my other functions) allowing for easy swapping/optimizing over time. See all the modules below in VBA wraps and in XL2BB, after the description; and download the template containing all my functions from the link below in my signature)
INDEX.Nth takes ten parameters, two required and eight optional, as follows:
A) The first parameter, lookup_1D_array, required, specifies the function input, i.e. a one-dimensional array, that is used for the lookup and can be a cell or one-dimensional range or array
B) The second parameter, nth_elements, required, specifies the positional number(s) of the input element(s) to be returned in the output and can be a single number or multiple numbers
Note that if multiple elements are sought, the numbers should be provided in array brackets "{}" (e.g. {1,2,3} or {2,1,6,8,4} or {1,2,2,2,4,3,5,5})
Note that if the one or more specified nth element numbers are all larger than the length of the lookup array, the function will return an #N/A. If, however, one or some of the numbers are larger than the lookup array, then those numbers will be automatically filtered out of the entire function's analysis and will, therefore, not affect the output in any way.
Note that the numbers can be in any order desired and/or repeated as shown in the example above
Note that if the numbers are repeated, the they will be reflected in the function output depending on which output_style is specified (see section F below)
Note that seeking multiple elements, if available, will result in a spill. The orientation of this spill (which I refer to as the primary spill) is controlled by the fifth parameter, i.e. output_orientation, as described in section E below
C) The third parameter, every, optional, specifies whether to return "every" nth element(s) (specified in the second parameter in section B above) and takes two general arguments:
0 or omitted or FALSE, for off (meaning "not every") (i.e. only the first of the nth element(s) will be returned)
1 or TRUE or any number other than 0, for on (meaning "every") [i.e. every (available) nth element(s) will be returned]
Note that this argument structure matches the Excel's native way of handling on/off arguments for parameters and is accomplished by my PLSL module shown below
Note that seeking every nth element(s), will result in the subsequent nth element(s) to have a secondary spill perpendicular to the direction of the main spill of the first set of nth element(s). This happens only when the sixth parameter, i.e. output_style, is omitted or 0. If output_style is set to 1 or 2, both the primary and secondary spills will combine one-dimensionally in the orientation of the primary spill. See section F below
D) The fourth parameter, start_at, optional, specifies the starting lookup position in the input lookup_1D_array and takes a single number (integer) as argument
Note that if omitted, (i.e. default) the function will consider the beginning element of the one-dimensional range/array as the first position to begin scanning
Note that if more than one number is entered through the array brackets, the function will return a single #N/A
Note that if the entered number is larger than the length of the lookup array, the function will return a single #N/A. For this assessment, the function automatically factors in the (specified) starting lookup position
E) The fifth parameter, output_orientation, optional, specifies the orientation of the output spill of the nth_elements and takes three arguments:
0 or omitted, for spill in the same orientation as the one-dimensional lookup range/array
1, for horizontal spill
2, for vertical spill
Note that this orientation refers to the spill of the nth elements (i.e. the primary spill) and not the perpendicular spill generated by using "every" (i.e. the secondary spill) as described in sections B and C above
F) The sixth parameter, output_style, optional, specifies the output style of the function if the "every" parameter is turned on and takes three arguments
0 or omitted, for default output showing the two-dimensional spill of nth_elements and every nth_elements, as described in sections B, C, and E above
1, for linearization of the default two-dimensional spill by stacking the primary (nth_elements) spills
2, for linearization of the default two-dimensional spill by stacking the secondary (every nth_elements) spills
Note that both arguments 1 and 2 will result in the same size of linear output; the difference is in the linearization method
Note that for both arguments 1 and 2, while linearizing the default two-dimensional spill, the function will automatically exclude any blank cells that are not part of the lookup_1D_array (i.e. the non-core blank cells) (see example in one of the attached XL2BBs where the non-core blank cells are highlighted in gray); this means that the overall size of the function output with arguments 1 or 2 may be smaller than the size of the output with argument 0 (or omitted).
Note that if "every" is not turned on, the use of this, i.e. output_style, parameter will have no effect on the function output
Note that if any number other than 0 (or omitted), 1, or 2 is used for this parameter, the function will return an #N/A
G) The seventh parameter, exclude_types, optional, specifies the type(s) of data that can be excluded from the main output and takes one or more of twenty-four arguments:
1, for number
2, for text
3, for blank
4, for logical
16, for error
31, for real blank
32, for formula blank
41, for TRUE
42, for FALSE
160, for #EXTERNAL! error
161, for #NULL! error
162, for #DIV/0! error
163, for #VALUE! error
164, for #REF! error
165, for #NAME? error
166, for #NUM! error
167, for #N/A error
168, for #GETTING_DATA error
169, for #SPILL! error
1610, for #CONNECT! error
1611, for #BLOCKED! error
1612, for #UNKNOWN! error
1613, for #FIELD! error
1614, for #CALC! error
Note that if you need to enter more than one argument, you need to use the array brackets "{}" such as {1,31} for excluding numbers and real blanks, respectively, or {16,2,4} for excluding errors, texts, and logicals, respectively; see the attached XL2BB examples (the number of types permutations is huge; I have only shown a few examples); this parameter as well as the next (include_types) allow for a remarkable level of flexibility and full resolution for managing data types in the function output
Note that excluding type(s) may cause the output spill to auto-shrink if full row(s) and/or column(s) in the original (unexcluded) spill get vacated by the exclusion. (The auto-shrink happens leftward and upward, i.e. towards the top-left corner of the output spill)
Note that the auto-shrink happens no matter which output_style is used. For output_style 0 (or omitted), the auto-shrink may happen two-dimensionally or one-dimensionally depending on the type of data in the lookup array and the excluded type(s). For output_style 1 or 2, the auto-shrink will happen one-dimensionally.
Note that the numbers included in the array brackets for excluding types don't have to be in any order, as shown in the example above
Note that the same principles specified for exclude_types apply to the include_types parameter as described below in section H
H) The eighth parameter, include_types, optional, specifies the type(s) of data that can be included in the main output and takes one or more of twenty-four arguments, exactly like the seventh parameter in section G above
Note that both exclude_types and include_types cannot be used simultaneously
Note that using include_types (unless all types are included as by {1,2,3,4,16} for example) may also cause the main output spill to auto-shrink depending on whether full row(s) or column(s) get vacated by the indirect exclusion caused by using this parameter. See section G above for additional information especially on the auto-shrink capability of the function.
I) The ninth parameter, replace_with, optional, specifies a single entry of any desired data type (i.e. number, text, blank, logical, or error) that can be used to replace any entries that were either directly excluded via the exclude_types parameter or indirectly excluded via the include_types parameter
Note that if you use the replace_with parameter by itself, i.e. without using one of exclude_types or include_types, the function will crash with a single #N/A
Note that if more than one entry is included in this parameter via array brackets "{}", the function will crash with a single #N/A
J) The tenth parameter, if_empty, optional, specifies a single entry of any desired data type (i.e. number, text, blank, logical, or error) that can be displayed as the main function output if the output is empty, i.e. #CALC! with the message "Empty arrays are not supported"
Note that if the function output contains #CALC! which comes from an existing entry in the function input, then you will see the message "This #CALC! error comes from a precedent to this formula". This implies that this particular type of output is not actually "empty", and therefore, if there is an entry in the if_empty parameter, this entry will not be shown
Note that if more than one entry is included in this parameter via array brackets "{}", the function will crash with a single #N/A
Note that the "empty" status of the output as well as the use of if_empty, if supplied, is controlled by my JAI module described below (I have adopted the JXX naming pattern for my modules and helper functions as there are the fewest number of built-in Excel functions starting with the letter J)
INDEX.Nth
XERROR.TYPE (posted separately)
XTYPE (posted separately)
JAB (returns a number greater than or equal to 1 (i.e. TRUE) if the reference has the specified data type(s) and 0 (i.e. FALSE) otherwise)
JAC (module for 1D [FILTER-based] "exclude_types/include_types/replace_with")
JAD [core for INDEX.Nth()]
JAE [INDEXer for JAD()]
JAF (module for 2D "exclude_types/include_types/replace_with" when spill includes non-core blank cells)
JAG ["output_orientation" module for INDEX.Nth()]
JAH (removes rows and columns containing non-core blank cells)
JAI (module for "if_empty")
JAL (switch for "include_types/exclude_types")
JAN [Returns TRUE if the reference is an array (i.e. is not a single element or is not omitted) and FALSE otherwise (simplified form of ISARRAY)]
JAO (Returns TRUE if the reference contains at least one error and FALSE otherwise [simplified form of ISERRORS])
JAP (Returns TRUE if the reference contains at least one blank and FALSE otherwise [simplified form of ISBLANKS])
PLS (stands for "parameter limit, single")
(Returns the parameter if it is a single entry (not blank or error) and #N/A otherwise)
(Causes the function to crash to a single #N/A if the incorrect parameter is entered [either the wrong type or more than a single entry in array brackets "{}"])
PLSL (stands for "parameter limit, single logical)
(Returns 1 if the parameter is a single TRUE entry; 0 if the parameter is a single FALSE entry or is omitted; and #N/A otherwise)
(Causes the function to crash to a single #N/A if incorrect parameters are entered [either anything not capable of being evaluated logically or more than a single entry in array brackets "{}"]))
IO (shortened form of ISOMITTED)
NIO [shortened form of NOT(ISOMITTED)]
OF (Returns "FALSE" if the reference is TRUE, "0" if the reference is FALSE, and #N/A otherwise)
OT (Returns "TRUE" if the reference is TRUE, "1" if the reference is FALSE, and #N/A otherwise)
PO0 (Returns 0 if the parameter is omitted and the parameter itself otherwise)
T1F0 (Returns 1 if the reference is TRUE, 0 if the reference is FALSE, and #N/A otherwise)
Here are all the modules in the XL2BB format (note that these are the most up-to-date versions compared with the same modules from my previous posts for other functions):
(The code is modular (as all my other functions) allowing for easy swapping/optimizing over time. See all the modules below in VBA wraps and in XL2BB, after the description; and download the template containing all my functions from the link below in my signature)
INDEX.Nth takes ten parameters, two required and eight optional, as follows:
A) The first parameter, lookup_1D_array, required, specifies the function input, i.e. a one-dimensional array, that is used for the lookup and can be a cell or one-dimensional range or array
B) The second parameter, nth_elements, required, specifies the positional number(s) of the input element(s) to be returned in the output and can be a single number or multiple numbers
Note that if multiple elements are sought, the numbers should be provided in array brackets "{}" (e.g. {1,2,3} or {2,1,6,8,4} or {1,2,2,2,4,3,5,5})
Note that if the one or more specified nth element numbers are all larger than the length of the lookup array, the function will return an #N/A. If, however, one or some of the numbers are larger than the lookup array, then those numbers will be automatically filtered out of the entire function's analysis and will, therefore, not affect the output in any way.
Note that the numbers can be in any order desired and/or repeated as shown in the example above
Note that if the numbers are repeated, the they will be reflected in the function output depending on which output_style is specified (see section F below)
Note that seeking multiple elements, if available, will result in a spill. The orientation of this spill (which I refer to as the primary spill) is controlled by the fifth parameter, i.e. output_orientation, as described in section E below
C) The third parameter, every, optional, specifies whether to return "every" nth element(s) (specified in the second parameter in section B above) and takes two general arguments:
0 or omitted or FALSE, for off (meaning "not every") (i.e. only the first of the nth element(s) will be returned)
1 or TRUE or any number other than 0, for on (meaning "every") [i.e. every (available) nth element(s) will be returned]
Note that this argument structure matches the Excel's native way of handling on/off arguments for parameters and is accomplished by my PLSL module shown below
Note that seeking every nth element(s), will result in the subsequent nth element(s) to have a secondary spill perpendicular to the direction of the main spill of the first set of nth element(s). This happens only when the sixth parameter, i.e. output_style, is omitted or 0. If output_style is set to 1 or 2, both the primary and secondary spills will combine one-dimensionally in the orientation of the primary spill. See section F below
D) The fourth parameter, start_at, optional, specifies the starting lookup position in the input lookup_1D_array and takes a single number (integer) as argument
Note that if omitted, (i.e. default) the function will consider the beginning element of the one-dimensional range/array as the first position to begin scanning
Note that if more than one number is entered through the array brackets, the function will return a single #N/A
Note that if the entered number is larger than the length of the lookup array, the function will return a single #N/A. For this assessment, the function automatically factors in the (specified) starting lookup position
E) The fifth parameter, output_orientation, optional, specifies the orientation of the output spill of the nth_elements and takes three arguments:
0 or omitted, for spill in the same orientation as the one-dimensional lookup range/array
1, for horizontal spill
2, for vertical spill
Note that this orientation refers to the spill of the nth elements (i.e. the primary spill) and not the perpendicular spill generated by using "every" (i.e. the secondary spill) as described in sections B and C above
F) The sixth parameter, output_style, optional, specifies the output style of the function if the "every" parameter is turned on and takes three arguments
0 or omitted, for default output showing the two-dimensional spill of nth_elements and every nth_elements, as described in sections B, C, and E above
1, for linearization of the default two-dimensional spill by stacking the primary (nth_elements) spills
2, for linearization of the default two-dimensional spill by stacking the secondary (every nth_elements) spills
Note that both arguments 1 and 2 will result in the same size of linear output; the difference is in the linearization method
Note that for both arguments 1 and 2, while linearizing the default two-dimensional spill, the function will automatically exclude any blank cells that are not part of the lookup_1D_array (i.e. the non-core blank cells) (see example in one of the attached XL2BBs where the non-core blank cells are highlighted in gray); this means that the overall size of the function output with arguments 1 or 2 may be smaller than the size of the output with argument 0 (or omitted).
Note that if "every" is not turned on, the use of this, i.e. output_style, parameter will have no effect on the function output
Note that if any number other than 0 (or omitted), 1, or 2 is used for this parameter, the function will return an #N/A
G) The seventh parameter, exclude_types, optional, specifies the type(s) of data that can be excluded from the main output and takes one or more of twenty-four arguments:
1, for number
2, for text
3, for blank
4, for logical
16, for error
31, for real blank
32, for formula blank
41, for TRUE
42, for FALSE
160, for #EXTERNAL! error
161, for #NULL! error
162, for #DIV/0! error
163, for #VALUE! error
164, for #REF! error
165, for #NAME? error
166, for #NUM! error
167, for #N/A error
168, for #GETTING_DATA error
169, for #SPILL! error
1610, for #CONNECT! error
1611, for #BLOCKED! error
1612, for #UNKNOWN! error
1613, for #FIELD! error
1614, for #CALC! error
Note that if you need to enter more than one argument, you need to use the array brackets "{}" such as {1,31} for excluding numbers and real blanks, respectively, or {16,2,4} for excluding errors, texts, and logicals, respectively; see the attached XL2BB examples (the number of types permutations is huge; I have only shown a few examples); this parameter as well as the next (include_types) allow for a remarkable level of flexibility and full resolution for managing data types in the function output
Note that excluding type(s) may cause the output spill to auto-shrink if full row(s) and/or column(s) in the original (unexcluded) spill get vacated by the exclusion. (The auto-shrink happens leftward and upward, i.e. towards the top-left corner of the output spill)
Note that the auto-shrink happens no matter which output_style is used. For output_style 0 (or omitted), the auto-shrink may happen two-dimensionally or one-dimensionally depending on the type of data in the lookup array and the excluded type(s). For output_style 1 or 2, the auto-shrink will happen one-dimensionally.
Note that the numbers included in the array brackets for excluding types don't have to be in any order, as shown in the example above
Note that the same principles specified for exclude_types apply to the include_types parameter as described below in section H
H) The eighth parameter, include_types, optional, specifies the type(s) of data that can be included in the main output and takes one or more of twenty-four arguments, exactly like the seventh parameter in section G above
Note that both exclude_types and include_types cannot be used simultaneously
Note that using include_types (unless all types are included as by {1,2,3,4,16} for example) may also cause the main output spill to auto-shrink depending on whether full row(s) or column(s) get vacated by the indirect exclusion caused by using this parameter. See section G above for additional information especially on the auto-shrink capability of the function.
I) The ninth parameter, replace_with, optional, specifies a single entry of any desired data type (i.e. number, text, blank, logical, or error) that can be used to replace any entries that were either directly excluded via the exclude_types parameter or indirectly excluded via the include_types parameter
Note that if you use the replace_with parameter by itself, i.e. without using one of exclude_types or include_types, the function will crash with a single #N/A
Note that if more than one entry is included in this parameter via array brackets "{}", the function will crash with a single #N/A
J) The tenth parameter, if_empty, optional, specifies a single entry of any desired data type (i.e. number, text, blank, logical, or error) that can be displayed as the main function output if the output is empty, i.e. #CALC! with the message "Empty arrays are not supported"
Note that if the function output contains #CALC! which comes from an existing entry in the function input, then you will see the message "This #CALC! error comes from a precedent to this formula". This implies that this particular type of output is not actually "empty", and therefore, if there is an entry in the if_empty parameter, this entry will not be shown
Note that if more than one entry is included in this parameter via array brackets "{}", the function will crash with a single #N/A
Note that the "empty" status of the output as well as the use of if_empty, if supplied, is controlled by my JAI module described below (I have adopted the JXX naming pattern for my modules and helper functions as there are the fewest number of built-in Excel functions starting with the letter J)
INDEX.Nth
VBA Code:
=LAMBDA(lookup_1D_array,nth_elements,[every],[start_at],[output_orientation],[output_style],[exclude_types],[include_types],[replace_with],[if_empty],
LET(
i,ROWS(lookup_1D_array),
j,COLUMNS(lookup_1D_array),
m,MAX(i,j),
e,PLSL(every),
o,PLS(PO0(output_orientation)),
s,PLS(PO0(output_style)),
f,TOCOL(IF(lookup_1D_array="","",lookup_1D_array)),
r,ROWS(f),
n,FILTER(TOROW(nth_elements),nth_elements<=r),
a,TOROW(XTYPE(lookup_1D_array,3)),
w,JAD(e,start_at,n,r,s),
x,JAE(f,w),
p,JAE(a,w),
v,IF(OR(AND(i=1,j=1),i>j),0,1),
IF(
AND(i>1,j>1),
NA(),
JAI(
JAG(
SWITCH(JAL(exclude_types,include_types,replace_with),
0,x,
1,IF(
s,
JAC(exclude_types,include_types,replace_with,x,p),
JAF(w,exclude_types,include_types,replace_with,x,p))),
v,
o),
if_empty
)
)
)
)XERROR.TYPE (posted separately)
XTYPE (posted separately)
JAB (returns a number greater than or equal to 1 (i.e. TRUE) if the reference has the specified data type(s) and 0 (i.e. FALSE) otherwise)
VBA Code:
=LAMBDA(types,advanced_types_array,
IF(
SUM(T1F0(ISERROR(MATCH(types,AllTypes,0)))),
NA(),
LET(
basic_types_array,SWITCH(advanced_types_array,1,1,2,2,31,3,32,3,41,4,42,4,"","",16),
MAP(advanced_types_array,LAMBDA(a,OR(a=types)))+MAP(basic_types_array,LAMBDA(a,OR(a=types)))
)
)
)JAC (module for 1D [FILTER-based] "exclude_types/include_types/replace_with")
VBA Code:
=LAMBDA(exclude_types,include_types,replace_with,main_array,advanced_types_array,
LET(
d,IF(IO(include_types),exclude_types,include_types),
x,JAB(d,advanced_types_array),
IF(
IO(replace_with),
FILTER(
main_array,
IF(IO(include_types),NOT(x),x)),
IF(
IO(include_types),
IF(x,replace_with,main_array),
IF(x,main_array,replace_with)
)
)
)
)JAD [core for INDEX.Nth()]
VBA Code:
=LAMBDA(every,start_at,nth_elements,rows,output_style,
IF(
JAN(start_at),
NA(),
LET(
o,LET(
s,start_at,
n,nth_elements,
SWITCH(every,
0,IF(
OR(output_style={0,1,2}),
LET(
m,n+(IF(IO(s),0,s-1)),
FILTER(
m,
(m>=IF(IO(s),0,s))*(m<=rows))),
NA()),
1,LET(
i,(s-IF(ISNUMBER(s),n,0))+n*SEQUENCE(ROUNDDOWN(IF(IO(s),rows/MIN(n),((rows-s)/MIN(n))+1),0)),
j,IF(i>rows,"",i),
SWITCH(output_style,
0,j,
1,LET(
u,TOCOL(j,,1),
FILTER(u,u<>"")),
2,LET(
v,TOCOL(j),
FILTER(v,v<>"")))))),
IF(
SUM(T1F0(ISERROR(o))),
NA(),
o
)
)
)
)JAE [INDEXer for JAD()]
VBA Code:
=LAMBDA(reference,core_output,
IF(
ISERROR(core_output),
NA(),
LET(
r,IF(ISNUMBER(core_output),core_output,0),
MAP(r,LAMBDA(r,IF(r,INDEX(reference,r),"")))
)
)
)JAF (module for 2D "exclude_types/include_types/replace_with" when spill includes non-core blank cells)
VBA Code:
=LAMBDA(core_output,exclude_types,include_types,replace_with,main_array,advanced_types_array,
LET(
c,IF(IO(include_types),exclude_types,include_types),
w,IF(ISNUMBER(core_output),1,0),
x,JAB(c,advanced_types_array),
m,IF(
IO(include_types),
IF(w*x,IF(IO(replace_with),"",replace_with),main_array),
IF(w*x,main_array,IF(IO(replace_with),"",IF(w,replace_with,"")))),
IF(
IO(replace_with),
JAH(core_output,x,m,include_types),
m
)
)
)JAG ["output_orientation" module for INDEX.Nth()]
VBA Code:
=LAMBDA(expression,hv,output_orientation,
SWITCH(output_orientation,
0,SWITCH(hv,0,TRANSPOSE(expression),expression),
1,expression,
2,TRANSPOSE(expression)
)
)JAH (removes rows and columns containing non-core blank cells)
VBA Code:
=LAMBDA(core_cells,treated_cells,treated_output,include_types,
LET(
c,core_cells,
t,treated_cells,
m,IF(ISNUMBER(c),1,0),
d,IF(IF(IO(include_types),t*m,t),1,0),
i,IF(IO(include_types),m-d,d),
e,(IF(BYROW(i,LAMBDA(a,SUM(a))),1,0))*(SEQUENCE(ROWS(c))),
o,FILTER(e,e),
f,(IF(BYCOL(i,LAMBDA(a,SUM(a))),1,0))*(SEQUENCE(,COLUMNS(c))),
p,FILTER(f,f),
CHOOSECOLS(CHOOSEROWS(treated_output,o),p)
)
)JAI (module for "if_empty")
VBA Code:
=LAMBDA(operation,if_empty,
IF(
JAN(if_empty),
NA(),
LET(
e,IF(ISERROR(operation),IF(ERROR.TYPE(operation)=14,1,0),0),
f,IF(ISERROR(ROWS(operation)*COLUMNS(operation)),1,0),
IF(
e+f=2,
IF(IO(if_empty),operation,if_empty),
operation
)
)
)
)JAL (switch for "include_types/exclude_types")
VBA Code:
=LAMBDA(exclude_types,include_types,replace_with,
IF(
JAN(replace_with),
NA(),
IFS(
AND(IO(exclude_types),IO(include_types),IO(replace_with)),0,
OR(AND(IO(include_types),NIO(exclude_types)),AND(NIO(include_types),IO(exclude_types))),1
)
)
)JAN [Returns TRUE if the reference is an array (i.e. is not a single element or is not omitted) and FALSE otherwise (simplified form of ISARRAY)]
VBA Code:
=LAMBDA([reference],
LET(
r,IFERROR(IF(reference="","",reference),""),
IF(
IO(reference),
FALSE,
IF(ROWS(r)*COLUMNS(r)>1,TRUE,FALSE)
)
)
)JAO (Returns TRUE if the reference contains at least one error and FALSE otherwise [simplified form of ISERRORS])
VBA Code:
=LAMBDA(reference,
IF(
IO(reference),
FALSE,
IF(SUM(IF(ISERROR(reference),1,0)),TRUE,FALSE)
)
)JAP (Returns TRUE if the reference contains at least one blank and FALSE otherwise [simplified form of ISBLANKS])
VBA Code:
=LAMBDA(reference,
IF(
IO(reference),
NA(),
IF(SUM(IF(IFERROR(reference,0)="",1,0)),TRUE,FALSE)
)
)PLS (stands for "parameter limit, single")
(Returns the parameter if it is a single entry (not blank or error) and #N/A otherwise)
(Causes the function to crash to a single #N/A if the incorrect parameter is entered [either the wrong type or more than a single entry in array brackets "{}"])
VBA Code:
=LAMBDA(parameter,
INDEX(
IF(OR(JAN(parameter),JAO(parameter),JAP(parameter)),NA(),parameter),
1
)
)PLSL (stands for "parameter limit, single logical)
(Returns 1 if the parameter is a single TRUE entry; 0 if the parameter is a single FALSE entry or is omitted; and #N/A otherwise)
(Causes the function to crash to a single #N/A if incorrect parameters are entered [either anything not capable of being evaluated logically or more than a single entry in array brackets "{}"]))
VBA Code:
=LAMBDA([parameter],
IF(
IO(parameter),
0,
IF(
JAN(parameter),
NA(),
LET(
p,INDEX(parameter,1),
IF(ISERROR(p),NA(),T1F0(p))
)
)
)
)IO (shortened form of ISOMITTED)
VBA Code:
=LAMBDA(parameter,
ISOMITTED(parameter)
)NIO [shortened form of NOT(ISOMITTED)]
VBA Code:
=LAMBDA(parameter,
NOT(ISOMITTED(parameter))
)OF (Returns "FALSE" if the reference is TRUE, "0" if the reference is FALSE, and #N/A otherwise)
VBA Code:
=LAMBDA(reference,
IF(
ISERROR(IF(reference="",NA(),reference)),
NA(),
IFERROR(IF(reference,0,FALSE),NA())
)
)OT (Returns "TRUE" if the reference is TRUE, "1" if the reference is FALSE, and #N/A otherwise)
VBA Code:
=LAMBDA(reference,
IF(
ISERROR(IF(reference="",NA(),reference)),
NA(),
IFERROR(IF(reference,1,TRUE),NA())
)
)PO0 (Returns 0 if the parameter is omitted and the parameter itself otherwise)
VBA Code:
=LAMBDA([parameter],
IF(IO(parameter),0,parameter)
)T1F0 (Returns 1 if the reference is TRUE, 0 if the reference is FALSE, and #N/A otherwise)
VBA Code:
=LAMBDA(expression,
IF(
ISNUMBER(expression)+ISLOGICAL(expression),
IF(expression,1,0),
NA()
)
)Here are all the modules in the XL2BB format (note that these are the most up-to-date versions compared with the same modules from my previous posts for other functions):
| INDEX.Nth.xlsx | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 1 | ||||||
| 2 | INDEX.Nth | #CALC! | ||||
| 3 | IO | #CALC! | ||||
| 4 | JAB | #CALC! | ||||
| 5 | JAC | #CALC! | ||||
| 6 | JAD | #CALC! | ||||
| 7 | JAE | #CALC! | ||||
| 8 | JAF | #CALC! | ||||
| 9 | JAG | #CALC! | ||||
| 10 | JAH | #CALC! | ||||
| 11 | JAI | #CALC! | ||||
| 12 | JAL | #CALC! | ||||
| 13 | JAN | #CALC! | ||||
| 14 | JAO | #CALC! | ||||
| 15 | JAP | #CALC! | ||||
| 16 | NIO | #CALC! | ||||
| 17 | OF | #CALC! | ||||
| 18 | OT | #CALC! | ||||
| 19 | PLS | #CALC! | ||||
| 20 | PLSL | #CALC! | ||||
| 21 | PO0 | #CALC! | ||||
| 22 | T1F0 | #CALC! | ||||
| 23 | XERROR.TYPE | #CALC! | ||||
| 24 | XTYPE | #CALC! | ||||
| 25 | ||||||
Sheet2 | ||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C2 | C2 | =LAMBDA(lookup_1D_array,nth_elements,[every],[start_at],[output_orientation],[output_style],[exclude_types],[include_types],[replace_with],[if_empty],LET(i,ROWS(lookup_1D_array),j,COLUMNS(lookup_1D_array),m,MAX(i,j),e,PLSL(every),o,PLS(PO0(output_orientation)),s,PLS(PO0(output_style)),f,TOCOL(IF(lookup_1D_array="","",lookup_1D_array)),r,ROWS(f),n,FILTER(TOROW(nth_elements),nth_elements<=r),a,TOROW(XTYPE(lookup_1D_array,3)),w,JAD(e,start_at,n,r,s),x,JAE(f,w),p,JAE(a,w),v,IF(OR(AND(i=1,j=1),i>j),0,1),IF(AND(i>1,j>1),NA(),JAI(JAG(SWITCH(JAL(exclude_types,include_types,replace_with),0,x,1,IF(s,JAC(exclude_types,include_types,replace_with,x,p),JAF(w,exclude_types,include_types,replace_with,x,p))),v,o),if_empty)))) |
| C3 | C3 | =LAMBDA(parameter,ISOMITTED(parameter)) |
| C4 | C4 | =LAMBDA(types,advanced_types_array,IF(SUM(T1F0(ISERROR(MATCH(types,AllTypes,0)))),NA(),LET(basic_types_array,SWITCH(advanced_types_array,1,1,2,2,31,3,32,3,41,4,42,4,"","",16),MAP(advanced_types_array,LAMBDA(a,OR(a=types)))+MAP(basic_types_array,LAMBDA(a,OR(a=types)))))) |
| C5 | C5 | =LAMBDA(exclude_types,include_types,replace_with,main_array,advanced_types_array,LET(d,IF(IO(include_types),exclude_types,include_types),x,JAB(d,advanced_types_array),IF(IO(replace_with),FILTER(main_array,IF(IO(include_types),NOT(x),x)),IF(IO(include_types),IF(x,replace_with,main_array),IF(x,main_array,replace_with))))) |
| C6 | C6 | =LAMBDA(every,start_at,nth_elements,rows,output_style,IF(JAN(start_at),NA(),LET(o,LET(s,start_at,n,nth_elements,SWITCH(every,0,IF(OR(output_style={0,1,2}),LET(m,n+(IF(IO(s),0,s-1)),FILTER(m,(m>=IF(IO(s),0,s))*(m<=rows))),NA()),1,LET(i,(s-IF(ISNUMBER(s),n,0))+n*SEQUENCE(ROUNDDOWN(IF(IO(s),rows/MIN(n),((rows-s)/MIN(n))+1),0)),j,IF(i>rows,"",i),SWITCH(output_style,0,j,1,LET(u,TOCOL(j,,1),FILTER(u,u<>"")),2,LET(v,TOCOL(j),FILTER(v,v<>"")))))),IF(SUM(T1F0(ISERROR(o))),NA(),o)))) |
| C7 | C7 | =LAMBDA(reference,core_output,IF(ISERROR(core_output),NA(),LET(r,IF(ISNUMBER(core_output),core_output,0),MAP(r,LAMBDA(r,IF(r,INDEX(reference,r),"")))))) |
| C8 | C8 | =LAMBDA(core_output,exclude_types,include_types,replace_with,main_array,advanced_types_array,LET(c,IF(IO(include_types),exclude_types,include_types),w,IF(ISNUMBER(core_output),1,0),x,JAB(c,advanced_types_array),m,IF(IO(include_types),IF(w*x,IF(IO(replace_with),"",replace_with),main_array),IF(w*x,main_array,IF(IO(replace_with),"",IF(w,replace_with,"")))),IF(IO(replace_with),JAH(core_output,x,m,include_types),m))) |
| C9 | C9 | =LAMBDA(expression,hv,output_orientation,SWITCH(output_orientation,0,SWITCH(hv,0,TRANSPOSE(expression),expression),1,expression,2,TRANSPOSE(expression))) |
| C10 | C10 | =LAMBDA(core_cells,treated_cells,treated_output,include_types,LET(c,core_cells,t,treated_cells,m,IF(ISNUMBER(c),1,0),d,IF(IF(IO(include_types),t*m,t),1,0),i,IF(IO(include_types),m-d,d),e,(IF(BYROW(i,LAMBDA(a,SUM(a))),1,0))*(SEQUENCE(ROWS(c))),o,FILTER(e,e),f,(IF(BYCOL(i,LAMBDA(a,SUM(a))),1,0))*(SEQUENCE(,COLUMNS(c))),p,FILTER(f,f),CHOOSECOLS(CHOOSEROWS(treated_output,o),p))) |
| C11 | C11 | =LAMBDA(operation,if_empty,IF(JAN(if_empty),NA(),LET(e,IF(ISERROR(operation),IF(ERROR.TYPE(operation)=14,1,0),0),f,IF(ISERROR(ROWS(operation)*COLUMNS(operation)),1,0),IF(e+f=2,IF(IO(if_empty),operation,if_empty),operation)))) |
| C12 | C12 | =LAMBDA(exclude_types,include_types,replace_with,IF(JAN(replace_with),NA(),IFS(AND(IO(exclude_types),IO(include_types),IO(replace_with)),0,OR(AND(IO(include_types),NIO(exclude_types)),AND(NIO(include_types),IO(exclude_types))),1))) |
| C13 | C13 | =LAMBDA([reference],LET(r,IFERROR(IF(reference="","",reference),""),IF(IO(reference),FALSE,IF(ROWS(r)*COLUMNS(r)>1,TRUE,FALSE)))) |
| C14 | C14 | =LAMBDA(reference,IF(IO(reference),FALSE,IF(SUM(IF(ISERROR(reference),1,0)),TRUE,FALSE))) |
| C15 | C15 | =LAMBDA(reference,IF(IO(reference),NA(),IF(SUM(IF(IFERROR(reference,0)="",1,0)),TRUE,FALSE))) |
| C16 | C16 | =LAMBDA(parameter,NOT(ISOMITTED(parameter))) |
| C17 | C17 | =LAMBDA(reference,IF(ISERROR(IF(reference="",NA(),reference)),NA(),IFERROR(IF(reference,0,FALSE),NA()))) |
| C18 | C18 | =LAMBDA(reference,IF(ISERROR(IF(reference="",NA(),reference)),NA(),IFERROR(IF(reference,1,TRUE),NA()))) |
| C19 | C19 | =LAMBDA(parameter,INDEX(IF(OR(JAN(parameter),JAO(parameter),JAP(parameter)),NA(),parameter),1)) |
| C20 | C20 | =LAMBDA([parameter],IF(IO(parameter),0,IF(JAN(parameter),NA(),LET(p,INDEX(parameter,1),IF(ISERROR(p),NA(),T1F0(p)))))) |
| C21 | C21 | =LAMBDA([parameter],IF(IO(parameter),0,parameter)) |
| C22 | C22 | =LAMBDA(expression,IF(ISNUMBER(expression)+ISLOGICAL(expression),IF(expression,1,0),NA())) |
| C23 | C23 | =LAMBDA(reference,[analysis_type],[if_not_error],IF(IO(reference),NA(),LET(a,PLS(PO0(analysis_type)),o,SWITCH(a,0,SWITCH(ERROR.TYPE(reference),0,160,1,161,2,162,3,163,4,164,5,165,6,166,7,167,8,168,9,169,10,1610,11,1611,12,1612,13,1613,14,1614),1,SWITCH(ERROR.TYPE(reference),0,"dt:ERROR_EXTERNAL!",1,"dt:ERROR_NULL!",2,"dt:ERROR_DIV/0!",3,"dt:ERROR_VALUE!",4,"dt:ERROR_REF!",5,"dt:ERROR_NAME?",6,"dt:ERROR_NUM!",7,"dt:ERROR_N/A",8,"dt:ERROR_GETTING_Data",9,"dt:ERROR_SPILL!",10,"dt:ERROR_CONNECT!",11,"dt:ERROR_BLOCKED!",12,"dt:ERROR_UNKNOWN!",13,"dt:ERROR_FIELD!",14,"dt:ERROR_CALC!"),2,ERROR.TYPE(reference)),IF(ISERROR(a),o,IF(JAN(if_not_error),NA(),IF(IO(if_not_error),o,IF(ISERROR(o),if_not_error,o))))))) |
| C24 | C24 | =LAMBDA(reference,[analysis_type],IF(IO(reference),NA(),LET(r,reference,a,PLS(PO0(analysis_type)),IF(OR(a={0,1,2,3}),LET(z,MAP(r,LAMBDA(b,IF(ISERROR(b),XERROR.TYPE(b),IF(b="",IF(ISBLANK(b),31,32),IF(ISNUMBER(b),1,IF(ISTEXT(b),2,IF(b,41,42))))))),SWITCH(a,0,SWITCH(z,1,"dt:NUMBER",2,"dt:TEXT",31,"dt:BLANK",32,"dt:BLANK",41,"dt:LOGICAL",42,"dt:LOGICAL","dt:ERROR"),1,SWITCH(z,1,"dt:NUMBER",2,"dt:TEXT",31,"dt:BLANK_REAL",32,"dt:BLANK_FORMULA",41,"dt:LOGICAL_TRUE",42,"dt:LOGICAL_FALSE",160,"dt:ERROR_EXTERNAL!",161,"dt:ERROR_NULL!",162,"dt:ERROR_DIV/0!",163,"dt:ERROR_VALUE!",164,"dt:ERROR_REF!",165,"dt:ERROR_NAME?",166,"dt:ERROR_NUM!",167,"dt:ERROR_N/A",168,"dt:ERROR_GETTING_Data",169,"dt:ERROR_SPILL!",1610,"dt:ERROR_CONNECT!",1611,"dt:ERROR_BLOCKED!",1612,"dt:ERROR_UNKNOWN!",1613,"dt:ERROR_FIELD!",1614,"dt:ERROR_CALC!"),2,SWITCH(z,1,1,2,2,31,3,32,3,41,4,42,4,16),3,z)),LET(t,MAP(r,LAMBDA(u,IF(OR(a={4,5}),TYPE(u),CELL("type",u)))),SWITCH(a,4,t,5,SWITCH(t,1,"dt:NUMBER",2,"dt:TEXT",4,"dt:LOGICAL",16,"dt:ERROR"),6,t)))))) |
Upvote
0
