AXLOOKUP

[Xlambda]

AXLOOKUP array XLOOKUP for 2D arrays, carries XLOOKUP arguments except the binary ones of search mode. calls AFLATTEN

=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.

 

[Xlambda]

This study was Inspired by Mike's ExcelIsFun latest 7 YT videos regarding the advantages of using good old LOOKUP functions.
1st :

7th:

Introducing LKP, a function that solves the endless debate of which lookup functions is better in various circumstances and also solves a big flaw of XLOOKUP's argument assigning that affects its performance considerably, compared with LOOKUP and VLOOKUP.
LKP is an All In One lookup function that combines the versatility and best performance out of all 4 lookup functions LOOKUP,VLOOKUP,HLOOKUP,XLOOKUP (plus, adds versatility of CHOOSECOLS/CHOOSEROWS arguments to use negative clm/row indexes. (like -1 for last row/clm) )

The concept is quite simple. All lookup functions need lookup value, lookup array (x) and return array (y) plus some arguments to trigger different behaviors or no other arguments at all.
LOOKUP specific functionality: when return array, ra, is omitted (2nd syntax of LOOKUP if la is 2D), if lookup array la is vertical (r>=c) => x=first column and y=last clmn of "la", respectively x=first row and y=last row if "la" is horizontal (r<c)
VLOOKUP/HLOOKUP specific functionality: x=first col/row of "la" and y=choosecols(la,clm index)/chooserows(la,row index)
LKP function embeds these functionalities and is designed to extract x and y for every possible scenario with the help of an additional options argument o as first argument, followed by the rest of the usual XLOOKUP arguments: lookup value(lv), lookup array (la), return array (ra), if not found (nf) , match mode (mm), search mode (sm)
LKP(o,lv,la,[ra],[nf],[mm],[sm]) All In One LooKuP custom made function
o: options argument: 0 or omitted, -1,1 or 2
o=0 or omitted => XLOOKUP functionality
o=-1 => return array ra is listening for rows index (like HLOOKUP)
o=1 => return array ra is listening for cols index (like VLOOKUP)
o=2 triggers LOOKUP
lv, la, ra, nf, mm, sm : xlookup's native arguments

=LAMBDA(o, lv, la, [ra], [nf], [mm], [sm],
    LET(
        i, ISOMITTED(ra),
        f, IF(ISOMITTED(nf), NA(), nf),
        r, ROWS(la),
        c, COLUMNS(la),
        v, r >= c,
        w, TAKE(la, , 1),
        l, TAKE(la, 1),
        k, IF(v, w, l),
        x, IF(OR(r = 1, c = 1), la, SWITCH(o, 0, k, -1, l, 1, w, 2, k)),
        y, IF(i, IF(v, TAKE(la, , -1), TAKE(la, -1)), SWITCH(o, 0, ra, 2, ra, -1, CHOOSEROWS(la, ra), 1, CHOOSECOLS(la, ra))),
        t, TOCOL(x),
        j, AND(t = SORT(t)),
        a, IF(j, 2, 1),
        IF(
            o = 2,
            IFNA(LOOKUP(lv, x, y), f),
            IF(AND(j, mm = -1, sm = -1), IFNA(LOOKUP(lv, x, y), f), LET(s, IF(sm, sm, IF(mm = -1, 1, a)), XLOOKUP(lv, x, y, nf, mm, s)))
        )
    )
)

@Excelambda All In One Lookup Functions.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1						Note: to compare LOOKUP VLOOKUP integration "la" was set in ascending order with dups and "lv" has values that returns results as exact or aprox match
2						o, omitted => ra, not omitted => XLOOKUP functionality
3		lv				la		ra		=LKP(,B4:D6,F4:F12,H4:H12,"x")					=LKP(,B4:D6,F4:F12,H4:H12,"-",,-1)					=LKP(,B4:D6,F4:F12,H4:H12,,1)
4		1	2	3		1		A		A	D	G			C	F	I			A	D	G
5		0.5	1.5	2.5		1		B		x	x	x			-	-	-			A	D	G
6		2.99	3	3.01		1		C		x	G	x			-	I	-			G	G	#N/A
7						2		D
8						2		E		=LKP(,B4:D6,F4:F12,H4:H12,,1,-1)					=LKP(,B4:D6,F4:F12,H4:H12,,-1)					=LKP(,B4:D6,F4:F12,H4:H12,,-1,-1)
9						2		F		C	F	I			A	D	G			C	F	I
10						3		G		C	F	I			#N/A	A	D			#N/A	C	F
11						3		H		I	I	#N/A			D	G	G			F	I	I
12						3		I
13
14						o, omitted, ra, omitted => LOOKUP functionality using XLOOKUP's arguments
15		lv				la vertical array (r>=c)						=LKP(,B16:D18,F16:I24)					=LKP(,B16:D18,F16:I24,,"--",0,-1)					=LKP(,B16:D18,F16:I24,,,1)
16		1	2	3		1	10	100	A			A	D	G			C	F	I			A	D	G
17		0.5	1.5	2.5		1	20	200	B			#N/A	#N/A	#N/A			--	--	--			A	D	G
18		2.99	3	3.01		1	30	300	C			#N/A	G	#N/A			--	I	--			G	G	#N/A
19						2	40	400	D
20						2	50	500	E			=LKP(,B16:D18,F16:I24,,,1,-1)					=LKP(,B16:D18,F16:I24,,,-1)					=LKP(,B16:D18,F16:I24,,,-1,-1)
21						2	60	600	F			C	F	I			A	D	G			C	F	I
22						3	70	700	G			C	F	I			#N/A	A	D			#N/A	C	F
23						3	80	800	H			I	I	#N/A			D	G	G			F	I	I
24						3	90	900	I
25
26						o, omitted, ra, omitted => LOOKUP functionality using XLOOKUP's arguments
27		lv				la horizontal array (r<c)											=LKP(,B28:D30,F28:N31)					=LKP(,B28:D30,F28:N31,,"",-1,-1)
28		1	2	3		1	1	1	2	2	2	3	3	3			A	D	G			C	F	I
29		0.5	1.5	2.5		10	20	30	40	50	60	70	80	90			#N/A	#N/A	#N/A				C	F
30		2.99	3	3.01		100	200	300	400	500	600	700	800	900			#N/A	G	#N/A			F	I	I
31						A	B	C	D	E	F	G	H	I
32
33						o,1 ra,clm index => VLOOKUP functionality using XLOOKUP's arguments
34		lv				la						=LKP(1,B35:D37,F35:I43,-1,"")					=LKP(1,B35:D37,F35:I43,3,"")					=LKP(1,B35:D37,F35:I43,-2,"")					=VLOOKUP(B35:D37,F35:I43,3,0)
35		1	2	3		1	10	100	A			A	D	G			100	400	700			100	400	700			100	400	700
36		0.5	1.5	2.5		1	20	200	B																		#N/A	#N/A	#N/A
37		2.99	3	3.01		1	30	300	C				G					700					700				#N/A	700	#N/A
38						2	40	400	D
39						2	50	500	E			=LKP(1,B35:D37,F35:I43,4,"",1)					=LKP(1,B35:D37,F35:I43,-3,"",-1)					=LKP(1,B35:D37,F35:I43,-1,"",-1,-1)					=VLOOKUP(B35:D37,F35:I43,4)
40						2	60	600	F			A	D	G			10	40	70			C	F	I			C	F	I
41						3	70	700	G			A	D	G				10	40				C	F			#N/A	C	F
42						3	80	800	H			G	G				40	70	70			F	I	I			F	I	I
43						3	90	900	I
44
45						o,-1 ra,row index => HLOOKUP functionality using XLOOKUP's arguments
46		lv				la
47		1	2	3		1	1	1	2	2	2	3	3	3
48		0.5	1.5	2.5		10	20	30	40	50	60	70	80	90
49		2.99	3	3.01		100	200	300	400	500	600	700	800	900
50						A	B	C	D	E	F	G	H	I
51
52						=LKP(-1,B47:D49,F47:N50,-1,"")					=LKP(-1,B47:D49,F47:N50,3,"")					=LKP(-1,B47:D49,F47:N50,-2,"")					=HLOOKUP(B47:D49,F47:N50,3,0)
53						A	D	G			100	400	700			100	400	700			100	400	700
54																					#N/A	#N/A	#N/A
55							G					700					700				#N/A	700	#N/A
56
57						=LKP(-1,B47:D49,F47:N50,4,"",1)					=LKP(-1,B47:D49,F47:N50,-3,"",-1)					=LKP(-1,B47:D49,F47:N50,-1,"",-1,-1)					=HLOOKUP(B47:D49,F47:N50,4)
58						A	D	G			10	40	70			C	F	I			C	F	I
59						A	D	G				10	40				C	F			#N/A	C	F
60						G	G				40	70	70			F	I	I			F	I	I
61
ex 1

Cell Formulas
Range	Formula
J3,O3,T3,F57,K57,P57,U57,P52,U52,F52,K52,Q39,V39,AA39,L39,AA34,V34,Q34,L34,V27,Q27,V20,Q20,L20,Q15,V15,L15,T8,J8,O8	J3	=FORMULATEXT(J4)
J4:L6	J4	=LKP(,B4:D6,F4:F12,H4:H12,"x")
O4:Q6	O4	=LKP(,B4:D6,F4:F12,H4:H12,"-",,-1)
T4:V6	T4	=LKP(,B4:D6,F4:F12,H4:H12,,1)
J9:L11	J9	=LKP(,B4:D6,F4:F12,H4:H12,,1,-1)
O9:Q11	O9	=LKP(,B4:D6,F4:F12,H4:H12,,-1)
T9:V11	T9	=LKP(,B4:D6,F4:F12,H4:H12,,-1,-1)
L16:N18	L16	=LKP(,B16:D18,F16:I24)
Q16:S18	Q16	=LKP(,B16:D18,F16:I24,,"--",0,-1)
V16:X18	V16	=LKP(,B16:D18,F16:I24,,,1)
L21:N23	L21	=LKP(,B16:D18,F16:I24,,,1,-1)
Q21:S23	Q21	=LKP(,B16:D18,F16:I24,,,-1)
V21:X23	V21	=LKP(,B16:D18,F16:I24,,,-1,-1)
Q28:S30	Q28	=LKP(,B28:D30,F28:N31)
V28:X30	V28	=LKP(,B28:D30,F28:N31,,"",-1,-1)
L35:N37	L35	=LKP(1,B35:D37,F35:I43,-1,"")
Q35:S37	Q35	=LKP(1,B35:D37,F35:I43,3,"")
V35:X37	V35	=LKP(1,B35:D37,F35:I43,-2,"")
AA35:AC37	AA35	=VLOOKUP(B35:D37,F35:I43,3,0)
L40:N42	L40	=LKP(1,B35:D37,F35:I43,4,"",1)
Q40:S42	Q40	=LKP(1,B35:D37,F35:I43,-3,"",-1)
V40:X42	V40	=LKP(1,B35:D37,F35:I43,-1,"",-1,-1)
AA40:AC42	AA40	=VLOOKUP(B35:D37,F35:I43,4)
F53:H55	F53	=LKP(-1,B47:D49,F47:N50,-1,"")
K53:M55	K53	=LKP(-1,B47:D49,F47:N50,3,"")
P53:R55	P53	=LKP(-1,B47:D49,F47:N50,-2,"")
U53:W55	U53	=HLOOKUP(B47:D49,F47:N50,3,0)
F58:H60	F58	=LKP(-1,B47:D49,F47:N50,4,"",1)
K58:M60	K58	=LKP(-1,B47:D49,F47:N50,-3,"",-1)
P58:R60	P58	=LKP(-1,B47:D49,F47:N50,-1,"",-1,-1)
U58:W60	U58	=HLOOKUP(B47:D49,F47:N50,4)
Dynamic array formulas.

@Excelambda All In One Lookup Functions.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1						o,2 => triggers explicit LOOKUP function (nf can be used but the rest of xlookup arguments mm,sm have no relevance)
2		lv				la		ra		=LKP(2,B3:D5,F3:F11,H3:H11)					=LKP(2,B3:D5,F3:F11,H3:H11,"<")					=LOOKUP(B3:D5,F3:F11,H3:H11)
3		1	2	3		1		A		C	F	I			C	F	I			C	F	I
4		0.5	1.5	2.5		1		B		#N/A	C	F			<	C	F			#N/A	C	F		any mm or sm values have no effect
5		2.99	3	3.01		1		C		F	I	I			F	I	I			F	I	I		=LKP(2,B3:D5,F3:F11,H3:H11,"",1,1)
6						2		D																C	F	I
7						2		E		if ra perpendicular to la															C	F
8						2		F		ra														F	I	I
9						3		G		A	B	C	D	E	F	G	H	I
10						3		H
11						3		I		=LKP(2,B3:D5,F3:F11,J9:R9)					=LKP(2,B3:D5,F3:F11,J9:R9,"<<")					=LOOKUP(B3:D5,F3:F11,J9:R9)
12										C	F	I			C	F	I			C	F	I
13										#N/A	C	F			<<	C	F			#N/A	C	F
14										F	I	I			F	I	I			F	I	I
15
16		lv				la vertical array (r>=c)						=LKP(2,B17:D19,F17:I25)					=LOOKUP(B17:D19,F17:I25)
17		1	2	3		1	10	100	A			C	F	I			C	F	I
18		0.5	1.5	2.5		1	20	200	B			#N/A	C	F			#N/A	C	F
19		2.99	3	3.01		1	30	300	C			F	I	I			F	I	I
20						2	40	400	D
21						2	50	500	E			=LKP(2,B17:D19,F17:I25,,"--")
22						2	60	600	F			C	F	I
23						3	70	700	G			--	C	F
24						3	80	800	H			F	I	I
25						3	90	900	I
26
27		lv				la horizontal array (r<c)
28		1	2	3		1	1	1	2	2	2	3	3	3
29		0.5	1.5	2.5		10	20	30	40	50	60	70	80	90
30		2.99	3	3.01		100	200	300	400	500	600	700	800	900
31						A	B	C	D	E	F	G	H	I
32
33						=LKP(2,B28:D30,F28:N31)					=LKP(2,B28:D30,F28:N31,,"na")					=LOOKUP(B28:D30,F28:N31)
34						C	F	I			C	F	I			C	F	I
35						#N/A	C	F			na	C	F			#N/A	C	F
36						F	I	I			F	I	I			F	I	I
37
ex 2

Cell Formulas
Range	Formula
J2,O2,T2,F33,K33,P33,L21,Q16,L16,J11,O11,T11,X5	J2	=FORMULATEXT(J3)
J3:L5	J3	=LKP(2,B3:D5,F3:F11,H3:H11)
O3:Q5	O3	=LKP(2,B3:D5,F3:F11,H3:H11,"<")
T3:V5	T3	=LOOKUP(B3:D5,F3:F11,H3:H11)
X6:Z8	X6	=LKP(2,B3:D5,F3:F11,H3:H11,"",1,1)
J12:L14	J12	=LKP(2,B3:D5,F3:F11,J9:R9)
O12:Q14	O12	=LKP(2,B3:D5,F3:F11,J9:R9,"<<")
T12:V14	T12	=LOOKUP(B3:D5,F3:F11,J9:R9)
L17:N19	L17	=LKP(2,B17:D19,F17:I25)
Q17:S19	Q17	=LOOKUP(B17:D19,F17:I25)
L22:N24	L22	=LKP(2,B17:D19,F17:I25,,"--")
F34:H36	F34	=LKP(2,B28:D30,F28:N31)
K34:M36	K34	=LKP(2,B28:D30,F28:N31,,"na")
P34:R36	P34	=LOOKUP(B28:D30,F28:N31)
Dynamic array formulas.

@Excelambda All In One Lookup Functions.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1		other examples
2		- single lookup value returns multiple results
3												lv for all examples lv = 2
4		lv				la						=LKP(,2,F5:F13,G5:I13)
5		1	2	3		1	10	100	A			40	400	D
6		0.5	1.5	2.5		1	20	200	B
7		2.99	3	3.01		1	30	300	C			=LKP(,2,F5:F13,G5:I13,,,-1)
8						2	40	400	D			60	600	F
9						2	50	500	E
10						2	60	600	F			lookup can not return multpl results for single lv
11						3	70	700	G			=LOOKUP(2,F5:F13,G5:I13)
12						3	80	800	H			#N/A
13						3	90	900	I
14												=LKP(1,2,F5:I13,{2,3,4})					=VLOOKUP(2,F5:I13,{2,3,4},0)
15												40	400	D			40	400	D
16
17												=LKP(1,2,F5:I13,{2,3,4},,,-1)					=VLOOKUP(2,F5:I13,{2,3,4})
18												60	600	F			60	600	F
19
20		- ra omitted and la only is a vector, not 2D array
21
22		lv				la		=LKP(,B23:D25,F23:F31,,"")					=LKP(,B23:D25,F23:F31,,"",-1,-1)					=LKP(2,B23:D25,F23:F31)					=LOOKUP(B23:D25,F23:F31)
23		1	2	3		1		1	2	3			1	2	3			1	2	3			1	2	3
24		0.5	1.5	2.5		1								1	2			#N/A	1	2			#N/A	1	2
25		2.99	3	3.01		1			3				2	3	3			2	3	3			2	3	3
26						2
27						2		ra
28						2		1	1	1	2	2	2	3	3	3
29						3
30						3		=LKP(,B23:D25,H28:P28,,"")					=LKP(,B23:D25,H28:P28,,"",-1,-1)					=LKP(2,B23:D25,H28:P28)					=LOOKUP(B23:D25,H28:P28)
31						3		1	2	3			1	2	3			1	2	3			1	2	3
32														1	2			#N/A	1	2			#N/A	1	2
33									3				2	3	3			2	3	3			2	3	3
34
ex 3

Cell Formulas
Range	Formula
L4,W30,M30,R30,H30,W22,M22,R22,H22,Q17,L17,Q14,L14,L11,L7	L4	=FORMULATEXT(L5)
L5:N5	L5	=LKP(,2,F5:F13,G5:I13)
L8:N8	L8	=LKP(,2,F5:F13,G5:I13,,,-1)
L12	L12	=LOOKUP(2,F5:F13,G5:I13)
L15:N15	L15	=LKP(1,2,F5:I13,{2,3,4})
Q15:S15	Q15	=VLOOKUP(2,F5:I13,{2,3,4},0)
L18:N18	L18	=LKP(1,2,F5:I13,{2,3,4},,,-1)
Q18:S18	Q18	=VLOOKUP(2,F5:I13,{2,3,4})
H23:J25	H23	=LKP(,B23:D25,F23:F31,,"")
M23:O25	M23	=LKP(,B23:D25,F23:F31,,"",-1,-1)
R23:T25	R23	=LKP(2,B23:D25,F23:F31)
W23:Y25	W23	=LOOKUP(B23:D25,F23:F31)
H31:J33	H31	=LKP(,B23:D25,H28:P28,,"")
M31:O33	M31	=LKP(,B23:D25,H28:P28,,"",-1,-1)
R31:T33	R31	=LKP(2,B23:D25,H28:P28)
W31:Y33	W31	=LOOKUP(B23:D25,H28:P28)
Dynamic array formulas.

Next post will be about the performance flaw of XLOOKUP solved by LKP's design.

 

[mgirvin]

Xlambda, You are a master mind of all masterminds : ) : ) It is just amazing that you created a single lookup formula that does everything. Are you a secret programmer at Microsoft? Probably the coolest thing in all this is that your arrays can listen for information! That means the genius programming is poetic and empathetic also : ) : )

 

[Xlambda]

Finally, vacation over, back to "school", back to posting from where we left off. Like always, Mike is too kind. Coming from the best Excel teacher in the world means a lot.

arg flaw.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1		The argument/parameter flaw of XLOOKUP. (context: lookup array "la" in ascending order)
2		Binary Search is defined as a searching algorithm used in a sorted array by repeatedly dividing the search interval in half. .
3		The idea of binary search is to use the information that the array is sorted and reduce the time complexity to O(log N)
4		All old lookup functions, MATCH,LOOKUP,VLOOKUP, by default, are doing approximate match using the binary search algorithm=> super fast. Non binary search, by comparison is super slow.
5
6		XLOOKUP syntax:
7		=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found], [match_mode], [search_mode])
8		[match_mode]:					[search_mode]:
9		0 exact match (default)					1 search first to last (default)
10		-1 exact match or next smaller item					-1 search last to first
11		1 exact match or					2 binary search (la ascending order)
12		2 wildcard char match					-2 binary search (la descending order)
13
14		As we see, the only way to trigger binary search (for an ascending lookup array) is to call [search_mode] =2.
15
16		"la" lkp array											XLOOKUP to deliver same results
17		asc with dups			"lv" lkp val		=LOOKUP(E19:E24,B19:C24)
18		la	ra		lv		↓↓		=VLOOKUP(E19:E24,B19:C24,2)				=XLOOKUP(E19:E24,B19:B24,C19:C24,,-1,-1)
19		1	1		1		2		2				2
20		1	2		1.5		2		2				2
21		2	3		2		4		4				4
22		2	4		2.5		4		4				4
23		3	5		3		6		6				6
24		3	6		3.5		6		6				6
25			"ra" return array
26							both, aprox match=> binary=> fast					since [srch_md] is already -1, can never be 2=> no binary search for last to first posiible
27												if we force it to be 2 => other results, first to last, not what we want
28		Conclusions:
29													=XLOOKUP(E19:E24,B19:B24,C19:C24,,-1,2)
30		LKP always checks if "la" is in ascending order, If Yes:											1
31		- if [search_mode] is omitted will trigger XLOOKUP(lv,la,ra,,mm,2) => binary											2
32		=> for whatever [match _mode] chosen => binary search											3
33		The flaw:											4
34		- if [search_mode] is -1 XLOOKUP can not perform binary search therefore											5
35		LKP will trigger LOOKUP(lv,la,ra) which is binary											6
36
37		LKP borrows functionality from LOOKUP and VLOOKUP
38		and is the most eficient when the others are not
39
40		=LKP(,E19:E24,B19:B24,C19:C24,,-1,-1)
41		↓↓		=LKP(1,E19:E24,B19:C24,2,,-1,-1)
42		↓↓		↓↓		=LKP(2,E19:E24,B19:C24)
43		2		2		2
44		2		2		2
45		4		4		4
46		4		4		4
47		6		6		6
48		6		6		6
49
50		All 3 possible options of LKP to deliver same results and all are binary searches
51		Next will test them speeds.
52
arg flaw

Cell Formulas
Range	Formula
G17,D41	G17	=FORMULATEXT(G19)
I18,M18,F42,M29	I18	=FORMULATEXT(I19)
G19:G24	G19	=LOOKUP(E19:E24,B19:C24)
I19:I24	I19	=VLOOKUP(E19:E24,B19:C24,2)
M19:M24	M19	=XLOOKUP(E19:E24,B19:B24,C19:C24,,-1,-1)
M30:M35	M30	=XLOOKUP(E19:E24,B19:B24,C19:C24,,-1,2)
B40	B40	=FORMULATEXT(B43)
B43:B48	B43	=LKP(,E19:E24,B19:B24,C19:C24,,-1,-1)
D43:D48	D43	=LKP(1,E19:E24,B19:C24,2,,-1,-1)
F43:F48	F43	=LKP(2,E19:E24,B19:C24)
Dynamic array formulas.

 

[Xlambda]

Mark's latest YT about XLOOKUP

Posted a comment about XLOOKUP flaw but was deleted (YT old flaw)
This is the comment:

Book1
	A
1	Great video !! Great examples !! Believe it or not , XLOOKUP(XMATCTH) has a big flaw. - consider this simple setup in this context :lookup array in ascending order, match mode: exact match or next smaller item A1:A6 B1:B6 1 A 2 B 2 C 3 D 3 E 4 F =VLOOKUP(2.5,A1:B6,2) returns C =LOOKUP(2.5,A1:B6) returns C To return C xlookup looks like this =XLOOKUP(2.5,A1:A6,B1:B6,,-1,-1) returns C The flaw : search_mode argument (last arg.) has to be -1 for search last to first like VLOOKUP and LOOKUP do natively aprox match If last arg has to be -1 can not be also 2 on the same time, so XLOOKUP con not perform binary search in this context and that means it will be dead slow, meanwhile VLOOKUP and LOOKUP are lighting fast since they do binary by default. The difference in time for large data sets or iterative algorithms is HUGE, hours instead of seconds. Conclusion: Whenever possible, sort lookup array and do binary search and if you need last to first do not use XLOOKUP, use VLOOKUP or LOOKUP. One day Excel will correct this. ✌🏼
Sheet1

XLOOKUP's match_mode and search_mode arguments

[match_mode] Optional

Specify the match type: 0 - Exact match. If none found, return #N/A. This is the default. -1 - Exact match. If none found, return the next smaller item. 1 - Exact match. If none found, return the next larger item. 2 - A wildcard match where *, ?, and ~ have special meaning.

[search_mode] Optional

Specify the search mode to use: 1 - Perform a search starting at the first item. This is the default. -1 - Perform a reverse search starting at the last item. 2 - Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned. -2 - Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		XLOOKUP flaw in 3 seconds
2
3		context:
4		-lookup_value: lv=2.5
5		-lookup_array: sorted ascending
6		-match_mode: -1 exact match or next smaller item
7		-search_mode: search last to first
8
9					=VLOOKUP(2.5,B10:C15,2)
10		1	A		C
11		2	B
12		2	C		=LOOKUP(2.5,B10:C15)
13		3	D		C
14		3	E
15		4	F		=XLOOKUP(2.5,B10:B15,C10:C15,,-1,-1)
16					C
17
18		VLOOKUP and LOOKUP , native binary search in this context => super fast
19		XLOOKUP can trigger binary search only if last argument is 2, but it's already -1
20
21		=> in this context XLOOKUP is dead Slow compared with older brothers
22
Sheet2

Cell Formulas
Range	Formula
E9,E15,E12	E9	=FORMULATEXT(E10)
E10	E10	=VLOOKUP(2.5,B10:C15,2)
E13	E13	=LOOKUP(2.5,B10:C15)
E16	E16	=XLOOKUP(2.5,B10:B15,C10:C15,,-1,-1)

 

[mgirvin]

Beautifully said. I love LOOKUP : )

One day Microsoft will think through things clearly ; )