The 11 Most Important Formulas to Know for RecOps and Data

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.