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
