Avoid XLookup to return Array

[Carlos Matioli]

Hello,

I'm trying to do a nested XLookup, as shown below. Inittially I will paste a "model" line, without any info. In the column P, I will do a nested XLookup, where I get the TextJoin of M:O to find this text in CabosNBR table headers. In the array returned, I search the value of column L, to get the cable section, which is my goal.

The thing is that if those cells (M:O) are blank and/or with values that don't match the CabosNBR table headers, the xlookup returns an array of all the cable sections.

How do I prevent this (xlookup do return array), showing a message or something like that if returns an array?

Ps.: the second xlookup needs to be "return the next larger item", because the value in the "L" column almost never matches the values in the CabosNBR data range.

Thank you for your time.

Cálculo cabo_rev1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Item	TAG Circ.	DE	Para	Descrição	Potência [kVA]	Potência [kW]	FP Regime [%]	FP Partida [%]	Tensão [V]	Nº Fases	Ib (A)	Tipo de Cabo	Método Inst.
2						20	17	85,00%		220	3	52,49	PVC	A1	PVC | A1 | 3	16
3						20	17	85,00%		220	3	26,10				0,5
4																0,75
5																1
6																1,5
7																2,5
8																4
9																6
10																10
11																16
12																25
13																35
14																50
15																70
16																95
17																120
18																150
19																185
20																240
21																300
22																400
23																500
24																630
25																800
26																1000
Cálculo

Cell Formulas
Range	Formula
L2	L2	=IF($K2=3,(F2*1000)/(J2*SQRT(3)),(F2*1000)/J2)
O2	O2	=TEXTJOIN(" | ",TRUE,M2:N2,K2)
G2:G3	G2	=F2*H2
P2:P26	P2	=XLOOKUP($L2, XLOOKUP($O2,CabosNBR[#Headers],CabosNBR[#Data],"Mét Inst Não Encontrado",0,1), CabosNBR[Seção],"Nada feito",1,2)

Cells with Data Validation
Cell	Allow	Criteria
M2:M3	List	PVC;EPR/HEPR
N2:N3	List	=INDIRETO("MetodoInst[Método]")

Cálculo cabo_rev1.xlsx
	A	B	C
1	Tipo cabo	PVC	PVC
2	Método Inst	A1	A1
3	Nº Cond	2	3
4	Seção	PVC | A1 | 2	PVC | A1 | 3
5	0,5	7	7
6	0,75	9	9
7	1	11	10
8	1,5	14,5	13,5
9	2,5	19,5	18
10	4	26	24
11	6	34	31
12	10	46	42
13	16	61	56
14	25	80	73
15	35	99	89
16	50	119	108
17	70	151	136
18	95	182	164
19	120	210	188
20	150	240	216
21	185	273	245
22	240	321	286
23	300	367	328
24	400	438	390
25	500	502	447
26	630	578	514
27	800	669	593
28	1000	767	679
Cabos

 

[KRice]

I used a FILTER function to return the inner array, and then the outer XLOOKUP looks at the first column of the CaborNBR to find the position of the "match" and returns the value from the array produced by the FILTER function. You do not need the TEXTJOIN function to concatenate the three different lookup values used to determine which column is needed in the CabosNBR table. I show how those three matches can be done inside the FILTER function.

MrExcel_20220515.xlsx
	A	B	C
1	Tipo cabo	PVC	PVC2
2	Método Inst	A1	A1
3	Nº Cond	2	3
4	Seção	PVC | A1 | 2	PVC | A1 | 3
5	0.5	7	7
6	0.75	9	9
7	1	11	10
8	1.5	14.5	13.5
9	2.5	19.5	18
10	4	26	24
11	6	34	31
12	10	46	42
Cabos

MrExcel_20220515.xlsx
	K	L	M	N	O	P
1	Nº Fases	Ib (A)	Tipo de Cabo	Método Inst.
2	3	52.486388	PVC2	A1		136
3	2	26.1	PVC	A1		99
Sheet9

Cell Formulas
Range	Formula
L2	L2	=IF($K2=3,(F2*1000)/(J2*SQRT(3)),(F2*1000)/J2)
P2:P3	P2	=XLOOKUP($L2,CabosNBR[Seção],FILTER(CabosNBR[#Data],(Cabos!$A$1:$C$1=M2)*(Cabos!$A$2:$C$2=N2)*(Cabos!$A$3:$C$3=K2),"Mét Inst Não Encontrado"),"Nada feito",1,1)

 

[Carlos Matioli]

@KRice, thank you for the reply and different approach. It is much simpler then double XLookup. But the situation persists. When the Filter function returns the [if_empyt] message, the XLookup returns the entire CabosNBR[Seção] array.

I used a FILTER function to return the inner array, and then the outer XLOOKUP looks at the first column of the CaborNBR to find the position of the "match" and returns the value from the array produced by the FILTER function. You do not need the TEXTJOIN function to concatenate the three different lookup values used to determine which column is needed in the CabosNBR table. I show how those three matches can be done inside the FILTER function.

MrExcel_20220515.xlsx
	A	B	C
1	Tipo cabo	PVC	PVC2
2	Método Inst	A1	A1
3	Nº Cond	2	3
4	Seção	PVC | A1 | 2	PVC | A1 | 3
5	0.5	7	7
6	0.75	9	9
7	1	11	10
8	1.5	14.5	13.5
9	2.5	19.5	18
10	4	26	24
11	6	34	31
12	10	46	42
Cabos

MrExcel_20220515.xlsx
	K	L	M	N	O	P
1	Nº Fases	Ib (A)	Tipo de Cabo	Método Inst.
2	3	52.486388	PVC2	A1		136
3	2	26.1	PVC	A1		99
Sheet9

Cell Formulas
Range	Formula
L2	L2	=IF($K2=3,(F2*1000)/(J2*SQRT(3)),(F2*1000)/J2)
P2:P3	P2	=XLOOKUP($L2,CabosNBR[Seção],FILTER(CabosNBR[#Data],(Cabos!$A$1:$C$1=M2)*(Cabos!$A$2:$C$2=N2)*(Cabos!$A$3:$C$3=K2),"Mét Inst Não Encontrado"),"Nada feito",1,1)

 

[KRice]

Let me know if this gives the desired outputs. I'm not quite sure what the return messages are. In this version, if the inner FILTER returns nothing (it doesn't find a column), it returns a blank. Then XLOOKUP will fail because there is no lookup array, so it will produce an error. So I've wrapped the entire formula with an IFERROR and instructed it to report back the message "Mét Inst Não Encontrado". Is that what you want for an output in that case?

MrExcel_20220515.xlsx
	K	L	M	N	O	P
1	Nº Fases	Ib (A)	Tipo de Cabo	Método Inst.
2	3	52.486388	PVC2	A2		Mét Inst Não Encontrado
3	2	26.1	PVC	A1		99
Sheet9

Cell Formulas
Range	Formula
L2	L2	=IF($K2=3,(F2*1000)/(J2*SQRT(3)),(F2*1000)/J2)
P2:P3	P2	=IFERROR(XLOOKUP($L2,CabosNBR[Seção],FILTER(CabosNBR[#Data],(Cabos!$A$1:$C$1=M2)*(Cabos!$A$2:$C$2=N2)*(Cabos!$A$3:$C$3=K2),""),"Nada feito",1,1),"Mét Inst Não Encontrado")

 

[Carlos Matioli]

Let me know if this gives the desired outputs. I'm not quite sure what the return messages are. In this version, if the inner FILTER returns nothing (it doesn't find a column), it returns a blank. Then XLOOKUP will fail because there is no lookup array, so it will produce an error. So I've wrapped the entire formula with an IFERROR and instructed it to report back the message "Mét Inst Não Encontrado". Is that what you want for an output in that case?

MrExcel_20220515.xlsx
	K	L	M	N	O	P
1	Nº Fases	Ib (A)	Tipo de Cabo	Método Inst.
2	3	52.486388	PVC2	A2		Mét Inst Não Encontrado
3	2	26.1	PVC	A1		99
Sheet9

Cell Formulas
Range	Formula
L2	L2	=IF($K2=3,(F2*1000)/(J2*SQRT(3)),(F2*1000)/J2)
P2:P3	P2	=IFERROR(XLOOKUP($L2,CabosNBR[Seção],FILTER(CabosNBR[#Data],(Cabos!$A$1:$C$1=M2)*(Cabos!$A$2:$C$2=N2)*(Cabos!$A$3:$C$3=K2),""),"Nada feito",1,1),"Mét Inst Não Encontrado")

I'm sorry, but I forgot to mention in the last reply. The value returned must be the one contained the CabosNBR[Seção] table column. So in your P2 formula, I switched the lookup_array and return_array:

Cálculo cabo_rev1.xlsx
	Q
3	0,5
Cálculo

Cell Formulas
Range	Formula
Q3:Q26	Q3	=IFERROR(XLOOKUP($L3,FILTER(CabosNBR[#Data],(Cabos!$A$1:$AG$1=$M3)*(Cabos!$A$2:$AG$2=$N3)*(Cabos!$A$3:$AG$3=$K3),"Mét Inst Não Encontrado"),CabosNBR[Seção],"Nada feito",1,1),"erro")
Dynamic array formulas.

 

[KRice]

Thank you for that clarification. I noticed a range mismatch issue. The FILTER formula needs to refer to the same size horizontal range as is found in the CabosNBR table. Since that table currently only has 3 columns, the logic tests that establish the matching-column criteria need to be an array that is only 3 elements wide...so all of the AG's become C's. In your actual table, just adjust these ranges to ensure they cover the same number of columns. Here is the adjusted formula, including your switched arrays:

MrExcel_20220515.xlsx
	F	G	H	I	J	K	L	M	N	O	P	Q
1
2	Potência [kVA]	Potência [kW]	FP Regime [%]	FP Partida [%]	Tensão [V]	Nº Fases	Ib (A)	Tipo de Cabo	Método Inst.
3	20	17	0.85		220	3	52.486388	PVC2	A1			16
4	20	17	0.85		220	2	26.1	PVC	A1			6
Sheet15

Cell Formulas
Range	Formula
L3	L3	=IF($K3=3,(F3*1000)/(J3*SQRT(3)),(F3*1000)/J3)
G3:G4	G3	=F3*H3
Q3:Q4	Q3	=IFERROR(XLOOKUP($L3,FILTER(CabosNBR[#Data],(Cabos!$A$1:$C$1=$M3)*(Cabos!$A$2:$C$2=$N3)*(Cabos!$A$3:$C$3=$K3),"Mét Inst Não Encontrado"),CabosNBR[Seção],"Nada feito",1,1),"erro")

 

[Carlos Matioli]

Thank you for that clarification. I noticed a range mismatch issue. The FILTER formula needs to refer to the same size horizontal range as is found in the CabosNBR table. Since that table currently only has 3 columns, the logic tests that establish the matching-column criteria need to be an array that is only 3 elements wide...so all of the AG's become C's. In your actual table, just adjust these ranges to ensure they cover the same number of columns. Here is the adjusted formula, including your switched arrays:

MrExcel_20220515.xlsx
	F	G	H	I	J	K	L	M	N	O	P	Q
1
2	Potência [kVA]	Potência [kW]	FP Regime [%]	FP Partida [%]	Tensão [V]	Nº Fases	Ib (A)	Tipo de Cabo	Método Inst.
3	20	17	0.85		220	3	52.486388	PVC2	A1			16
4	20	17	0.85		220	2	26.1	PVC	A1			6
Sheet15

Cell Formulas
Range	Formula
L3	L3	=IF($K3=3,(F3*1000)/(J3*SQRT(3)),(F3*1000)/J3)
G3:G4	G3	=F3*H3
Q3:Q4	Q3	=IFERROR(XLOOKUP($L3,FILTER(CabosNBR[#Data],(Cabos!$A$1:$C$1=$M3)*(Cabos!$A$2:$C$2=$N3)*(Cabos!$A$3:$C$3=$K3),"Mét Inst Não Encontrado"),CabosNBR[Seção],"Nada feito",1,1),"erro")

@KRice, sorry, but since my first post I complemented the CabosNBR with more columns. It doesn't change the application, just the content. I'm updanting it below.

About the formula, since the XLookup doesn't return an error, the IFERROR function has no effect. The only "error" is a #Spill if I insert a text interrupting the array.

I found this tip that helped a lot: Fix XLOOKUP #SPILL Error. Just type @ at the beginning. The correct formula is shown in the cell R3 below.

Sorry for the long post ?

Cálculo cabo_rev1.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	DE	Para	Descrição	Potência [kVA]	Potência [kW]	FP Regime [%]	FP Partida [%]	Tensão [V]	Nº Fases	Ib (A)	Tipo de Cabo	Método Inst.		Seção do Cabo
2				10	8,5	85,00%		220	2	45,45	EPR	A1	EPR | A1 | 2	10
3				20	17	85,00%		220	3	26,10		A2	A2 | 3	0,5	#DESPEJAR!	Erro!!!!
4														0,75	x
5														1
6														1,5
7														2,5
8														4
9														6
10														10
11														16
12														25
13														35
14														50
15														70
16														95
17														120
18														150
19														185
20														240
21														300
22														400
23														500
24														630
25														800
26														1000
Cálculo

Cell Formulas
Range	Formula
L2	L2	=IF($K2=3,(F2*1000)/(J2*SQRT(3)),(F2*1000)/J2)
O2:O3	O2	=TEXTJOIN(" | ",TRUE,M2:N2,K2)
P2	P2	=XLOOKUP($L2, FILTER(CabosNBR[#Data], (Cabos!$A$1:$AG$1=$M2)*(Cabos!$A$2:$AG$2=$N2)*(Cabos!$A$3:$AG$3=$K2), "Mét Inst Não Encontrado"), CabosNBR[Seção], "Nada feito", 1, 1 )
G2:G3	G2	=F2*H2
P3:P26,Q3	P3	=IFERROR(XLOOKUP($L3, FILTER(CabosNBR[#Data], (Cabos!$A$1:$AG$1=$M3)*(Cabos!$A$2:$AG$2=$N3)*(Cabos!$A$3:$AG$3=$K3), "Mét Inst Não Encontrado"), CabosNBR[Seção], "Nada feito", 1, 1 ),"erro")
R3	R3	=IFERROR(@XLOOKUP($L3, FILTER(CabosNBR[#Data], (Cabos!$A$1:$AG$1=$M3)*(Cabos!$A$2:$AG$2=$N3)*(Cabos!$A$3:$AG$3=$K3), "Mét Inst Não Encontrado"), CabosNBR[Seção], "Nada feito", 1, 1 ),"Erro!!!!")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
M2:M3	List	PVC;EPR
N2:N3	List	=INDIRETO("MetodoInst[Método]")

Cálculo cabo_rev1.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	AF	AG
1	Tipo cabo	PVC	PVC	PVC	PVC	PVC	PVC	PVC	PVC	PVC	PVC	PVC	PVC	PVC	PVC	PVC	PVC	EPR	EPR	EPR	EPR	EPR	EPR	EPR	EPR	EPR	EPR	EPR	EPR	EPR	EPR	EPR	EPR
2	Método Inst	A1	A1	A2	A2	B1	B1	B2	B2	C	C	D	D	E	E	F	F	A1	A1	A2	A2	B1	B1	B2	B2	C	C	D	D	E	E	F	F
3	Nº Cond	2	3	2	3	2	3	2	3	2	3	2	3	2	3	2	3	2	3	2	3	2	3	2	3	2	3	2	3	2	3	2	3
4														PVC | E | 2	PVC | E | 3	PVC | F | 2	PVC | F | 3	EPR | A1 | 2	EPR | A1 | 3	EPR | A2 | 2	EPR | A2 | 3	EPR | B1 | 2	EPR | B1 | 3	EPR | B2 | 2	EPR | B2 | 3	EPR | C | 2	EPR | C | 3	EPR | D | 2	EPR | D | 3	EPR | E | 2	EPR | E | 3	EPR | F | 2	EPR | F | 3
5	Seção	PVC | A1 | 2	PVC | A1 | 3	PVC | A2 | 2	PVC | A2 | 3	PVC | B1 | 2	PVC | B1 | 3	PVC | B2 | 2	PVC | B2 | 3	PVC | C | 2	PVC | C | 3	PVC | D | 2	PVC | D | 3	PVC | E | 2	PVC | E | 3	PVC | F | 2	PVC | F | 3	EPR | A1 | 2	EPR | A1 | 3	EPR | A2 | 2	EPR | A2 | 3	EPR | B1 | 2	EPR | B1 | 3	EPR | B2 | 2	EPR | B2 | 3	EPR | C | 2	EPR | C | 3	EPR | D | 2	EPR | D | 3	EPR | E | 2	EPR | E | 3	EPR | F | 2	EPR | F | 3
6	0,5	7	7	7	7	9	8	9	8	10	9	12	10	11	9	11	8	10	9	10	9	12	10	11	10	12	11	14	12	13	12	13	10
7	0,75	9	9	9	9	11	10	11	10	13	11	15	12	14	12	14	11	12	11	12	11	15	13	15	13	16	14	18	15	17	15	17	13
8	1	11	10	11	10	14	12	13	12	15	14	18	15	17	14	17	13	15	13	14	13	18	16	17	15	19	17	21	17	21	18	21	16
9	1,5	14,5	13,5	14	13	17,5	15,5	16,5	15	19,5	17,5	22	18	22	18,5	22	17	19	17	18,5	16,5	23	20	22	19,5	24	22	26	22	26	23	27	21
10	2,5	19,5	18	18,5	17,5	24	21	23	20	27	24	29	24	30	25	31	24	26	23	25	22	31	28	30	26	33	30	34	29	36	32	37	29
11	4	26	24	25	23	32	28	30	27	36	32	38	31	40	34	41	33	35	31	33	30	42	37	40	35	45	40	44	37	49	42	50	40
12	6	34	31	32	29	41	36	38	34	46	41	47	39	51	43	53	43	45	40	42	38	54	48	51	44	58	52	56	46	63	54	65	53
13	10	46	42	43	39	57	50	52	46	63	57	63	52	70	60	73	60	61	54	57	51	75	66	69	60	80	71	73	61	86	75	90	74
14	16	61	56	57	52	76	68	69	62	85	76	81	67	94	80	99	82	81	73	76	68	100	88	91	80	107	96	95	79	115	100	121	101
15	25	80	73	75	68	101	89	90	80	112	96	104	86	119	101	131	110	106	95	99	89	133	117	119	105	138	119	121	101	149	127	161	135
16	35	99	89	92	83	125	110	111	99	138	119	125	103	148	126	162	137	131	117	121	109	164	144	146	128	171	147	146	122	185	158	200	169
17	50	119	108	110	99	151	134	133	118	168	144	148	122	180	153	196	167	158	141	145	130	198	175	175	154	209	179	173	144	225	192	242	207
18	70	151	136	139	125	192	171	168	149	213	184	183	151	232	196	251	216	200	179	183	164	253	222	221	194	269	229	213	178	289	246	310	268
19	95	182	164	167	150	232	207	201	179	258	223	216	179	282	238	304	264	241	216	220	197	306	269	265	233	328	278	252	211	352	298	377	328
20	120	210	188	192	172	269	239	232	206	299	259	246	203	328	276	352	308	278	249	253	227	354	312	305	268	382	322	287	240	410	346	437	383
21	150	240	216	219	196	309	275	265	236	344	299	278	230	379	319	406	356	318	285	290	259	407	358	349	307	441	371	324	271	473	399	504	444
22	185	273	245	248	223	353	314	300	268	392	341	312	258	434	364	463	409	362	324	329	295	464	408	395	348	506	424	363	304	542	456	575	510
23	240	321	286	291	261	415	370	351	313	461	403	361	297	514	430	546	485	424	380	386	346	546	481	462	407	599	500	419	351	641	538	679	607
24	300	367	328	334	298	477	426	401	358	530	464	408	336	593	497	629	561	486	435	442	396	628	553	529	465	693	576	474	396	741	621	783	703
25	400	438	390	398	355	571	510	477	425	634	557	478	394	715	597	754	656	579	519	527	472	751	661	628	552	835	692	555	464	892	745	940	823
26	500	502	447	456	406	656	587	545	486	729	642	540	445	826	689	868	749	664	595	604	541	864	760	718	631	966	797	627	525	1030	859	1083	946
27	630	578	514	526	467	758	678	626	559	843	743	614	506	958	798	1005	855	765	685	696	623	998	879	825	725	1122	923	711	596	1196	995	1254	1088
28	800	669	593	609	540	881	788	723	645	978	865	700	577	1118	930	1169	971	885	792	805	721	1158	1020	952	837	1311	1074	811	679	1396	1159	1460	1252
29	1000	767	679	698	618	1012	906	827	738	1125	996	792	652	1292	1073	1346	1079	1014	908	923	826	1332	1173	1088	957	1515	1237	916	767	1613	1336	1683	1420
Cabos

Cell Formulas
Range	Formula
N4:AG4	N4	=TEXTJOIN(" | ",TRUE,N1:N3)

 

[KRice]

I'm not following something. If FILTER finds only one column of interest in the Cabos table (I don't believe there are any columns with the same combination of data in rows 1:3), then XLOOKUP will look down that single column to find the best match and return the single value from CabosNBR[Seção] (I say single value because CabosNBR[Seção] is also a single column). So there should be no SPILL error because you will have only one value returned. The SPILL error occurs when there are an insufficient number of cells to receive an output array (multiple values returned).

 

[KRice]

Why do you want the expression "Mét Inst Não Encontrado" inside the FILTER function? If FILTER cannot find a matching column in CabosNBR, as written, FILTER will return this expression to the XLOOKUP function and then XLOOKUP will generate an error...so the expression will never be displayed. What is the purpose of "Mét Inst Não Encontrado"?

 

[Carlos Matioli]

Why do you want the expression "Mét Inst Não Encontrado" inside the FILTER function? If FILTER cannot find a matching column in CabosNBR, as written, FILTER will return this expression to the XLOOKUP function and then XLOOKUP will generate an error...so the expression will never be displayed. What is the purpose of "Mét Inst Não Encontrado"?

(I don't believe there are any columns with the same combination of data in rows 1:3)

You are correct.

If FILTER finds only one column of interest in the Cabos table

Only when I fill all the values (M, N and O columns).

If I don't fill all the values (M, N and O columns), the FILTER function will return "Mét Inst Não Encontrado". The behavior that I saw is that when this happens, the XLOOKUP function returns the entire CabosNBR[Seção] column. If I insert any text in the line below this formula, the #SPILL error occurs to me.