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 ...
- 1,649
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&...
- 26.2k
4
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 ...
- 26.2k
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.
...
- 3,272
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, ...
- 26.2k
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"&...
- 26.2k
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 ...
- 12.6k
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 ...
- 151
2
votes
Pick the first value in every row that has not been picked yet
I think I figured it out myself. This works for 5 columns, but I could add more by extending the formula.
=if(isna(C12=vlookup(C12,$G$2:G11,1,false)),C12,if(isna(D12=vlookup(D12,$G$2:G11,1,false)),...
- 61
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 ...
- 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)),...
- 31
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 ...
- 17.4k
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>=...
- 26.2k
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" };
...
- 26.2k
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
=...
- 26.2k
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 ...
- 449
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." ...
- 690
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)
- 52.3k
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 ...
- 12.6k
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=...
- 12.6k
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 ...
- 44.6k
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 ...
- 26.2k
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(...
- 26.2k
1
vote
Summing up amounts if one row contains a key within another sheet
Next to the keys on your second spreadsheet, add the following formula (assumes keys are on column A and amount on B)
=SUMIFS(Sheet1!B:B,Sheet1!A:A,"="&Sheet2!A1)
Update: to calculate the sum, ...
- 629
1
vote
Summing up amounts if one row contains a key within another sheet
You probably want something like the below. In the brackets, first part is the logical expression 2nd is what to return if true and last is what to return if false. If it is not "ABC" then is will ...
- 11
Only top scored, non community-wiki answers of a minimum length are eligible