The 11 Most Important Formulas to Know for RecOps and Data

Jeremy Lyons
Jeremy Lyons
Guest Author

3 minute read

All RecOps roles call for, at a minimum, an advanced knowledge of Microsoft Excel and/or Google Sheets. With 450+ functions, it can be hard to know where to start and which ones to learn.

To help, here is a list of formulas we recommend you know:

VLOOKUP / XLOOKUP / HLOOKUP

  • The staple has always been VLOOKUP to find information across a vertical column and check it with search criteria. XLOOKUP eliminates some of the restrictions of VLOOKUP and offers greater flexibility.

  • Used heavily in spreadsheets to find information.

TRUE/FALSE

  • These often appear when checking lists or in other formulas to determine exact match or a relational match.
  • Used heavily to checklists

IF especially IFERROR and IFNA

  • The IF family is especially effective if you checklists. Using IFERROR or IFNA are helpful when trying to have information stand out.
  • Used heavily in checking lists

COUNT especially COUNTIF and COUNTA

  • Like the name suggests, it counts values. When building tables without using Pivot Tables, using these can be helpful.

QUERY

  • Allows you to extract and manipulate data from a table or range of cells based on specific criteria.
  • Very useful when dealing with a massive table and multiple inputs.

ARRAYFORMULA

  • Allows you to apply a formula to an entire range of cells at once, without having to manually copy and paste the formula into each cell.

IMPORTRANGE

  • Allows you to important information from another workbook (Excel) or sheet (Google Sheets).
  • Extremely useful if you need to filter and manipulate data from a master spreadsheet that many people use at the same time.

INDEX

  • Allows you to retrieve a value or a range of values from a specific location within a table or range of cells. Often used in combination with other functions like MATCH or IF, to search for specific data within a table or range and retrieve the corresponding values.
  • When trying to calculate team data, this function is fantastic.

MATCH

  • Allows you to search for a specific value in a range of cells, and return the relative position of that value within the range.
  • Can be helpful in various tasks, such as looking up data in a table or validating user inputs.
  • Advanced-level formulas to be comfortable with are:

QUERY(IMPORTRANGE())

  • Allows you to import data from another workbook or sheet spreadsheet and then filter and manipulate that data using SQL-like queries.
  • Fantastic if you want to pull in information from a master spreadsheet but only want a few columns.

INDEX(MATCH())

  • Allows you to search for specific data within a table or range and retrieve the corresponding values.
  • Powerful alternative to V or XLOOKUP for searching for specific data within a table or range, and retrieving the corresponding values. It can be beneficial in large datasets where manual searching can be time-consuming and error-prone.

Share this post

Subscribe to Updates

Ashby products are trusted by recruiting teams at fast growing companies.

QuoraVanMoofDockerWeTransferIroncladDeelRampVeevFullStoryJuniAstronomerTalentfulModern Treasury
QuoraVanMoofDockerWeTransferIroncladDeelRampVeevFullStoryJuniAstronomerTalentfulModern Treasury
NotionVerkadaRetoolMarqetaDuolingoRampColorDeliveroo
NotionVerkadaRetoolMarqetaDuolingoRampColorDeliveroo