Excel’s XLOOKUP Function Explained
Excel's XLOOKUP function searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't...
How to Count the Occurrences of Items in a List
"I have a list of repair orders (RO). I want to know how many one line repair orders there are in the list. So...
How to Split and Index Delimited Text with Excel Formulas
For the longest time I've wished that Microsoft would add a SPLIT function to Excel. The function would have a syntax like this...
=SPLIT(text, delimiter,...
Excel’s VLOOKUP vs INDEX-MATCH Functions
Excel offers two primary lookup methods: VLOOKUP and INDEX-MATCH. Although the two methods are similar, INDEX-MATCH is more powerful.
I suspect, however, that VLOOKUP is better-known and more...
Manage Periodic Payments with an Excel Cash Calendar
Most companies must write checks periodically to companies that don't send invoices. Often, their accounting systems provide little help in keeping track of these...
How to Read and Update Excel Tables Using SUMIFS and INDEX-MATCH
A growing number of Excel users in business are linking their reports and analyses to Excel tables. By doing so, they can update them...
How to Use SUMPRODUCT to Find the Last Item in an Excel List
Suppose you have a Sequential List of items, as in the second table in the figure below. And suppose you want to show the...
How to Create Summarized Financial Statements with SUMIFS Criteria Lists
In How to Use SUMIFS with Criteria Lists, Summarizing Sales, I explained how to use the SUMPRODUCT function with SUMIFS to return the sum of...
How to Smooth Monthly Trends with Centered Moving Averages
I recently read an article in the Wall Street Journal about the expected increase of electric vehicles in the US. That made me wonder...
How to Fight Spreadsheet Hell with Three Excel Lookup Functions
(I wrote this long ago, and there's much to be added. It's high on my Update list. Charley)
Many Excel users build their reports like...