Google Sheets Cheatsheet

MATCH

INDEX

INDIRECT

SUBSTITUTE

COUNTIF

Relative vs Absolute references

IFERROR()

MATCH

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.

INDEX

Docs

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.

INDIRECT

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)

SUBSTITUTE

This is a formula for taking filenames and turning them into what WordPress 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","")

COUNTIF

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

=countif(B:B,D2)>0

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)

IFERROR()

Suppress errors like #N/A