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]

Thanks for the explanation. I did not realize that there might be some cases where values are missing from M,N, or O (or M,N,K...right?...since O is a TEXTJOIN that relies on the others). What do you want to happen in that case? Do you want the formula to stop at that point and return the message "Mét Inst Não Encontrado" in your results cell? And if so, would this message be displayed an any one or more of M,N,O are missing?

 

[KRice]

Have a look at this...I think it might be a better approach. For your row 3 result, paste this formula:

=IF(OR($K3="",$M3="",$N3=""),
            "Mét Inst Não Encontrado",
            XLOOKUP($L3, FILTER(CabosNBR[#Data],(Cabos!$A$1:$AG$1=$M3)*(Cabos!$A$2:$AG$2=$N3)*(Cabos!$A$3:$AG$3=$K3),-99),CabosNBR[Seção],"Nada feito",1,1)  )

This first checks that K,M, and N are not blank...if any are then the formula stops and displays "Mét Inst Não Encontrado". If K,M, and N have values, then this array...
(Cabos!$A$1:$AG$1=$M3)*(Cabos!$A$2:$AG$2=$N3)*(Cabos!$A$3:$AG$3=$K3)
...identifies which column is of interest (identified by a 1, while all of the other column positions will be 0's). This column-matching array is passed to the FILTER function where the entire vertical array corresponding to the column match in CabosNBR is returned...and used in the next step with XLOOKUP. But suppose the values in K,M, or N are such that the column-matching array consists of all 0's...that is, there is no column in the CabosNBR table for the particular combination of K,M, and N. We tell FILTER to return a nonsense number...-99. So either the correct vertical array (column) from CabosNBR is sent to the XLOOKUP function or the number -99 (indicating an error) is sent to XLOOKUP. Next XLOOKUP attempts to find the value in L3 in either the matching column array or in the single item array holding the value -99. In the case where -99 is the lookup array, no match will be found, so the "Nada feito" message is returned. In cases where the CabosNBR matching-column array is the lookup array, XLOOKUP will find the best match unless the L3 lookup value is greater than the last lookup array entry or represents a nonsense value (e.g. if it is text), and in those cases it would also return the "Nada feito" message.