Skip to main content
Share Your Experience: Take the 2024 Developer Survey
5 votes
Accepted

In ARRAY_LITERAL, an Array Literal was missing values for one or more rows

...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 ...
user0's user avatar
  • 26.7k
4 votes

multiple IF statements with between number ranges alternative

CHOOSE() with MATCH() =ARRAYFORMULA(IFERROR(CHOOSE(MATCH(B2:B, {1, 11, 21, 51, 101, 151, 201, 301, 501, 801, 1001, 1223, 1569}), "01-10", "11-20", "21-50", "51-100&...
user0's user avatar
  • 26.7k
4 votes
Accepted

Use an array/list as VLOOKUP search key

Let's say your names are in H1:H4, and your data is in J1:K6. Then to obtain the sum you want use the formula: =sum(arrayformula(vlookup(H1:H4, J1:K6, 2, false))) The key to the solution is ...
ttarchala's user avatar
  • 1,679
3 votes
Accepted

How to update VLOOKUP results in Google Sheets to most recent input from Google form?

Try this:B3: =ARRAYFORMULA(IFERROR(VLOOKUP($A$3:$A,SORT(FILTER({'Form Responses 1'!$B$2:$F$7,'Form Responses 1'!$A$2:$A$7},'Form Responses 1'!C2:C7<>""),6,0),B2,0))) Drag fill to the right. ...
TheMaster's user avatar
  • 3,342
3 votes
Accepted

Google Sheets Query with Date and Time

=ARRAYFORMULA(VLOOKUP(TO_DATE(QUERY(VALUE(QUERY(FormResponses!A2:A, "select *", 0)), "select Col1 where Col1 > "&VALUE(J2), 0)), {VALUE(FormResponses!A2:A), FormResponses!B2:G}, {1, 2, 3, ...
user0's user avatar
  • 26.7k
3 votes
Accepted

Add missing dates to list of dates with values

paste this into C1 cell: =ARRAYFORMULA({TO_DATE(ROW( INDIRECT("A"&DATEVALUE(MIN(A:A))&":B"&DATEVALUE(MAX(A:A))))), IFERROR(VLOOKUP(TO_DATE(ROW( INDIRECT("A"&...
user0's user avatar
  • 26.7k
3 votes
Accepted

How do I populate cells (with formulas) from one google sheet into another while using one as a search key for the other? If C=This, State D

You can try a QUERY formula like: =QUERY(IMPORTRANGE("1jD9MgFRnIi-44GaqsS5XAdhcV-Vg36eXnAXzkFh5xnM","'Form Responses 1'!C2:D"), "select Col2 where Col1='"& B3&"'") The formula will ...
marikamitsos's user avatar
  • 12.9k
3 votes

Categorize text in Google Sheets based on matching partial text in the Category index

Thanks to @Ruben for the answer. Eventually we decided to take a simpler approach. Just to check the regexp of the search string in VLOOKUP instead, and have those substrings in the key indexes in ...
PKHunter's user avatar
  • 151
2 votes

multiple IF statements with between number ranges alternative

ARRAYFORMULA() with IF() =ARRAYFORMULA(IFERROR( IF((B2:B>=1) * (B2:B<11), "01-10", IF((B2:B>=11) * (B2:B<21), "11-20", IF((B2:B>=21) * (B2:B<51), "21-50", IF((B2:B>=...
user0's user avatar
  • 26.7k
2 votes

multiple IF statements with between number ranges alternative

standalone single cell solution using VLOOKUP() =IFERROR(ARRAYFORMULA(VLOOKUP(B2:B, {{1, "01-10" }; {11, "11-20" }; ...
user0's user avatar
  • 26.7k
2 votes

Vlookup across multiple columns

I ended up doing this the not-so-smart way: instead of making VLOOKUP happen across multiple columns, I copied all of the English terms multiple times and stacked them vertically in one column, then ...
gesher's user avatar
  • 165
2 votes

Pull a value for the following month in Google Sheets

Nevermind, I found an answer online after much searching. Here's what the formula looks like on my sheet, if anyone else is interested: =VLOOKUP(INDEX($A$5:$A$41,MATCH(TRUE,$A$5:$A$41>=TODAY(),0)),...
Jarad's user avatar
  • 41
2 votes

VLOOKUP using cell reference with IMPORTRANGE gives error

VLOOKUP is format sensitive. It neither 'matches' text to number nor number to text, even where on screen the appearance is identical. This is a standard gotcha, though unfortunately not mentioned ...
pnuts's user avatar
  • 17.6k
2 votes

Cartesian Product with constraints

step 1: convert the dataset to an actual value holder =ARRAYFORMULA(IF(C3:J5="x", $C$2:$J$2, )) step 2: multiply combinations between P and F values drag formula over to the E column =...
user0's user avatar
  • 26.7k
2 votes

Conditional formatting rule that compares against a range of values

If you have your data in, say, A2:A200 (assuming a header row), and you holidays are stored in G2:G20, then set a Conditional Formatting rule with: Apply to range: A2:A200 Custom formula is: =...
sancho.s ReinstateMonicaCellio's user avatar
2 votes
Accepted

How Can I Sort a Data Range in Google Sheets Based on an Data Range?

Create and fill sort order numbers (column D on the screenshot) Enter the following formula to cell F2: =SORT(ARRAYFORMULA({$A2:A , IF($A2:A="", "", VLOOKUP($A2:A,$C$2:$D$8,2,FALSE))}),2,TRUE) It ...
Andrej Adamenko's user avatar
2 votes
Accepted

Google form wrongly delete rows in second sheet

When you move formulas around, or the cells they pull values from, Sheets does its best to intelligently preserve or update those links. You said "I'm not deleting rows myself in the second sheet." ...
Joel Reid's user avatar
  • 700
2 votes

Value of Cell A1 could point to values of different cells

Use VLOOKUP(). Assuming your lookup table is in H1:I50, you'd put the following in B1: =VLOOKUP(A1,H1:I150,2,FALSE)
ale's user avatar
  • 52.7k
2 votes
Accepted

How to find, out of many days, 1 day that is most profitable on Google Sheets?

You need to use the QUERY and VLOOKUP functions: In cell D2 place the following formula nad drag it down to cell D6 =QUERY($A$2:$C,"select sum(B) where A='"&$C2&"' label sum(B)''") In cell ...
marikamitsos's user avatar
  • 12.9k
2 votes
Accepted

Formula to find the cell in column C where a value in column A = a value in column E

EDIT (following OP's edited question) Please use the following in cell D2: =ArrayFormula(IFERROR(VLOOKUP(E2:E,$A$2:$C,3,0))) Original answer Please use the following formula: =ArrayFormula(IF(A2:A13=...
marikamitsos's user avatar
  • 12.9k
2 votes

Categorize text in Google Sheets based on matching partial text in the Category index

In this case VLOOKUP can't be used because some of the categories kyes are contained within the data to be matched, by the other hand, the case is more complex than the description included in the ...
Rubén - Volunteer Moderator -'s user avatar
1 vote
Accepted

SUM columns from other sheets and group by month, but also show the individual sheets values before doing SUM

one cell solution: ={{QUERY({Sheet1!E3:F; Sheet2!E3:F; Sheet3!E3:F; Sheet4!E3:F; Sheet5!E3:F}, "select Col1, sum(Col2) where Col1 is not null group by Col1 label Col1 'Month', sum(...
user0's user avatar
  • 26.7k
1 vote

SUM columns from other sheets and group by month, but also show the individual sheets values before doing SUM

={{QUERY({Sheet1!I2:I, Sheet1!L2:L; Sheet2!I2:I, Sheet2!L2:L}, "select Col1, sum(Col2) where Col1 is not null group by Col1 label Col1 'Month', sum(Col2)'Sum'", 0), {"Sheet1"; ...
user0's user avatar
  • 26.7k
1 vote

SUM columns from other sheets and group by month, but also show the individual sheets values before doing SUM

={QUERY({Sheet1!I2:I, Sheet1!L2:L; Sheet2!I2:I, Sheet2!L2:L}, "select Col1, sum(Col2) where Col1 is not null group by Col1 label Col1 'Month', sum(Col2)'Sum'", 0), {"Sheet1"; QUERY(...
user0's user avatar
  • 26.7k
1 vote

multiple IF statements with between number ranges alternative

=query() is useful for this. Split the "rule set" into lower and upper limits, and create a named range to contain all three resulting columns (here named "rule_set"): To find ...
Stephen Wheeler's user avatar
1 vote

In Google Sheets, I'm trying to create a drop down that reads from 2 columns but returns from 1 column

Data validation drop drowns in Google Sheets can't pull data from two columns. One solution is the one that you already figured out, join the values of those columns and the use those joined values.
Rubén - Volunteer Moderator -'s user avatar
1 vote
Accepted

multiple IF statements with between number ranges alternative

VLOOKUP() =IFERROR(ARRAYFORMULA(VLOOKUP(B2:B; {QUERY(IFERROR(ARRAYFORMULA(VALUE( REGEXEXTRACT(Sheet2!A2:A; "(\d+)"))); ); "SELECT Col1")\ QUERY(Sheet2!A2:A; "SELECT A")}; 2)); ) where Sheet2 ...
user0's user avatar
  • 26.7k
1 vote
Accepted

Summing up amounts if one row contains a key within another sheet

=SUM(IFERROR(ARRAYFORMULA(IF(LEN(A2:A); VLOOKUP(A2:A; Sheet1!A2:B; 2; 0); )); )) VLOOKUP searches for A2:A in Sheet1!A2:B and returns 2nd column from Sheet1!A2:B as it is eg. 0 for not sorted LEN(...
user0's user avatar
  • 26.7k

Only top scored, non community-wiki answers of a minimum length are eligible