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.
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.
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.
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 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","")
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.
$A$3(Column and row) or
A$3(just Row) or
- Suppress errors like #N/A