XLOOKUP vs VLOOKUP vs INDEX-MATCH vs SUMIFS
SUMIFS? Really?
Why is SUMIFS included among those lookup methods? I'll explain in a few minutes.
But first, let's look at the lookup methods shown in...
Show Top and Bottom Results in a Chart-Table
The workbook that supports the following figure does a lot of work!
First, it uses Power Query to download the weekly unemployment claims and the...
How to Use SUMPRODUCT in an Excel Table to Filter Any Number of Items
Excel 2007 introduced the powerful Table feature, as illustrated below. Tables allow you to sort and filter your data easily.
However, the filter capability has...
How to Use SUMPRODUCT to Create Two-Dimensional Lookups in Excel Formulas
A friend recently asked, "In this table, how can I return the date where the lowest value occurs?"
More generally, however, he was asking how to...
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...
How to Debug INDEX in Excel
"I'm not a very experienced Excel user. Why does = INDEX(...) sometimes work and sometimes not.... :-)?" -- Suzan G.
Probably the best way to...
Use Excel VBA to Test Report Calculation Times
I once exchanged email with an experienced Excel jockey who insisted that VLOOKUP was Excel’s fastest lookup method. It was even faster, he said,...
How to Use INDEX-MATCH, Part 3: Six Lookups That VLOOKUP Can’t Do
In this three-part series, I'm showing you how to use INDEX-MATCH. In Part 1, I showed you how to use the INDEX function. In Part...
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...
How to Use INDEX-MATCH, Part 2: The MATCH Function
In this three-part series, I'm showing you how to use INDEX-MATCH. In Part 1, I showed you how to use the INDEX function. Here,...
Latest Articles
A Lesson in Excel Analysis Learned from Rising US Inflation
Looking at the trends in the annual rate of change (ROC) of business and economic measures is an excellent way to reveal hidden information...
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,...
Warren Buffett, Inflation, and Excel
On May 3, 2021, CNBC quoted Warren Buffett saying, “We are seeing very substantial inflation. We are raising prices. People are raising prices to...
Advertisement
Advertisement