Xlambda
Well-known Member
- Joined
- Mar 8, 2021
- Messages
- 837
- Office Version
- 365
- Platform
- Windows
AXLOOKUP array XLOOKUP for 2D arrays, carries XLOOKUP arguments except the binary ones of search mode. calls AFLATTEN
Excel Formula:
=LAMBDA(lv,la,ra,nf,m,s,
LET(x,AND(OR(m={0,-1,1,2}),OR(s={0,1,-1})),sm,IF(s=0,1,s),
fla,AFLATTEN(la),fra,AFLATTEN(ra),
IF(x,XLOOKUP(lv,fla,fra,nf,m,sm),"check values")
)
)
LAMBDA 6.0.xlsx | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
1 | lookup array | return array | ||||||||||||||
2 | 1 | 2 | a1234 | b | 3.1 | 1 | 2 | 3 | 4 | 5 | ||||||
3 | d | 4.1 | 1 | x1234 | a | 6 | 7 | 8 | 9 | 10 | ||||||
4 | a2345 | 2 | a | 2 | 11 | 12 | 13 | 14 | 15 | |||||||
5 | 3.9 | b | x3456 | d | 4.9 | 16 | 17 | 18 | 19 | 20 | ||||||
6 | ||||||||||||||||
7 | looup value | regular XLOOKUP function | =AXLOOKUP(A8:B9,A2:E5,G2#,,,) | =AXLOOKUP(A8:B9,A2:E5,G2#,,,-1) | ||||||||||||
8 | 1 | 2 | #VALUE! | #VALUE! | m=0 | 1 | 2 | m=0 | 8 | 15 | ||||||
9 | a | b | #VALUE! | #VALUE! | s=0 | 10 | 4 | s=-1 | 14 | 17 | ||||||
10 | ||||||||||||||||
11 | =AXLOOKUP(A12:B13,A2:E5,G2#,"NA",,) | =AXLOOKUP(A12:B13,A2:E5,G2#,,-1,) | =AXLOOKUP(A12:B13,A2:E5,G2#,,-1,-1) | |||||||||||||
12 | b | d | nf="NA" | 4 | 6 | m=-1 | 4 | 6 | m=-1 | 17 | 19 | |||||
13 | 3.5 | 4.5 | m=0 | NA | NA | s=0 | 5 | 7 | s=-1 | 5 | 7 | |||||
14 | s=0 | input error | ||||||||||||||
15 | =AXLOOKUP(A12:B13,A2:E5,G2#,,1,) | =AXLOOKUP(A12:B13,A2:E5,G2#,,1,-1) | =AXLOOKUP(A12:B13,A2:E5,G2#,,3,) | |||||||||||||
16 | m=1 | 4 | 6 | m=1 | 17 | 19 | check values | |||||||||
17 | s=0 | 16 | 20 | s=-1 | 16 | 20 | ||||||||||
18 | =AXLOOKUP(A19:B19,A2:E5,G2#,,2,) | =AXLOOKUP(A19:B19,A2:E5,G2#,,2,-1) | ||||||||||||||
19 | a* | x* | m=2 | 3 | 9 | m=2 | 14 | 18 | ||||||||
20 | s=0 | s=-1 | ||||||||||||||
21 | ||||||||||||||||
22 | regular XLOOKUP arguments are carried also by AXLOOKUP except the binary ones of search mode | |||||||||||||||
23 | ||||||||||||||||
24 | nf=not found | m=match mode | s=search mode | |||||||||||||
25 | 0 exact match | 1 search first to last | ||||||||||||||
26 | -1 exact match or next smaller item | -1 search last to first | ||||||||||||||
27 | 1 exact match or next larger item | |||||||||||||||
28 | 2 wild character match | |||||||||||||||
29 | ||||||||||||||||
30 | Obs. | inside formula , if "s" is ignored or 0, will take the default XLOOKUP value 1 | ||||||||||||||
AXLOOKUP post |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G2:K5 | G2 | =SEQUENCE(4,5) |
H7,K7,G18,D18,G15,J15,D15,D11,G11,J11 | H7 | =FORMULATEXT(H8) |
D8:E9 | D8 | =XLOOKUP(A8:B9,A2:E5,G2#) |
H8:I9 | H8 | =AXLOOKUP(A8:B9,A2:E5,G2#,,,) |
K8:L9 | K8 | =AXLOOKUP(A8:B9,A2:E5,G2#,,,-1) |
D12:E13 | D12 | =AXLOOKUP(A12:B13,A2:E5,G2#,"NA",,) |
G12:H13 | G12 | =AXLOOKUP(A12:B13,A2:E5,G2#,,-1,) |
J12:K13 | J12 | =AXLOOKUP(A12:B13,A2:E5,G2#,,-1,-1) |
D16:E17 | D16 | =AXLOOKUP(A12:B13,A2:E5,G2#,,1,) |
G16:H17 | G16 | =AXLOOKUP(A12:B13,A2:E5,G2#,,1,-1) |
J16 | J16 | =AXLOOKUP(A12:B13,A2:E5,G2#,,3,) |
D19:E19 | D19 | =AXLOOKUP(A19:B19,A2:E5,G2#,,2,) |
G19:H19 | G19 | =AXLOOKUP(A19:B19,A2:E5,G2#,,2,-1) |
Dynamic array formulas. |
Upvote
0