dudes who copy-pasted INDIRECT function into Google Sheets completely failed to understand the potential of it and therefore they made zero effort to improve upon it and cover the obvious logic which is crucial in this age of arrays. in other words, INDIRECT can’t intake more than one array: =INDIRECT(“Sheet1!A:B”; “Sheet2!A:B”) nor convert an arrayed … Read more
Number of columns in both sheets must be the same: Try ={Sheet1!A2:D10;Sheet2!A2:D10}
As stated previously, there is no native function which can do what you want in a single cell. If you absolutely cannot use VBA, then you could use a helper column (can hide the column if preferred) and then have the cell where you want the result simply show the last cell of the helper … Read more
You dont need single quotes ‘ around Sheet1. Try me.getRange(‘B8’) .setFormula(‘=arrayformula(concatenate(filter(Sheet1!E2:E,Sheet1!E2:E<>””) & “, “))”‘); If you still need it, you need to escape it with backlashes like this \’: me.getRange(‘B8’) .setFormula(‘=arrayformula(concatenate(filter(\’Sheet1\’!E2:E,\’Sheet1\’!E2:E<>””) & “, “))”‘); Reference: String ยง Escape notation
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\.)?([^\/]+)”)))
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
…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
=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)”))
AND doesn’t work that way with Array formulae because it ANDs the whole array together in the top left cell, regardless of number of dimensions. I.e. it checks if “”>”” which is FALSE, ANDed with anything it will return FALSE for the top left cell, that result is carried down. You can use multiplication of … Read more
Similar answer to caligari’s answer, but we can tidy it up by just specifying the full column range: =INDEX(G2:G, COUNT(G2:G))