Questions tagged [google-sheets-arrayformula]

Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.

Filter by
Sorted by
Tagged with
1
vote
1answer
12 views

VLOOKUP needs to find value from a list and if its equivalent then

I have a problem where I'm currently using a VLOOKUP function on this sheet. LINK What I wanted to happen is VLOOKUP would search on the List on G column and compare it to B column and if it matches. ...
1
vote
1answer
236 views

Conditional IMPORTRANGE

In Google Sheets, I'm using IMPORTRANGE to pull in data from to a large sheet. One field is a dollar amount. I would like to import the dollar amount and replace any zero amount with a specific ...
1
vote
1answer
32 views

Formula for combining text if there are duplicate numbers

I have a table to combine text with conditions if in column A there is a duplicate number, then in the text in column B combined. The formula that I have made like this: =IF(COUNTIF($A:$A,A2)>1,...
1
vote
3answers
184 views

How to return what is missing from a 2 column list comparing against all possible combinations of 2 single column lists?

Sample data: A B C D E F 1 name handed in students assignments name missing 2 a 1 a 1 b 2 3 b 1 b 2 ...
1
vote
3answers
260 views

How to scan rows for match with ArrayFormula?

Trying to search a row for a given value, but want to make an ARRAYFORMULA so that every time a new row is added the search will be performed on that row. Currently using =IF(COUNTIF($A2:$EI2,"="&...
1
vote
1answer
47 views

Combing two ranges with identical column structure from two different sheets (in same spreadsheet)

I'm doing a simple ticketing system, where I have two sheets, "Form Responses" and "Email Responses" both of which are populated automatically as their names imply (one from a form, one from an email ...
1
vote
1answer
36 views

How to combine dynamic columns in Google Sheets while retaining data in other rows?

I have a Google Form that asks people to fill out their name, gender, and then their size preference for shirts and pants. The form automatically outputs the 2 sizes in separate columns. I want to ...
1
vote
1answer
86 views

Creating a parent ID cell in Google Sheets

I've got a spreadsheet of various categories and I'd like to create a column which gets the parent name of that particular category. They are currently broken down as follows: 01.01 Category 1 01.02 ...
1
vote
1answer
986 views

Pie chart with sub-slices per category in Google Sheets?

I have a sheet that contains a list of books, with metadata about those books. Two columns that exist are the library within which the book is stored, and the condition of the book. I'd like to create ...
1
vote
2answers
40 views

Query using date range doesn't work for specific data

I have a spreadsheet (URL below). I've set up a query to make sure I understand how to do a query with a date range. Then I tried to create a similar query on the "real" data. The "Week 1" worksheet ...
1
vote
1answer
94 views

Apply a formula to each cell in column before computing a sum of the cells

How could I apply a formula to each cell in a column before computing a sum of the cells in Google spreadsheet? In other words, I have a column of cells with numbers. And what I want is to iterate ...
1
vote
1answer
196 views

How to concatenate cells using ARRAYFORMULA across rows and columns?

I have a document that's a recurring CSV import/export. I'm trying to concatenate images separated by ", " and using ARRAYFORMULA to make it all the way down. The problem is, I don't know how many ...
1
vote
1answer
43 views

Populating alternatives in question 2 based on answers in question 1

I have ten people who sell something to companies, and I want to track what they do IN GOOGLE FORMS. Every agent has his own 10 companies that he contacts. So, in Question 1, I will have a drop-down ...
1
vote
2answers
104 views

Create random pairs without duplicates

I try to randomly pair people into unique pairs. I have two columns one with helping values created via Rand() and one that ranks upon these values with =INDEX($A$2:$A$164,RANK(S2,$S$2:$S$164)) ...
1
vote
2answers
38 views

Google Sheets' Query or Filter solution

I have a simple table with columns: A (Donor ID) B (Donation date) C (Donation Amount) I need to extract the following report: For each donor ID most recent donation date and amount donated ...
1
vote
2answers
27 views

Perform operation on specific column in filtered range, right after filtering

I have this formula in E2: =FILTER(A1:C20,B1:B20=2) and need to perform substitute like: spreadsheet here I'm hoping to do the formula in a single cell, without additional columns.
1
vote
1answer
56 views

Formula for filtering all duplicate data pairs in Google Sheets

I'm making a table to check duplicate data. Where data that wants to be duplicated is in the "Sampel1" column and the duplicate checking results are in the "Status" column. The formula that I have ...
1
vote
1answer
566 views

Get URL from web page using IMPORTXML

Though I am new to web scraping, but I have a hand on experience of web scraping using VBA (Excel) I am facing the following problem: I have tried all methods (ImportXML, ImportHTMP, ImportData, etc.)...
1
vote
1answer
17 views

LOOKUP for data from other sheet

So I have a drop-down menu in order sheet and I have an info sheet. In the info sheet, I have 2 columns, column A has the customer name, column B has the customer e-mail. So what I'd like to do is, ...
1
vote
1answer
32 views

How to join VLOOKUP results of multiple sheets into one cell

I am trying to use TEXTJOIN across multiple sheets, IF a cell is is the same I would like it to return the value from another cell in all sheets that are associated with it. In this example, I am ...
1
vote
0answers
33 views

How to create a user defined function that maps one value to another in Google Sheets

I would like to create a mapping function that takes as an input 3 arrays of the same length and a double between 0 and 1. Array #1: Each element is the minimum value of the nth bin. Array #2: Each ...
1
vote
1answer
1k views

Dynamic updating referenced cell from another workbook after adding/deleting rows using IMPORTRANGE - use ARRAY or VLOOKUP?

I add/deleted/move rows frequently in my workbooks. When I add/remove rows from Spreadsheet#1 ("S1"), I need the referenced cell in Spreadsheet#2 ("S2") to dynamically update so it still references ...
1
vote
2answers
40 views

Confirm that values in a column are alternating

I have a column, call it A, of around 1500 cells. Each cell contains either "a" or "b". For the most part, the As and Bs are alternating, but sometimes there are repeating values. An example of A1:...
1
vote
1answer
835 views

Compare two lists and produce a list of names not duplicated

I have one list of total available people and the second list of people who have been assigned. I would like to auto-populate a third list of the people (from the first list) who have not been ...
1
vote
2answers
11k views

Google Sheets formula for “if contains” from range

I try to figure out how to get a value based on "if some word is in a range". So formulas from (Google Sheets formula for "if contains") works: =IF(ISTEXT(REGEXEXTRACT( A1, "sites")), 1,0) ...
1
vote
2answers
203 views

Is it possibile to concatenate with bidimensional arrayformula?

I have a table like this: +----------+----------+----------+----------+ | Photo1-1 | Photo1-2 | Photo1-3 | | +----------+----------+----------+----------+ | Photo2-1 | Photo2-2 | | ...
1
vote
1answer
24 views

How to show which column has the first amount and count columns to reach a sum amount

I am working on a Google sheet that shows a sum that has been advanced to a customer and then shows the income per month the customer receives. We will retain 100% of the income until the amount that ...
1
vote
0answers
20 views

Multiple IF/And as array in Google Sheets [duplicate]

I am attempting to create an array that will compare the contents of two cells and return a resultant text to a third cell. I am able to make it work as a formula in a single cell, but when I attempt ...
1
vote
1answer
456 views

Range notation and current row

I wonder how does work the following and is it documented somewhere? I have the sheet: A B ----------------- 1 2 3 =$A:$A 4 5 =$A:$A For column B (row 3 and 5) I see ...
1
vote
0answers
73 views

Function always parses column in first row but would like it to repeat

I am trying to make a function that repeats in each row of a column that fetches the URL in the first column, does an xpath query and gets an image. Currently its all working other than the fact it ...
1
vote
1answer
31 views

How to Iterate between colunns and rows?

I am running the following forumula: =IF(INDEX(C$2:$2, 1, ROW(1:1)),CONCATENATE(B$2,"-",INDEX(C$1:$1, 1, ROW(1:1)))) On the following table Basically, If a value is present in column C2, I am ...
1
vote
2answers
24 views

Reverse subtraction if less than zero (negative)

I have a simple Google spreadsheet that Takes the estimated cost for a service provided and compares it to the actual cost of the service. the equation I have in the Q column is =K4-P4. When the ...
1
vote
2answers
34 views

Google Sheets' Duration Input issue

We have been using Apple Numbers which seems to have this duration thing figured out a little better than Google, (either that or I'm missing something). In Apple Numbers, if I type into a cell with a ...
0
votes
5answers
35 views

Layperson's grid to normalized table

Introduction  (too long, don't read) I have a data-arrangement problem. I'm now up to three qualifying files from coworkers, and for that matter, I've run into a couple of questions here on SE ...
0
votes
3answers
8k views

Google Sheets QUERY won't display cell text if other cells have numbers

I'm not sure what is happening in my Google Sheet query and could really use some help. I have a quick screencast that demonstrates and explains the issue. Basically, I'm using a query to pull data ...
0
votes
3answers
2k views

Numeric before query but query results are not a numbers

I have a column of values, let's say column A, that if I do =isnumber on any of the column data it returns TRUE. Now after I =query and select A, the query gives me the right values, but the type ...
0
votes
1answer
18 views

How to merge data from multiple Google Sheets with formula?

I have 10 columns in 10 Google sheets and in all sheets are the data labeled like Month | Total number of winter/summer schools attended | Sr. No. | Title | Date | Venue | Organisation | Number of ...
0
votes
3answers
333 views

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

I have been looking for a way to sort this information. I need to sort these items in this order: BK, BB, BV, BIV, BIII, BII, BR. I was trying to make a Google Script to get it, but I could only ...
0
votes
1answer
107 views

Use of IMPORTRANGE to juxtapose variable-length lists

Let's say I have two Google sheets, Doc1 and Doc2, both having only one sheet featuring two lists with the same schema, say two columns Name and Age. The lists have an unknown number of entries, but ...
0
votes
2answers
31 views

How to make a formula so it repeats istself indefinetly

I have a formula that does exactly what I need except for that, for now, I need to manually expand the range for which it applies. What do I need to add so I only have this in one cell and it ...
0
votes
2answers
113 views

How to round float to bigger integer?

In Google Sheet, we can use ROUND() to round float to the nearest. like 5.2 = 5 5.5 = 6. What if I want it: 5.2 = 6 5.5 = 6. If the number exceeds by a decimal, then rounded off to +1. Any idea?
0
votes
2answers
496 views

Google Sheets - Fetch random value from array for each new line created with form

I have a Google Form that fills up a new line with values in Sheet1!, column A and column B. I am trying to create an =ARRAYFORMULA function in column C, which will populate the C cell in each new ...
0
votes
1answer
390 views

Vlookup across multiple columns

This question is related to the same project in this question, but I'm now at a later step. I have a Google Sheet with a variety of German translations of a large set of English words. Using a ...
0
votes
1answer
26 views

Is there any way to dynamically modify dates based on number of days something would take?

Imagine you're creating a Google Sheet to track all of your tasks, how long they take, and their deadlines: Task |Number of days| Start Date | Date Done Buy plane tickets | ...
0
votes
1answer
15 views

Pull data when cell value is selected

I am Trying to pull data from part of a sheet to another part depending on if dirt level 22 value is filled in. Please see attached Picture, basically, the bottom section pulls data from rows above ...
0
votes
1answer
21 views

Importrange (or maybe another part of formula) behaves differently after a short time period

I am using this formula to look for data in column F related to data in column A between two spreadsheets. =ARRAYFORMULA(IFERROR(VLOOKUP(A2:A; {IMPORTRANGE("spreadsheetID";"sheet1!A4:A")\ IMPORTRANGE(...
0
votes
1answer
19 views

Google Sheets populate X cells with formulas based on single cell containing value X?

Google sheets dynamically populate cells based on single cell value. I don't know what the term would be called and a bunch of searching hasn't resulted in any lead on what it would be called. What I ...
0
votes
1answer
14 views

Percentage from subtraction

I currently have an issue with a statistics sheet for a sports team. If I have a total amount of practices, with the number of days missed subtracted for each player. How do I turn that into a ...
0
votes
1answer
8 views

How do I copy over a value from another worksheet depending on the adjacent column?

I have two checklists of the same items in a different order in two separate sheets, and I want to only update one. Basically, I have two pages that look like this And I want the? to be automatically ...
0
votes
1answer
23 views

Importrange doesn't import data from extensive sheets

The formula works on lower column number that I need to get data from but nothing is happening when I need to import data from columns with a higher number. Following formula works (data imported ...