Google Sheets Cheatsheet



Updated 2022-08-14


To run a search and return a row number, use match() on the entire column, such as A:A. By default, match returns a relative position.

Example: =INDEX('7/12/22'!B:B,MATCH(A3,'7/12/22'!D:D,0),0)

Here I’m searching in another tab named 7/12/22 in column D for text that is in A3 in the current tab.



Returns the contents of a cell specified by a row and column offset.

Example: =INDEX('7/12/22'!B:B,MATCH(A3,'7/12/22'!D:D,0),0)

This returns the contents of what is in Column B in a tab named 7/12/22 and the row number returned by the MATCH function.


Let’s say you want to use the contents of a cell as the tab or cell name/reference in a formula. You can use INDIRECT for this.

Example: =INDEX(INDIRECT(B1&"!B:B"),MATCH(A2,INDIRECT(B1&"!D:D"),0),0)

This example uses the contents of B1 as the tab name for the Index function (described above)


This is a formula for taking filenames and turning them into what would turn them into if you upload them into the media library – stripping spaces and such (and also removing the extension)

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," - ","-")," ","-"),"(",""),")",""),".webp","")


This checks if the value in D2 shows up anywhere else in column B.


Relative vs Absolute references

Helpful for fixing whether or not you can fill sideways and down without Sheets incrementing columns and rows in forumulas.

  • Relative: A3
  • Absolute: $A$3 (Column and row) or A$3 (just Row) or $A3 (just column)


  • Suppress errors like #N/A

Related Posts