Avoid XLookup to return Array

C

Carlos Matioli

New Member
Joined
May 15, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOP
1ItemTAG Circ.DEParaDescriçãoPotência [kVA]Potência [kW]FP Regime [%]FP Partida [%]Tensão [V]Nº FasesIb (A)Tipo de CaboMétodo Inst.
2201785,00%220352,49PVCA1PVC | A1 | 316
3201785,00%220326,100,5
40,75
51
61,5
72,5
84
96
1010
1116
1225
1335
1450
1570
1695
17120
18150
19185
20240
21300
22400
23500
24630
25800
261000
Cálculo
Cell Formulas
RangeFormula
L2L2=IF($K2=3,(F2*1000)/(J2*SQRT(3)),(F2*1000)/J2)
O2O2=TEXTJOIN(" | ",TRUE,M2:N2,K2)
G2:G3G2=F2*H2
P2:P26P2=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
CellAllowCriteria
M2:M3ListPVC;EPR/HEPR
N2:N3List=INDIRETO("MetodoInst[Método]")


Cálculo cabo_rev1.xlsx
ABC
1Tipo caboPVCPVC
2Método InstA1A1
3Nº Cond23
4SeçãoPVC | A1 | 2PVC | A1 | 3
50,577
60,7599
711110
81,514,513,5
92,519,518
1042624
1163431
12104642
13166156
14258073
15359989
1650119108
1770151136
1895182164
19120210188
20150240216
21185273245
22240321286
23300367328
24400438390
25500502447
26630578514
27800669593
281000767679
Cabos
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
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
ABC
1Tipo caboPVCPVC2
2Método InstA1A1
3Nº Cond23
4SeçãoPVC | A1 | 2PVC | A1 | 3
50.577
60.7599
711110
81.514.513.5
92.519.518
1042624
1163431
12104642
Cabos

MrExcel_20220515.xlsx
KLMNOP
1Nº FasesIb (A)Tipo de CaboMétodo Inst.
2352.486388PVC2A1136
3226.1PVCA199
Sheet9
Cell Formulas
RangeFormula
L2L2=IF($K2=3,(F2*1000)/(J2*SQRT(3)),(F2*1000)/J2)
P2:P3P2=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)
 
Upvote 0
@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.
KRice said:
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
ABC
1Tipo caboPVCPVC2
2Método InstA1A1
3Nº Cond23
4SeçãoPVC | A1 | 2PVC | A1 | 3
50.577
60.7599
711110
81.514.513.5
92.519.518
1042624
1163431
12104642
Cabos

MrExcel_20220515.xlsx
KLMNOP
1Nº FasesIb (A)Tipo de CaboMétodo Inst.
2352.486388PVC2A1136
3226.1PVCA199
Sheet9
Cell Formulas
RangeFormula
L2L2=IF($K2=3,(F2*1000)/(J2*SQRT(3)),(F2*1000)/J2)
P2:P3P2=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)
Click to expand...
 
Upvote 0
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
KLMNOP
1Nº FasesIb (A)Tipo de CaboMétodo Inst.
2352.486388PVC2A2Mét Inst Não Encontrado
3226.1PVCA199
Sheet9
Cell Formulas
RangeFormula
L2L2=IF($K2=3,(F2*1000)/(J2*SQRT(3)),(F2*1000)/J2)
P2:P3P2=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")
 
Upvote 0
KRice said:
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
KLMNOP
1Nº FasesIb (A)Tipo de CaboMétodo Inst.
2352.486388PVC2A2Mét Inst Não Encontrado
3226.1PVCA199
Sheet9
Cell Formulas
RangeFormula
L2L2=IF($K2=3,(F2*1000)/(J2*SQRT(3)),(F2*1000)/J2)
P2:P3P2=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")
Click to expand...
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
30,5
Cálculo
Cell Formulas
RangeFormula
Q3:Q26Q3=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.
 
Upvote 0
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
FGHIJKLMNOPQ
1
2Potência [kVA]Potência [kW]FP Regime [%]FP Partida [%]Tensão [V]Nº FasesIb (A)Tipo de CaboMétodo Inst.
320170.85220352.486388PVC2A116
420170.85220226.1PVCA16
Sheet15
Cell Formulas
RangeFormula
L3L3=IF($K3=3,(F3*1000)/(J3*SQRT(3)),(F3*1000)/J3)
G3:G4G3=F3*H3
Q3:Q4Q3=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")
 
Upvote 0
KRice said:
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
FGHIJKLMNOPQ
1
2Potência [kVA]Potência [kW]FP Regime [%]FP Partida [%]Tensão [V]Nº FasesIb (A)Tipo de CaboMétodo Inst.
320170.85220352.486388PVC2A116
420170.85220226.1PVCA16
Sheet15
Cell Formulas
RangeFormula
L3L3=IF($K3=3,(F3*1000)/(J3*SQRT(3)),(F3*1000)/J3)
G3:G4G3=F3*H3
Q3:Q4Q3=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")
Click to expand...
@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
CDEFGHIJKLMNOPQR
1DEParaDescriçãoPotência [kVA]Potência [kW]FP Regime [%]FP Partida [%]Tensão [V]Nº FasesIb (A)Tipo de CaboMétodo Inst.Seção do Cabo
2108,585,00%220245,45EPRA1EPR | A1 | 210
3201785,00%220326,10A2A2 | 30,5#DESPEJAR!Erro!!!!
40,75x
51
61,5
72,5
84
96
1010
1116
1225
1335
1450
1570
1695
17120
18150
19185
20240
21300
22400
23500
24630
25800
261000
Cálculo
Cell Formulas
RangeFormula
L2L2=IF($K2=3,(F2*1000)/(J2*SQRT(3)),(F2*1000)/J2)
O2:O3O2=TEXTJOIN(" | ",TRUE,M2:N2,K2)
P2P2=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:G3G2=F2*H2
P3:P26,Q3P3=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")
R3R3=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
CellAllowCriteria
M2:M3ListPVC;EPR
N2:N3List=INDIRETO("MetodoInst[Método]")


Cálculo cabo_rev1.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG
1Tipo caboPVCPVCPVCPVCPVCPVCPVCPVCPVCPVCPVCPVCPVCPVCPVCPVCEPREPREPREPREPREPREPREPREPREPREPREPREPREPREPREPR
2Método InstA1A1A2A2B1B1B2B2CCDDEEFFA1A1A2A2B1B1B2B2CCDDEEFF
3Nº Cond23232323232323232323232323232323
4PVC | E | 2PVC | E | 3PVC | F | 2PVC | F | 3EPR | A1 | 2EPR | A1 | 3EPR | A2 | 2EPR | A2 | 3EPR | B1 | 2EPR | B1 | 3EPR | B2 | 2EPR | B2 | 3EPR | C | 2EPR | C | 3EPR | D | 2EPR | D | 3EPR | E | 2EPR | E | 3EPR | F | 2EPR | F | 3
5SeçãoPVC | A1 | 2PVC | A1 | 3PVC | A2 | 2PVC | A2 | 3PVC | B1 | 2PVC | B1 | 3PVC | B2 | 2PVC | B2 | 3PVC | C | 2PVC | C | 3PVC | D | 2PVC | D | 3PVC | E | 2PVC | E | 3PVC | F | 2PVC | F | 3EPR | A1 | 2EPR | A1 | 3EPR | A2 | 2EPR | A2 | 3EPR | B1 | 2EPR | B1 | 3EPR | B2 | 2EPR | B2 | 3EPR | C | 2EPR | C | 3EPR | D | 2EPR | D | 3EPR | E | 2EPR | E | 3EPR | F | 2EPR | F | 3
60,5777798981091210119118109109121011101211141213121310
70,75999911101110131115121412141112111211151315131614181517151713
811110111014121312151418151714171315131413181617151917211721182116
91,514,513,5141317,515,516,51519,517,522182218,52217191718,516,523202219,52422262226232721
102,519,51818,517,524212320272429243025312426232522312830263330342936323729
1142624252332283027363238314034413335313330423740354540443749425040
1263431322941363834464147395143534345404238544851445852564663546553
13104642433957505246635763527060736061545751756669608071736186759074
14166156575276686962857681679480998281737668100889180107969579115100121101
1525807375681018990801129610486119101131110106959989133117119105138119121101149127161135
16359989928312511011199138119125103148126162137131117121109164144146128171147146122185158200169
175011910811099151134133118168144148122180153196167158141145130198175175154209179173144225192242207
1870151136139125192171168149213184183151232196251216200179183164253222221194269229213178289246310268
1995182164167150232207201179258223216179282238304264241216220197306269265233328278252211352298377328
20120210188192172269239232206299259246203328276352308278249253227354312305268382322287240410346437383
21150240216219196309275265236344299278230379319406356318285290259407358349307441371324271473399504444
22185273245248223353314300268392341312258434364463409362324329295464408395348506424363304542456575510
23240321286291261415370351313461403361297514430546485424380386346546481462407599500419351641538679607
24300367328334298477426401358530464408336593497629561486435442396628553529465693576474396741621783703
25400438390398355571510477425634557478394715597754656579519527472751661628552835692555464892745940823
2650050244745640665658754548672964254044582668986874966459560454186476071863196679762752510308591083946
2763057851452646775867862655984374361450695879810058557656856966239988798257251122923711596119699512541088
288006695936095408817887236459788657005771118930116997188579280572111581020952837131110748116791396115914601252
2910007676796986181012906827738112599679265212921073134610791014908923826133211731088957151512379167671613133616831420
Cabos
Cell Formulas
RangeFormula
N4:AG4N4=TEXTJOIN(" | ",TRUE,N1:N3)
 
Upvote 0
Solution
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).
 
Upvote 0
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"?
 
Upvote 0
KRice said:
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"?
Click to expand...
(I don't believe there are any columns with the same combination of data in rows 1:3)
Click to expand...
You are correct.

If FILTER finds only one column of interest in the Cabos table
Click to expand...
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Combining Data from Multiple Sheets
2
Replies
16
Views
1K
alansidman
alansidman
F
  • Solved
Loaded question with filter function multiple criteria and return value.
Replies
2
Views
249
floW_5
F
T
  • Question Question
Flattening a hierarchy from 5 columns into a unique one
Replies
1
Views
761
Kerryx
K
N
  • Solved
Filter Function returns #N/A from Dynamic Array but works with cell reference
Replies
6
Views
2K
Peter_SSs
Peter_SSs
P
  • Question Question
Combining XLOOKUP and IF/S or SWITCH
Replies
1
Views
2K
SanjayGMusafir
SanjayGMusafir

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top