google sheets gsheets spreadsheets tips

gsheets.md

Unique

https://stackoverflow.com/a/46790769/1180523

=UNIQUE(A2:A)

# Exclude the "N/A" results:
=UNIQUE(FILTER(A2:A,A2:A<>""))

Count occurrences

https://www.extendoffice.com/documents/excel/4707-google-sheets-count-number-of-occurrence.html

=COUNTIF(A2:A16, C2)

Find Match

https://support.google.com/docs/answer/3093378?hl=en

=MATCH("searching for",G2:G,0)

Show "YES"/"NO" with Match

(replace "YES"/"NO" with desired output)

https://stackoverflow.com/a/38726628/1180523

=IF(ISNUMBER(MATCH("searchign for",G2:G,0)),"YES","NO")

Combine Columns (for searching)

{G2:G;J2:J}

Import Google Sheet into a Google Sheet sheet

https://support.google.com/docs/answer/3093340?hl=en

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1234/edit","'Sheet 1'!A1:Z")

Check if cell is blank and ouput "MISSING VALUE"

=IF(ISBLANK(F2), "MISSING VALUE", "")

CROSS-REFERENCE different sheets to grab a value from sheet 2 and insert into sheet 1

basically, select b.value if a.col == b.col

• Sources to lookup for help:
  • https://www.ablebits.com/office-addins-blog/2020/02/04/google-sheets-index-match/
  • https://www.ablebits.com/office-addins-blog/2017/07/05/vlookup-google-sheets-example/

=IF(ISNUMBER(MATCH("SEARCHING FOR",A:A,0)),VLOOKUP("SEARCHING FOR",A:B, 2, FALSE), "NOT FOUND")