Cacher is the code snippet organizer for pro developers

#vlookup formula with "iferror" to remove "N/A" from empty cells

=iferror(vlookup(A1, 'Sheet1'!A2:A200, 2, false))

or better - with array formula so you don't have to drag

=arrayformula(iferror(vlookup(A1:A, 'Sheet1'!A2:A, 2, false)))

VLOOKUP to the left

=arrayformula(iferror(vlookup(A2:A,{Sheet8!E2:E,Sheet8!D2:D},2,0)))

=arrayformula(iferror(vlookup(Range,{Column To Match,Column To Left},2,0)))

=index('Search Data'!A:A,match(B2,'Search Data'!B:B))

# This will sort all data on sheet "name of tab" on cells a:l in descensing order

=sort('Name-of-tab'!A2:L, 1, false)

## In this case columns B, C, D, E, and F into column A.

=ArrayFormula( TRANSPOSE(SPLIT(SUBSTITUTE(CONCATENATE(SUBSTITUTE( TRIM( SUBSTITUTE(B2:B," ",CHAR(9)) & " " & SUBSTITUTE(C2:C," ",CHAR(9)) & " " & SUBSTITUTE(D2:D," ",CHAR(9)) & " " & SUBSTITUTE(E2:E," ",CHAR(9)) &" " & SUBSTITUTE(F2:F," ",CHAR(9)) &" " & SUBSTITUTE(G2:G," ",CHAR(9)) &" " ) ," " ,CHAR(10))&CHAR(10)),CHAR(9)," "), CHAR(10))))

# Choose your range and start your formula with the cell at the beginning of your range

=$D88<>""

=arrayformula(iferror(REGEXEXTRACT(A2:A,"(?:\.com|\.net|\.edu)(.+)")))

=QUERY( 'Pardot Export'!A2:E , "Select C where D starts with 'CEO' and E='Credit Union'")

=REGEXEXTRACT(A1,"/([^/]+)/$")

# use this to display something in the even that there is no data (or data) in a cell. In this example, if cell e532 has something in it, then put a "Y" in whichever cell the formula is placed in.

=IF(ISBLANK(E532),,"Y")

Or as an array formula

=arrayformula(iferror(if(isblank(B1:B37)*isblank(B1:B37),"No","Yes")))

=ArrayFormula(if(row(B:B)=1,"Title of Column",if(len(B:B),countif(unique(match(B:B&C:C,B:B&C:C,0)+match(B:B&C:C&H:H,B:B&C:C&H:H,0)/50000),"<="&match(B:B&C:C,B:B&C:C,0)+match(B:B&C:C&H:H,B:B&C:C&H:H,0)/50000)-countif(unique(match(B:B&C:C,B:B&C:C,0)+match(B:B&C:C&H:H,B:B&C:C&H:H,0)/50000),"<"&match(B:B&C:C,B:B&C:C,0)),)))

# Highlight an entire row based on the first instance of a set of data

=and(countif($A:$A,$A2)>0,match($A2,$A:$A,0)=row())

i.e. if row a's data is:

1
1
1
1
4
4
4
5
6
6
6

only the first 1, 4, 5, and 6 would be highlighted. The repeats of the data would not be.

=ArrayFormula(if(len(B2:B),B2:B&" "&C2:C&" "&E2:E,iferror(1/0)))