Why INDEX-MATCH Is Far Better Than VLOOKUP or HLOOKUP in Excel
(Download the workbook.)
Excel’s VLOOKUP function is more popular than the INDEX-MATCH function combination, probably because when Excel users need to look up data then a "lookup" function...
How to Perform Multiple Table Searches Using the SEARCH & SUMPRODUCT Functions in Excel
SUMPRODUCT is one of Excel's most-powerful worksheet functions. Here, for example, you can use it in one formula to search text in one cell...
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...
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...
Use Excel’s INDEX-MATCH or VLOOKUP Functions to Populate Invoices and POs
A visitor asked how to set up a simple invoicing system in Excel.
This is a common problem in many small businesses, divisions, and sales...
Introducing Excel’s STOCKHISTORY Function
The STOCKHISTORY function recently showed up in my non-beta version of Excel 365.
Its appearance was a surprise because it wasn't announced as a What's...
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 INDEX-MATCH, Part 1: The INDEX Function
Which is best, VLOOKUP or INDEX-MATCH?
You might be surprised at the question, because the answer never has been in doubt. INDEX-MATCH can do anything...
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...
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...
Latest Articles
Getting Radical with Excel
It's time to think about Excel in a radical new way—when we use it to work with business or economic data.
It's time, in fact,...
How Fast Is Too Fast?
(Originally published in Inc Magazine.)
What typically tops the list of worries of the chief executive officers of fast growing companies? Financing that growth, according...
How to Smooth Data by Using the TREND Function
Years ago, I read that Prof. William S. Cleveland had suggested that data could be smoothed by calculating a centered trendline through adjacent data—a...
Advertisement