Google Spreadsheet
便利な関数
=QUERY(range, sql query)
=ArrayFormula(INDEX(B2:B19, MATCH(1, FIND(E2, C2:C19)), 0))
Case Sensitive Search
=IFERROR(ArrayFormula(INDEX(B2:B27, MATCH(CONCATENATE(F2:F4), A2:A27&C2:C27&D2:D27, 0),)), "Not found")
Multiple Criteria search
=INDEX(reference,MATCH(search_key, range, [search_type]),[column])
https://www.ablebits.com/office-addins-blog/2020/02/04/google-sheets-index-match/
Note. Faster than normal VLOOKUP
Match gets which row the key is and then index uses match result to return the row data or specific column in the row
=INDEX(reference, [row], [column])
Index RETURNS value from specific cell if both row and column are declared, otherwise it returns the entire row
=MATCH(search_key, range, [search_type])
Match looks in ONE column for the KEY and RETURNS POSITION
Note. MATCH only accepts one-dimensional arrays: either row or column.
=VLOOKUP(search_key, range, index_key)
Note. VLOOKUP searches the range for a match on the first left column ONLY
=ARRAYFORMULA()
=IFERROR(formula, in case error)
just like try catch
=IMPORTRANGE()
import spreadsheet