IFS Function in Google Sheets

The IFS function in Google Sheets is used to test multiple conditions and outputs a value specified by the first test that evaluates to true.

It’s akin to a nested IF formula, although it’s not exactly the same. However, if you find yourself creating a nested IF formula then it’s probably easier to use this IFS function.

Example IFS Function in Google Sheets

Suppose you have a set of student exam scores and you want to assign different grades to the students:

Simple IFS Function In Google Sheets

In this scenario, you want to put students into three groups: i) those who score below 50 failed the exam, ii) those with scores between 50 and 79 passed the exam, and iii) students who scored 80 or above passed with distinction.

The IFS Function to do this is:

=IFS(B2<50,"Fail",B2<80,"Pass",B2>=80,"Pass with distinction")

The IFS function consists of pairs of arguments: a condition to test and a value.

If the conditional test is true, the value is displayed and the function stops. If not, and the test is false, the function tries the next test/value pair.

Consider row 2 of the example above, where Bob has a score of 70 in cell B2.

The first logical test and value pair is:

B2<50,"Fail"

The function takes the value of 70 in cell B2 and compares it to the value of 50 to see if it’s less. This is false, so the “Fail” output is not displayed.

Instead, the IFS function in Google Sheets moves to the second logical test and value pair:

B2<80,"Pass"

This time the logical test – is 70 less than 80? – evaluates to true, so the function displays “Pass”.

The IFS formula never reaches the third test/value pair.

The result of the IFS function in Google Sheets is an output that classifies the students:

IFS Function In Google Sheets Exam Grade

You could modify this IFS formula to assign grades “A”, “B”, “C” etc. instead, based on bands.

Note, it can also be done with a nested IF function. This gives the same result as the IFS function, but is more complex to understand:

=IF(B2<50,"Fail",IF(B2<80,"Pass","Pass with distinction"))

IFS Function in Google Sheets: Syntax

=IFS(condition1, value1, [condition2, value2, …])

It takes a minimum of two arguments:

condition1

This is a logical test that evaluates to a TRUE or FALSE value.

For example A1 > 10 or ISBLANK(A1)

value1

If condition1 is TRUE, then the IFS function will output this value.

condition2, value2

These are optional pairs of logical tests and values. If condition 1 is FALSE, the IFS moves on to test condition 2, then 3, then 4 etc.

IFS Function Notes

  • The conditions and values always come in a pair, so the IFS always has an even number of arugments.
  • The function reads the test/value pairs from left to right. It always starts with the leftmost argument as the first logical test.
  • There is no default value to display should all the conditions fail. The IFS function will then output an #N/A! error.
  • However, you can create a default fallback value by adding a penultimate argument TRUE (the logical test) and some value (the fallback value). This is illustrated in the bank account example below.

IFS function template

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy…

If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you click the link and open in an Incognito window you’ll be able to see it.

You can also read about it in the Google documentation.

IFS Function Account Balance Example

Suppose you have a list of corporate bank balances and a table of interest rates, and you want to add the correct rate against the account balance so you can calculate the interest.

Account IFS Function Example

One way to do this is to use this IFS function in cell B2:

=IFS(A2<50001,0.5%,A2<100001,0.75%,A2<250001,1.25%,A2>=250000,1.75%)

The first test compares the value in cell A2 to see if it’s less than $50,001, which means the account balance is in the $0 – $50,000 tier, so the formula returns a value of 0.5%.

If this first test fails, it tries the second logical test. If that fails, it tries the third logical test, etc.

As it’s written above, the interest rates are hardcoded in the formula, which is generally bad practice (because it’s hard to make changes and easy to make mistakes).

A better solution is to link the output values to the interest rate table, as shown in this version of the formula:

=IFS(A2<50001,$E$2,A2<100001,$E$3,A2<250001,$E$4,A2>=250000,$E$5)

(Exercise for readers: take this a step further and link the bounds (50001, 100001 etc.) to the interest rate table instead of hard coding them.)

The output looks like this:

Account IFS Function Solution

Note: The final logical test can be replaced with the word TRUE, as the catch-all when none of the other conditions are met:

=IFS(A2<50001,0.5%,A2<100001,0.75%,A2<250001,1.25%,TRUE,1.75%)

Alternative Solution

Note that this account example can also be solved with the VLOOKUP function using a TRUE value as the final argument.

This account example is covered in Day 6 of my free Advanced Formulas 30 Day Challenge course.

Advanced IFS Function in Google Sheets

This is somewhat contrived but will give you an idea of what’s possible with the IFS function.

In this scenario, imagine that two parties of buyers have viewed some properties.

The agent wants to record each party’s preferences. He remembers:

“Party 1 liked apartments under $200k”

“Party 2 liked houses or townhouses”

There are various ways you could solve this, including simply recording the preferences manually in a Sheet, but let’s see an IFS formula that does it automatically.

Here’s the data table:

Advanced IFS Formula Data

And here’s the formula in cell D2 that can be dragged down the column and across the row to fill in the peferences:

=IFS(ISBLANK($C3),"",AND(D$1="Party 1",$B3="Apartment",$C3<200000),"Yes", AND(D$1="Party 2", OR($B3="House",$B3="Townhouse")), "Yes",TRUE,"Not interested")

The test/value pairs are grouped as follows:

Test # Test In Formula Output
1 Is cell C3 blank? ISBLANK($C3) ""
2 Is it party 1 and Apartment and less than $200k? AND(D$1 = "Party 1",$B3 = "Apartment",$C3 < 200000) Yes
3 Is it party 2 and house or townhouse? AND(D$1 = "Party 2",OR($B3 = "House",$B3 = "Townhouse")) Yes
4 Catch all when conditions 1 – 3 not true TRUE Not interested

You’ll notice that test/value pairs 2 and 3 show how to use a nested AND function and a nested OR function to combine conditions.

The output of this formula is:

Advanced IFS Formula

SPLIT Function in Google Sheets

The SPLIT function in Google Sheets is used to divide a text string (or value) around a given delimiter, and output the separate pieces into their own cells.

SPLIT Function Examples

Let’s see a simple example using SPLIT to separate a list of names in cell A1:

Split Function Name Example

This simple SPLIT formula will separate these names, using the comma as the separator:

=SPLIT(A1,",")

The result is 5 cells, each containing a name. Note that one cell looks blank because the text string in cell A1 has two adjacent commas with a space between them. The “space” is interpreted in the same way as the names and contained in the output:

Split Function Name Example

Now watch what happens if we include a space in the delimiter, i.e. ", "

=SPLIT(A1,", ")

The function splits on the comma "," and on the space " ", so the name “Mary Jo” split in two:

Undesirable behavior with SPLIT formula

This is probably not the desired behavior.

The third argument is an optional TRUE or FALSE that determines whether SPLIT considers each individual character of the delimiter (TRUE) or only the full combination as the separator to use (FALSE).

In our example, adding FALSE ensures that it only considers the combined comma/space string as the delimiter:

=SPLIT(A1,", ", FALSE)

And the output looks like this:

Split names in Google Sheets

There is a fourth argument too, which is optional and takes a TRUE/FALS value. It determines whether to remove blank cells or not in the output.

To illustrate this, consider this arrangement of data separated by semi-colons. Note the presence of two adjacent semi-colons with no data between them:

Split Formula Fourth Argument

The fourth argument determines whether to show or hide the blank cell caused by the two adjacent semi-colons.

To keep the blank cells, add FALSE as the fourth argument:

=SPLIT(A2,",", TRUE, FALSE)

SPLIT Function in Google Sheets: Syntax

=SPLIT(text, delimiter, [split_by_each], [remove_empty_text])

It takes 4 arguments:

text

This is the text string or value in the cell that you want to split. It can also be a reference to a cell with a value in, or even the output of a nested formula, provided that output is a string or value and not an array.

delimiter

The character or characters used to split the text. Note that by default, all characters are used in the division. So a delimiter of “the” will split a text string on “the”, “he”,”t”,”h”,”e” etc.

This behavior can be controlled by the next argument:

split_by_each

This argument is optional and takes a TRUE or FALSE value only. If omitted, it’s assumed to be TRUE.

The TRUE behavior splits by individual characters in the delimiter and any combination of them. The FALSE behavior does not consider the characters separately, and only divides on the entire delimiter.

remove_empty_text

The fourth and final argument is optional and takes a TRUE or FALSE value only. If omitted, it’s assumed to be TRUE.

It specifies what to do with empty results in the SPLIT output. For example, suppose you’re splitting a text string with a "," and your string looks like this: “Ben,Bob,,Jenny,Anna”

Between the names Bob and Jenny are two commas with no value between them.

Setting this final argument of the SPLIT function to FALSE results in a blank cell in the output. If this fourth argument is omitted or set to TRUE, then the blank cell is removed and “Bob” and “Jenny” appear in adjacent cells.

SPLIT Function Notes

  • Delimiters in SPLIT are case sensitive. So “t” only splits on lower-case t’s in the text
  • The SPLIT function requires enough “space” for its output. If it splits a text string into 4 elements then it requires 4 cells (including the one the formula is in) on that row to expand into. If there is already data in any of these cells, it does NOT overwrite it but instead shows a #REF! error message
  • You can input a range as the first argument to the SPLIT function, but it requires an Array Formula wrapper to work
  • The output from the SPLIT function is an array of values that can be passed as the input into another formula, which may require the use of the Array Formula

Alternative Split Method

There’s an alternative way to split values in a Google Sheet.

Under the Data menu, there’s a feature called “Split text to columns” which will separate single columns into multiple columns, based on the delimiter you specify.

It’s a quick and easy way to split text.

Note that it overwrites existing data in your Sheet if the split columns overlap with any existing data.

Split function alternative

SPLIT Function Template

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy…

If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you click the link and open in an Incognito window you’ll be able to see it.

You can also read about it in the Google documentation.


Advanced Examples of the SPLIT Formula in Google Sheets

Extract The N-th Word In A Sentence

You can wrap the SPLIT function output with an INDEX function to extract the word at a given position in a sentence. E.g. to extract the 4th word, use this formula:

=INDEX(SPLIT(A1," "),4)

Extract Nth Word in Google Sheets

If you combine this with a drop down menu using data validation, you can create a word extractor:

extract Nth Word Data Validation

Alphabetize Comma-Separated Strings With The SPLIT Function in Google Sheets

Suppose you have a list of words in a single cell that you want to sort alphabetically:

Formula Challenge 3

This formula will rearrange that list alphabetically:

=JOIN(",",SORT(TRANSPOSE(SPLIT(A1,","))))

It splits the string of words, applies the TRANSPOSE function to convert into a column so it can be sorted using the SORT function, and then recombines it with the JOIN function.

Formula Challenge 3 Solution

Read more in Formula Challenge #3: Alphabetize Comma-Separated Strings.

Splitting and Concatenating Strings

The SPLIT is useful in more advanced formulas as a way to divide an array into separate elements, do some work on those elements (e.g. sort them) before recombining them with another function, like the JOIN function.

For example, this formula will add surnames to a list of first names in a cell:

=ArrayFormula(TRIM(JOIN(", ",SPLIT(A2,", ")&" Smith")))

which looks like this in your Google Sheet:

Split Function In Google Sheets To Add Surnames

Using the onion framework to analyze this formula, starting from the innermost function and working out, it splits the text string, joins on the surname “Smith”, trims the excess trailing space and finally outputs an array by using the Array Formula.

Advanced SPLIT Array Techniques

The SPLIT function in Google Sheets is used in a number of the complex IMPORT formulas for retrieving social media statistics into your Google Sheet.

The SPLIT function was also used in this exceedingly wacky unpivot formula in Google Sheets (see Solution 4):

=ArrayFormula({"Customer","Product","Value";
QUERY(IFERROR(SPLIT(TRIM(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"🐠"&Sheet1!B1:1&"🐠"&Sheet1!B2:Z&"🌢", )), , 500000)), , 500000)),"🌢"))),"🐠"),""),"SELECT Col2, Col1, Col3 ORDER BY Col2 OFFSET 1",0)})

All in all, SPLIT is a useful function!

REPT Function in Google Sheets

The REPT function in Google Sheets is used to repeat an expression a set number of times.

The REPT formula to repeat “Go! ” three times is:

=REPT("Go! ", 3)

Notice the additional space added after the exclamation point, so that there is a space between the repeated values in the output.

More REPT Function Examples

Rept formula examples in Google Sheets

Column A contains the values you want to repeat.

Column B contains the number of repetitions.

The formula is then very straightforward:

=REPT(A2,B2)

As you can see, for each row the value in A is repeated per the number in column B and joined into one long string.

REPT Function in Google Sheets: Syntax

The REPT function takes two arguments and both are required.

=REPT(text_to_repeat, number_of_repetitions)

text_to_repeat

This is a text string or cell reference that you want to repeat.

number_of_repetitions

This is a positive integer indicating how many times you want to repeat the input text.

Notes on using the REPT function

  • The return value (the output of the REPT function) is a string value in a single cell
  • Setting the number of repetitions to 0 results in a cell containing a blank string (not a true blank cell though. ISBLANK will still give FALSE)
  • Setting the number of repetitions to -1 results in a #VALUE! error
  • If you want spaces between the repeated text strings, you must add that as the final character of the input string (as shown in the "Go! " example above). This results in a final trailing space, which can be removed with the TRIM function if required
  • The number_of_repetitions can’t exceed the character limit of a cell: 32,000 characters. If it does, you’ll see a #VALUE! error
  • Ranges can be repeated by wrapping the REPT function in an Array Formula

REPT function template

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy…

If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you click the link and open in an Incognito window you’ll be able to see it.

You can also read about it in the Google documentation.

Using The REPT Function For Repeated Values In Separate Cells

What if you don’t want to create a long string of repeated values in a single cell, but instead want to output the repeated value across a range of cells, such that each value is in its own cell?

That’s possible with this formula, assuming the value is in cell A1 and the number of repetitions is in cell B1:

=TRANSPOSE(SPLIT(REPT(A1&"β™•",B1),"β™•"))

This particular formula outputs 10 values “text” in a column. (You can remove the transpose if you want the output across a row.)

The Queen symbol “β™•” is added to the end of the repeated value to act as a unique value used by the SPLIT function to divide the repeated string.

REPT formula to output values in separate cell

Note: you can also achieve this affect with the SEQUENCE function:

=ArrayFormula(TEXT(SEQUENCE(B1),"")&A1)

This particular variation uses the SEQUENCE function to output an array 1,2,3,…10. The TEXT function then “tricks” all of these to become empty strings. We then concatenate the repeated value from cell A1 onto this string. Finally, the Array Formula ensures that the output is an array.

Repeated Images with REPT Formula in Google Sheets

This REPT formula will repeat the specified image across a row. It uses the same Queen symbol “β™•” trick as the previous formula.

=ArrayFormula(IMAGE(SPLIT(REPT("https://www.google.com/favicon.ico"&"β™•",5),"β™•")))

The output looks like this:

Repeated image using the REPT function in Google Sheets

Using REPT As Logical Formulas

The REPT function can be used instead of IF formulas in specific situations.

It’s quite a clever trick that works because TRUE is equivalent to the number 1, and FALSE to the number 0, in formulas.

So, by putting a logical test, which evaluates to TRUE or FALSE, equivalent 1 or 0, in the number of repetitions argument, we can either show 1 value or 0 values using REPT.

For example, this formula identifies values over $1,000:

=REPT("Over $1,000",A1 > 1000)

And the output looks like this:

Rept Function Logical Formula

This formula works because the logical test checks if the value in cell A1 is greater than $1,000. If the result is TRUE, it’s interpreted as a value of 1, which then repeats the text in the REPT formula once. So the output is “Over $1,000”.

If the value is less than or equal to $1,000, then the test evaluates to FALSE, equivalent to 0 input for the REPT formula, so the output is a blank string in the cell.

This technique can be taken a step further to check multiple conditions are TRUE.

For example, the following formula checks whether the value is over $1,000 AND comes from Client A:

=REPT("Over $1,000 and Client A",(B1>1000)*(A1="Client A"))

The two logical tests evaluate to TRUE or FALSE, which are interpreted as 1 or 0 by the formula.

When the value is greater than $1,000 and from Client A, it’s equivalent to:

TRUE * TRUE = 1 * 1 = 1

In which case, the formula outputs the string “Over $1,000 and Client A”

Multiplication using “*” as shown above is for the AND case, where both conditions are true.

To do the OR case, you use addition “+”. If either value is TRUE and the other FALSE, it’s equivalent to 1 + 0 = 1. When both are true it’s equivalent to 1 + 1 = 2, so the REPT would repeat the value twice which we don’t want.

To fix this, wrap it with a MIN formula to set to 1 if the value is above 1.

=REPT("Over $1,000 and Client A",MIN((B1>1000)+(A1="Client A"),1)

In Cell Charts with REPT Function in Google Sheets

Bar chart with REPT Function

In cell bar chart With Rept Function

The formula to do this is:

=REPT(CHAR(10074),A1)

You can use any character as the repeated symbol, for example you could use tacos with CHAR(127790):

tacos in Google Sheets

Vertical Bar Chart with REPT function

You can easily extend the technique above to display the bar chart in a single cell, with a vertical orientation, using this formula:

=ArrayFormula(JOIN(CHAR(10),REPT(CHAR(9679),A1:A6)))

which looks like this (using dots as the repeated symbol):

Rept Column Chart

Dynamic Width Bar Charts Using REPT function

This is an interesting, although not particularly useful, formula which uses the CELL function to access the width of a cell and then sets the REPT to repeat the value to match the width of the cell:

Dynamic Width Bar Chart With Rept

The formula to do this is:

=REPT(CHAR(10074),ROUND((A2/MAX($A$2:$A$4))*(CELL("width",$B$1)*1.25),0))

It uses the random number generator and the spreadsheet calculation setting to be “On change and every minute” to force the CELL function to re-calculate the width value each time there is a change.

The 1.25 factor at the end is simply to adjust the largest bar to fit the full width. It depends on the character you use to repeat and how wide it is. Feel free to experiment.

Padding Strings With The REPT Function

REPT can also be used to pad text strings.

With a value in A1, this formula adds a variable number of underscores to the end of the string:

=A1&REPT("_",20-LEN(A1))

If you want to have the text strings to have equal widths you’ll also need to use a font where the letters are equally spaced, like SOURCE CODE PRO.

This approach can also be achieved with numbers, although it’s much better to use custom number formatting, because that preserves the value as type number instead of converting to a string.

PI Function in Google Sheets And Other Fun Ο€ Facts

The PI function in Google Sheets returns the value of the mathematical constant pi (Ο€) to 9 decimal places: 3.141592654

Pi is defined as the ratio of a circle’s circumference to its diameter.

Pi is denoted by the Greek lowercase letter Ο€

PI Function in Google Sheets: Syntax

=PI()

There are no arguments. If you put anything between the brackets the formula will give you an error.

Circle Calculations With The PI Function

If you know the circumference of a circle then you can calculate the diameter and vice versa.

How to calculate the circumference

Suppose you have a diameter of 10 (the units are irrelevant).

Then you calculate the circumference with this formula: the diameter multiplied by pi.

= 10 * PI()

How to calculate the diameter

Now suppose you know the circumference is value 10.

The diameter is calculated as the circumference divided by pi:

= 10 / PI()

How to calculate the area of a circle

The formula for the area of a circle is Ο€ * rΒ² (pi times the radius squared).

To calculate the radius, you must divide the diameter by 2.

So, knowing the diameter d, you can calculate the area of a circle as follows: Ο€ * (d/2)Β²

Taking a diameter of 10, the area is:

= PI() * ( 10 / 2 )^2

for an answer 78.5398

Creating the PI Symbol with the CHAR Function

The amazing CHAR function, which converts numbers into characters per the Unicode table, can output the symbol for pi in your Google Sheet.

Use this formula to output the pi symbol Ο€:

=CHAR(960)

Setting the cell to the Merriweather font gives it the nice rounded appearance shown in this image:

Pi Symbol With Char Function

By the way, this formula:

=CHAR(129383)

will output an image of an entirely different type of pie in your Google Sheet:

Pie in a Google Sheet

PI function template

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy…

If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you click the link and open in an Incognito window you’ll be able to see it.

You can also read about it in the Google documentation.

Approximating Pi in Google Sheets With the Nilakantha Series

The Nilakantha series, named after the 15th century mathematician of the same name, is an infinite series that converges on pi as the number of iterations rises. It converges quickly so it’s a good algorithm to use to compute the digits of pi.

The Nilakantha series is:

Nilakantha Series
Image source: Wikipedia

Let’s build an approximation of pi in a Google Sheet using this series.

Nilakantha Series in Google Sheets With The PI Function

In cell A1, enter this SEQUENCE formula:

=SEQUENCE(1000,1,2,2)

And in B1:

=SEQUENCE(1000,1,3,2)

Then in C1:

=SEQUENCE(1000,1,4,2)

These three formulas give us three columns of numbers with 1,000 digits each. They start with 2, 3 and 4 respectively and increment by 2.

Look closely at the infinite series definition above and you’ll notice that each row represents a denominator for each iteration of the series.

In cell D1, multiply the values together for each row:

=PRODUCT(A1:C1)

Copy this formula down the column.

This formula generates the denominators for the series.

In cell E1, put this product as the denominator to calculate the series values:

= 4 / D1

Next, we need to alternate adding and subtracting these values, which we can do with this formula:

=IF( ISODD( ROW() ), E1 , UMINUS( E1 ) )

This IF formula checks if we’re on an odd numbered row (e.g. row 1) using the ISODD function and, if we are, returns the number unchanged. But if we’re on an even numbered row, it returns the negative value from column E, using the UMINUS function.

Finally we can approximate pi in column G.

In G1, put this formula to start the series:

= 3 + F1

In cell G2, put this formula:

= G1 + F2

and drag this formula all the way to the bottom of the range.

With 1,000 rows, we get a value for pi of 3.1415926533, which is a similar level of accuracy to the PI function.

Here are the first 16 iterations, the remaining rows up to 1,000 can be found in the template linked above in this article:

Nilakantha Series to approximate pi in Google Sheets

Now, you might be wondering how many digits of pi have been calculated…

…well it’s a whopping 31.4 trillion digits!

Obviously, that is not a calculation you can perform in your Google Sheet, but interestingly, it was performed on Google Cloud infrastructure!

Update: The record is now 50 trillion digits, broken by an enthusiast with a self-built server setup. Super impressive!

Read more about the chronology of computing digits of pi.

Getting More Digits of Pi in Google Sheets

Whilst we can’t get 50 trillion digits of pi into a Google Sheet, we can call the Pi Delivery API and retrieve more digits.

Here’s the code to request the first 1,000 digits of pi and display them in a Google Sheet as a text string (because numbers get truncated):

/** 
 * function to call the Pi API
 * https://pi.delivery/#apipi_get
 */
function getPI() {
  
  // endpoint
  const root = 'https://api.pi.delivery/v1/pi';
  
  // start point and number of digits
  const startDigit = 0;
  const numDigits = 1000; // max request is 1000 digits in 1 call

  // create endpoint
  const endpoint = root + '?start=' + startDigit + '&numberOfDigits=' + numDigits;

  // call the PI API to fetch digits of pi
  const response = UrlFetchApp.fetch(endpoint);
  const result = JSON.parse(response.getContentText());
  
  // get pi
  // keep pi as a string otherwise it gets truncated if a number
  // add single tick in front of the 3 to avoid automatic conversion to number in Sheet
  const piDigits = result.content;
  const pi = "'3." + piDigits.substring(1); 

  // paste result into Google Sheet
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const piSheet = ss.getSheetByName('PI API');
  piSheet.getRange(3,1).setValue(pi);
  
}

The API is limited to calls of up to 1,000 digits at a time, so if you want more than that you’ll need to expand the script above to request batches of 1,000.

More Resources For Mathematics in Google Sheets

Complex Numbers In Google Sheets

How To Draw The MandelBrot Set In Google Sheets, Using Only Formulas

INDIRECT Function in Google Sheets

The INDIRECT function in Google Sheets is used to convert text strings into valid cell or range references.

For example, the INDIRECT function will convert the text string “A1” into the cell reference A1. The formula is:

=INDIRECT("A1")

which is equivalent to this formula:

= A1

It gives the answer 10 in the following example because that’s the value in cell A1:

Indirect Function In Google Sheets

INDIRECT Function in Google Sheets: Syntax

=INDIRECT(cell_reference_as_string, [is_A1_notation])

It takes two arguments:

  1. cell_reference_as_string
  2. [is_A1_notation]

Here is what they mean:

cell_reference_as_string

The first argument is a text string that represents a cell or range reference, e.g.:

"A1"

"Sheet1!B4:D100"

It can also point to a cell containing a range reference, as shown here:

Indirect Formula Cell Reference

The value in cell B1 is the text value “A1”.

When the INDIRECT points to B1, it picks up this string reference and then converts it to an actual cell reference.

[is_A1_notation]

This second argument is an optional argument that is either TRUE or FALSE.

  • If you set it to TRUE, the INDIRECT function to use familiar A1 style notation (e.g. A1, B2:D15, G1:M1000)
  • Setting this argument to FALSE forces the INDIRECT function to use the uncommon R1C1 notation (see below)

If you omit this argument, and your INDIRECT just has a single argument like the example at the top of this page, it defaults to A1 notation as if you had used a TRUE argument.

I’ve never seen a formula with this second argument set to FALSE in the wild. So you can almost certainly ignore this argument and just use the INDIRECT function with a single input.

Aside On R1C1 Notation

R1C1 notation describes a cell or range reference using a row number and column number.

R1C1 is an absolute reference to the cell at position (1,1) in your Sheet, i.e. A1 in regular notation.

The reference with square brackets – R[1]C[1] – is a relative R1C1 reference meaning the cell 1 row down and 1 column to the right of the current cell, wherever that is in your worksheet.

Here’s an example of the INDIRECT using the R1C1 notation:

Indirect Function R1C1 Notation

Both formulas have FALSE as the last argument of the INDIRECT function, so they’re using R1C1 notation.

The first has the absolute notation – “R1C1” – so it points to cell A1, the cell at Row 1 and Column 1 in your Sheet.

The second – “R[-1]C[0]” – is a relative reference telling the INDIRECT function to go back one row (i.e. the row above, going from row 2 to row 1) and stay in the same column (because the C value is 0).

This is equivalent to the formula:

= B1

R1C1 notation can also reference ranges by joining two cell references with a “:” colon, similar to how ranges are referenced in A1 notation, e.g.:

R[-3]C[0]:R[-1]C[0]

INDIRECT function template

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy…

If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you click the link and open in an Incognito window you’ll be able to see it.

The INDIRECT function is also covered in the Day 22 lesson of my free Advanced Formulas 30 Day Challenge course.

You can also read about it in the Google documentation.

Using The INDIRECT Function To Work With Multiple Sheets

Suppose you have multiple sheets in you Google Sheets file, each with a similar structure. For example, it could be that you have transaction data per year, each in their own sheets: 2018 Data, 2019 Data, 2020 Data, 2021 Data

If you want to perform a calculation across all four sheets at once (e.g. a grand total), you have to click into each sheet from a formula and it’s rather tedious, especially if you have lots of sheets.

By using the INDIRECT function, you can build the formula with strings to save having to click on each tab.

With a list of sheet names in column A, use this INDIRECT formula in column B to access values from each sheet without needing to click across:

=INDIRECT(A1&"!B6")

Drag this formula down the column and it will return the value in cell B6 of each sheet:

Indirect Formula Example across Google Sheets

The first formula in cell B2 returns the value from cell B6 of the “2018 Data” sheet:

Indirect Formula Across Sheets

VLOOKUP + INDIRECT Formula in Google Sheets

This is another example of how to use the INDIRECT function to work with multiple sheets.

By combining the VLOOKUP and INDIRECT functions, you can lookup data from different sheets based on a user input.

It requires you to have consistently named tabs in your Google Sheet, e.g. something like Data 2018, Data 2019, Data 2020, Data 2021

Create a data validation drop down to let the user select one of these sheet names and then use the INDIRECT function to convert the text string into a valid range reference which becomes your lookup table.

=VLOOKUP(A2,INDIRECT(B2&" Data!A1:B5"),2,false)

Vlookup Indirect Formula

Using The INDIRECT Function To Create Vectors

As you progress in your spreadsheet career, you’ll start to work more frequently with arrays of data rather than single values. One thing you’ll find yourself doing fairly often is needing to create a numbered list.

For example, you might want to sort data on the fly but need some way to get back to the original order. By using a combination of a column vector and array literals {…} you can build this inside the formula without having to add a helper column to your data.

Vectors crop up in other complex formulas too, like this text reverser, unpivot or even advanced sparklines like this clock or this visualize value experiment.

The INDIRECT function can be combined with the ROW function to create column vectors, or combined with the COLUMN function to create row vectors (yes, I realize that sounds strange, but you’ll see what I mean below).

NOTE: These methods have largely been replaced by the simpler, more elegant SEQUENCE function which can easily generate row or column vectors. However, you may still see the INDIRECT version in online forums or older resources, hence why I’m sharing here.

This formula creates a vector from 1 to 10 running down a column:

=ArrayFormula(ROW(INDIRECT("1:10")))

This formula creates a vector from 1 to 10 running across a row:

=ArrayFormula(COLUMN(INDIRECT("A:J")))

Vectors With Indirect

The formula can be generalized to create vectors of variable length. One example might be measuring the length of a text string in cell A1 and creating a vector matching that length.

This particular technique as part of a formula to reverse text in Google Sheets (again the newer SEQUENCE function could make this more succinct).

=ArrayFormula(ROW(INDIRECT("1:"&LEN(A1))))

Other Advanced INDIRECT Techniques

The INDIRECT function can also be used to build dynamic named ranges in Google Sheets.

=SUM(INDIRECT(dynamicRange))

Read all about dynamic named ranges in Google Sheets here.