I Stopped Wasting Time in Excel When I Learned These 3 Functions

Most people think speed in Excel means memorizing hundreds of formulas and functions. I thought so, too, until I discovered that just three functions could replace the pile of formulas I used to juggle.

3

XLOOKUP

Remember when VLOOKUP made you count columns just to figure out your column index? Or when you wanted to look up data to the left of your key column and ended up rearranging your whole sheet? Those headaches are gone.

XLOOKUP is Excel’s modern fix for everything VLOOKUP got wrong. It’s simpler, more flexible, and available in Excel 365 and Excel 2021+. Instead of wrestling with column numbers or direction limitations, you just tell it three things: what to look for, where to look, and what to return.

Here’s the basic syntax:

=XLOOKUP(lookup_value, lookup_array, return_array) 
=XLOOKUP(O2,I2:I200,L2:L200)

That’s enough to search for a value and return the matching result. When you want to search for another value, you don’t need to change the cell you’ve designated as your lookup_value (in my case, O2) in your formula. You only need to change the figure in the cell (in my case, 3604), and the results will update immediately. If Excel can’t find a match, it defaults to showing #N/A.

But XLOOKUP has extra tricks that make it far more useful. Here’s an example:

=XLOOKUP(O2,I2:I200,L2:L200,”Figure not found”) 
=XLOOKUP(O2,I2:I200,L2:L200,0,1,-1)

The first formula shows a friendly message instead of an error when there’s no match. Meanwhile, the second one tells Excel to return 0 if no match is found, look for the next largest value if there isn’t an exact match, and search from the bottom up instead of top down. That last setting (the search_mode) is perfect if the value you’re searching for has multiple occurrences and you only want the last one.

Basically, the full XLOOKUP syntax is this:

=XLOOKUP(lookup_value, lookup_array, return_array, “if_not_found”, match_mode, search_mode)

Here’s where it gets even better: XLOOKUP can return multiple results at once. For example, instead of pulling just revenue, you can pull revenue, cost, and profit all in one go:

=XLOOKUP(O2,I2:I200,L2:N200)

You can even add them up directly:

=SUM(XLOOKUP(O2,I2:I200,L2:N200))

No column counting, no juggling absolute references, and no restrictions on which direction you can look. Let’s say you want to look up a team member’s name and department based on their ID:

=XLOOKUP(A2,TeamMemberID_column,Name:Department_columns)

This formula is all you’d need.

2

SUMIFS/COUNTIFS

If you’re still filtering your data every time you need a quick total or count, you’re working way too hard. SUMIFS and COUNTIFS are unsung heroes, especially for sales reports, budgets, or any dataset where you need conditional calculations.

Let’s imagine that your boss asks you, “What were our total sales in Asia for products over $150 that were ordered online?” Instead of creating three filters and hoping you don’t mess up the sheet, you can answer with one line:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2]...) =SUMIFS(Sales_column,Region_column,"Asia",Price_column,">150",SalesChannel_column,"Online")

Or, in a real dataset:

=SUMIFS(L2:L200,A2:A200,"Asia",J2:J200,">150",D2:D200,"Online")

This formula sums all the values in column L where column A equals “Asia,” column J is greater than 150, and column D equals “Online.”

COUNTIFS works the same way, except it returns the number of rows that meet your conditions:

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]...)
=COUNTIFS(A2:A200,"Asia",J2:J200,">150",D2:D200,"Online")

Instead of an amount, as in SUMIFS, you’ll get the number of orders from Asia that were ordered online and have a unit price of over $150.

SUMIFS and COUNTIFS also handle wildcards. For instance, you could count all countries starting with “T” and all items except fruits:

=COUNTIFS(B2:B200,"=A*",C2:C200, "Fruits")

Wildcards are handy, but they depend on clean data. If your dataset has inconsistent capitalization or hidden characters, your results might look wrong. A quick cleanup of your messy Excel sheet will save you headaches later.

The beauty of SUMIFS and COUNTIFS is that they’re non-destructive. You’re not slicing and messing up your original dataset at all. And if you need more complexity, Excel supports up to 127 criteria pairs.

So whether you’re checking “How many customers spent over $500 in the last 30 days?” or “What’s the revenue for online orders of accessories under $50 in North America?”, these two functions will give you the answer instantly.

1

FILTER

The FILTER function might just be the most satisfying function Excel has added in years. Remember when filtering data meant clicking through menus, setting conditions, and hoping you didn’t accidentally hide the wrong rows? With FILTER, all of that is reduced to a single formula, and the basic syntax is simple:

=FILTER(range to filter, criteria for filtering)

Say you want to see revenue from sales where the unit price was over $600:

=FILTER(Revenue_column,UnitsSold_column>600).

Or, in a real dataset:

=FILTER(L2:L200,J2:J200>=600)

Instantly, you’ll only see the numbers that match your criteria. If nothing qualifies, you can also add a fallback message instead of getting an error:

=FILTER(L2:L200,J2:J200>=1000,"No Match")

Just like SUMIFS, you’re not limited to one condition. You can use OR logic (at least one condition must be true) or AND logic (all conditions must be true). Here are some simple examples:

=FILTER(L2:L200,(J2:J200>=600)+(D2:D200="Online"),"No Match")
=FILTER(L2:L200,(J2:J200>=600)*(D2:D200="Online"),"No Match")

The first formula returns values that are either priced above $600 or sold online. The second only returns values that are above $600 and sold online.

FILTER gets even better when combined with SORT. Suppose cell O2 contains “Meat,” and you want all matching rows sorted by country:

=SORT(FILTER(A2:N200,C2:C200=O2,""),2,1)

That gives you all sales records for meat purchases, arranged in ascending order according to a specified column (in my case, I chose column 2, which is my country column). Change the 1 at the tail end of the formula to -1, and your results will appear in descending order instead.

FILTER returns a live range. If your data updates, your results update automatically. You can share the range or even use it as the source for another formula. And don’t worry if you send the file to someone whose version is without FILTER; they’ll still see the results (just not the live formula).

Together, XLOOKUP, SUMIFS/COUNTIFS, and FILTER eliminate most of the repetitive, click-heavy work we Excel users typically slog through. If you only add one to your toolkit this week, pick the one that saves you from the toughest workaround you’re living with today. Once you see how much time it saves you, you’ll be keen on trying the others.