# Tag Info

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 ...

### 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&...
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 ...
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. ...
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, ...
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"&...
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 ...

### 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 ...

### 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)),...

### 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 ...

### 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)),...

### 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 ...

### 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>=...

### 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" }; ...

### 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 =...
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 ...
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." ...

### 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)
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 ...
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=...

### 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 ...
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 ...
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(...
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, ...
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 ...

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