CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Anderson
Anderson

Reputation: 25

Function ARRAY_ROW parameter 2 has mismatched row size

I have this issue with the arrayformula and importing with query function, the purpose of this is to use 2 search boxes with different criteria and extract the info. from a different sheet.

This formula stopped working for me :(

Function ARRAY_ROW parameter 2 has mismatched row size. Expected: 1. Actual: 25849.

=ARRAYFORMULA(QUERY({IMPORTRANGE(Links!C2,"Supply!A2:L"),

 IMPORTRANGE(Links!C2,"Supply!A2:B")&
 IMPORTRANGE(Links!C2,"Supply!C2:D")&
 IMPORTRANGE(Links!C2,"Supply!D2:E")},

 "select Col1,Col2,Col3,Col4,Col6,Col7,Col11,Col10,Col9,Col8,Col12
  where lower(Col13) contains '"&LOWER(B1)&"'", 0))

Thanks!!!

Upvotes: 1

Views: 1159

Answers (1)

Osm
Osm

Reputation: 2881

1 - Try this to import all the sheet lets call it "osm" by pasting the formula in a seprate sheet and drag it to the right to get all the data, and baypass the Result too large error.

=IMPORTRANGE(Links!$C$2,"Supply!"&Substitute(Address(1,COLUMN(A1),4),"1","")&"2:"&Substitute(Address(1,COLUMN(A1),4),"1","")&"")

For all the cells in row 1

Column Formula
A =IMPORTRANGE(Links!$C$2,"Supply!"&Substitute(Address(1,COLUMN(A1),4),"1","")&"2:"&Substitute(Address(1,COLUMN(A1),4),"1","")&"")
B =IMPORTRANGE(Links!$C$2,"Supply!"&Substitute(Address(1,COLUMN(B1),4),"1","")&"2:"&Substitute(Address(1,COLUMN(B1),4),"1","")&"")
C =IMPORTRANGE(Links!$C$2,"Supply!"&Substitute(Address(1,COLUMN(C1),4),"1","")&"2:"&Substitute(Address(1,COLUMN(C1),4),"1","")&"")
D =IMPORTRANGE(Links!$C$2,"Supply!"&Substitute(Address(1,COLUMN(D1),4),"1","")&"2:"&Substitute(Address(1,COLUMN(D1),4),"1","")&"")
E =IMPORTRANGE(Links!$C$2,"Supply!"&Substitute(Address(1,COLUMN(E1),4),"1","")&"2:"&Substitute(Address(1,COLUMN(E1),4),"1","")&"")
F =IMPORTRANGE(Links!$C$2,"Supply!"&Substitute(Address(1,COLUMN(F1),4),"1","")&"2:"&Substitute(Address(1,COLUMN(F1),4),"1","")&"")
G =IMPORTRANGE(Links!$C$2,"Supply!"&Substitute(Address(1,COLUMN(G1),4),"1","")&"2:"&Substitute(Address(1,COLUMN(G1),4),"1","")&"")
H =IMPORTRANGE(Links!$C$2,"Supply!"&Substitute(Address(1,COLUMN(H1),4),"1","")&"2:"&Substitute(Address(1,COLUMN(H1),4),"1","")&"")
I =IMPORTRANGE(Links!$C$2,"Supply!"&Substitute(Address(1,COLUMN(I1),4),"1","")&"2:"&Substitute(Address(1,COLUMN(I1),4),"1","")&"")
J =IMPORTRANGE(Links!$C$2,"Supply!"&Substitute(Address(1,COLUMN(J1),4),"1","")&"2:"&Substitute(Address(1,COLUMN(J1),4),"1","")&"")
K =IMPORTRANGE(Links!$C$2,"Supply!"&Substitute(Address(1,COLUMN(K1),4),"1","")&"2:"&Substitute(Address(1,COLUMN(K1),4),"1","")&"")

2 - Then paste this in place of your formula

=ArrayFormula(
 LAMBDA(i, 
 QUERY({i,i&i&i},
 "select Col1,Col2,Col3,Col4,Col6,Col7,Col11,Col10,Col9,Col8,Col12
  where lower(Col13) contains '"&LOWER(Permisos_supply!B4)&"' AND lower(Col2) CONTAINS '"&LOWER(Permisos_supply!B3)&"'", 0))({osm!A:K}))

Explanation

The first formula is the same as this:

=IMPORTRANGE(Links!$C$2,"Supply!A2:A)

to import one column A2:A.

We replaced 'A' with "&Substitute(Address(1,COLUMN(A1),4),"1","")&" to get the column letter Dynamically when dragged to the right we get Column B,C,D,...K.

The second formula is the same as your's but the impoted range is osm!A:K put in a Lambda call with the name i "you can name it any thing you like, i is short for import" so you dont need to write the range refrence every time.

Used formulas help
ARRAYFORMULA - LAMBDA - QUERY - LOWER - AND

Upvotes: 3

Related Questions