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
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) orA$3(just Row) or$A3(just column)
IFERROR()
- Suppress errors like #N/A