Give a try on below formula- =INDEX(SPLIT(FLATTEN(SPLIT(JOIN(“”,INDEX(REPT(BYROW(A2:D3,LAMBDA(x,TEXTJOIN(“|”,0,x)))&”@”,E2:E3))),”@”)),”|”)) To make it dynamic spill array, use- =INDEX(SPLIT(FLATTEN(SPLIT(JOIN(“”,INDEX(REPT(BYROW(A2:INDEX(D2:D,MATCH(“zzz”,D2:D)),LAMBDA(x,TEXTJOIN(“|”,0,x)))&”@”,E2:INDEX(E2:E,MATCH(9^9,E2:E))))),”@”)),”|”))
…understanding the ARRAY_LITERAL ERROR: until both queries/filters/formulas output something then all is good: however if one of those queries/filters/formulas doesn’t have anything to output it outputs #N/A – No matches are found in QUERY/FILTER evaluation. – the issue is that #N/A is only in the 1st cell: but array expects that matrix on both sides … Read more
you can freeze it like: =INDIRECT(“Responses!A2:R500”) instead of your: =ARRAYFORMULA(Responses!$A$2:R500)
You want to retrieve the price like 55,500₽ from the URL of https://tarkov-market.com/item/Pack_of_sugar and put to a cell on Google Spreadsheet. I could understand like this. If my understanding is correct, how about this answer? Issue and workaround: Unfortunately, IMPORTXML cannot be used for this situation. Because IMPORTXML is used like =IMPORTXML(“https://tarkov-market.com/item/Pack_of_sugar”,”//*”), an error like … Read more
=IF(DATEDIF(A1, B1, “D”)>365, QUOTIENT(DATEDIF(A1, B1, “D”), 365)&” year(s) “& QUOTIENT(MOD(DATEDIF(A1, B1, “D”), 365), 30)&” month(s) “& MOD(QUOTIENT(MOD(DATEDIF(A1, B1, “D”), 365), 30), 30)&” day(s)”, IF(DATEDIF(A1, B1, “D”)>30, QUOTIENT(DATEDIF(A1, B1, “D”), 30)&” month(s) “& MOD(DATEDIF(A1, B1, “D”), 30)&” day(s)”, DATEDIF(A1, B1, “D”)&” day(s)”))
try: =ARRAYFORMULA(INDEX(SPLIT(REGEXREPLACE(A8:A12, “https?://www.|https?://|www.”, ), “/”),,1)) UPDATE 1: =ARRAYFORMULA(IFNA(REGEXEXTRACT(INDEX(SPLIT( REGEXREPLACE(A8:A14, “https?://www.|https?://|www.”, ), “/”),,1), “\.(.+\..+)”), INDEX(SPLIT( REGEXREPLACE(A8:A14, “https?://www.|https?://|www.”, ), “/”),,1))) UPDATE 2: =INDEX(IFERROR(REGEXEXTRACT(A1:A, “^(?:https?:\/\/)?(?:ftp:\/\/)?(?:www\.)?([^\/]+)”)))
google-sheets It is a hard task for native functions. Try a script and use it as a custom function: function getTournament(teams_from_range) { // teams_from_range — 2D Array var teams = []; // convert to list teams_from_range.forEach(function(row) { row.forEach(function(cell) { teams.push(cell); } ); } ); return getTournament_(teams); } function getTournament_(teams) { var start = 0; var … Read more
Without splitting and joining and other string manipulations, =ARRAYFORMULA(FLATTEN(IF(SEQUENCE(ROWS(A2:A4),5),A2:A4))) A2:A4 The range to repeat 5 Number of times to repeat SEQUENCE to create a 2D array of numbers IF to transpose a 1D array A2:A4 to a 2D array of size equal to the SEQUENCE array created FLATTEN the 2D to 1D array. A1 Name … Read more
To achieve the result you’re looking for, you can use BYROW to supply the argument once per array: =BYROW({1;5},LAMBDA(row,INDEX(A2:B16,row,2))) BYROW sends the array argument provided once per row to the function inside LAMBDA. As a general formula, =BYROW(range, LAMBDA(row, your_formula(row))) If you want to send two arguments, use MAP instead. =MAP({1;5},{1;2},LAMBDA(arr_1,arr_2,INDEX(A2:B16,arr_1,arr_2))) This will get row … Read more
The limitations described below are because of lambda functions. The first solution can be successfully implemented without lambda: =ARRAYFORMULA(QUERY(BASE(SEQUENCE(PERMUTATIONA(7,7)),7,7),”where not Col1 matches ‘.*((“&JOIN(“)|(“,SEQUENCE(7,1,0)&”.*”&SEQUENCE(7,1,0))&”)).*'”,0)) The trick here is to use regex to find unique elements using query…match. The only problem with this is memory size needed will exceed 10 million for 8 items PERMUTATIONA(8,8). But that … Read more