Lookup & Reference

VLOOKUP, INDEX/MATCH, and XLOOKUP are the most important formulas for working with large datasets. They let you pull data from one table into another based on a matching value — essential for combining reports, pricing lists, employee data, and any multi-sheet work. Mastering even one of these immediately qualifies you for higher-paying data-focused VA roles.

Math & Statistics

SUMIF, SUMIFS, COUNTIF, and COUNTIFS let you calculate totals and counts based on conditions without needing a pivot table. These are the formulas most VAs use daily to produce summary reports from raw data. A client who previously spent hours manually filtering and summing data will immediately see the value of a VA who knows these.

Logic

IF, IFS, AND/OR, and IFERROR control what your spreadsheet displays based on conditions. IFERROR alone is one of the most practical formulas for keeping reports clean by replacing error codes with meaningful text. IF formulas power status columns, grading systems, and any automated labeling in a spreadsheet.

Text

LEFT, RIGHT, MID, SUBSTITUTE, TRIM, and TEXT handle messy imported data. Raw exports from CRMs, e-commerce platforms, and HR systems almost always have formatting issues. A VA who can clean a 5,000-row export in minutes using text formulas saves the client hours and builds a reputation for technical competence fast.

Date & Time

TODAY, DATEDIF, NETWORKDAYS, and EDATE are the most useful date formulas for VAs managing project timelines, calculating employee tenure, tracking contract durations, and building deadline-aware trackers. Any VA who works with scheduling, HR data, or project management will use these regularly.

Finance

PMT and related financial formulas are valuable for VAs working with bookkeeping clients, real estate investors, or financial planning businesses. Being able to build a basic loan calculator or amortization schedule inside a spreadsheet is a skill most VAs do not have — which means it commands a higher rate.

Utility (UNIQUE, FILTER, ISBLANK)

The modern Excel 365 dynamic array functions let you build powerful data tools with a single formula. UNIQUE removes duplicates, FILTER replaces manual filtering, and ISBLANK helps build smart validation. VAs who know these can build dashboards and automation tools that previously required advanced programming knowledge.

Do these formulas work in Google Sheets?

Yes. The vast majority of formulas here work identically in Google Sheets. The main exceptions are XLOOKUP, UNIQUE, and FILTER, which are available in Google Sheets as well but may have slight syntax differences. Classic formulas like VLOOKUP, SUMIF, IF, and IFERROR are fully compatible with all versions of both tools.

I am a beginner. Which formula should I learn first?

Start with IF and IFERROR — they are the most universally useful and appear in almost every spreadsheet task. Then learn VLOOKUP or INDEX/MATCH for data lookups, and SUMIF for conditional totals. Those four formulas alone cover 80% of what most admin and operations VAs need day to day.

Why is my VLOOKUP returning #N/A?

This almost always means the lookup value was not found in the first column of your table range. Common causes: extra spaces in the data (fix with TRIM), the lookup value and table values are different data types (one is a number, one is text), or the table range does not start with the column you are searching. Wrap the formula in IFERROR to handle not-found cases without showing an error.

What is the difference between VLOOKUP and INDEX/MATCH?

VLOOKUP can only look to the right — the column you search must be the leftmost column in your table range. INDEX/MATCH has no such restriction and can look left, right, or even up. It is also more resilient to columns being inserted or deleted. XLOOKUP (Excel 365+) is the modern solution that replaces both and is simpler to write.

How do I reference another sheet in a formula?

Use the format SheetName!CellReference. For example, =VLOOKUP(A2, Sheet2!B:D, 2, FALSE) searches columns B through D on Sheet2. If your sheet name contains spaces, wrap it in single quotes: =VLOOKUP(A2, 'Client Data'!B:D, 2, FALSE).

Can I list Excel as a skill on my VA profile if I only know basic formulas?

Yes — but be specific. Instead of just listing 'Excel', list what you can actually do: 'Excel (VLOOKUP, SUMIF, IF formulas, data cleaning)'. Specific claims are more credible than general ones and help clients self-select based on what they actually need.

Can I use these formulas to build a dashboard for a client?

Absolutely. Combine SUMIFS and COUNTIFS with a summary table to build a dynamic dashboard without a pivot table. Use IFERROR to handle missing data cleanly, TEXT to format outputs for presentation, and UNIQUE/FILTER (Excel 365) to build dynamic dropdown-driven views that update automatically.