Tuesday, April 13, 2021
Home Formulas & Functions Lookup Formulas and Functions

Lookup Formulas and Functions

    Excel’s ability to look up data offers significant power to Excel users. You’ll find examples and ideas here to improve your own Excel work.

    Latest
    We compare Excel's five columnar lookup functions.

    XLOOKUP vs VLOOKUP vs INDEX-MATCH vs SUMIFS

    Lookup Formulas and Functions Charley Kyd -
    0
    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...
    This Excel table shows the top and bottom five results, with charts that show the most recent three month trends. And it updates automatically.

    Show Top and Bottom Results in a Chart-Table

    Lookup Formulas and Functions Charley Kyd -
    0
    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...
    In Excel Tables, you can filter on any two conditions in a column. But by using the SUMPRODUCT function, you can filter on any number of items in a list.

    How to Use SUMPRODUCT in an Excel Table to Filter Any Number of Items

    Lookup Formulas and Functions Charley Kyd -
    0
    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

    Lookup Formulas and Functions Charley Kyd -
    0
    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...
    Do you spend hours each period turning raw data into useful information? You can fight such Spreadsheet Hell with help from three key Excel functions.

    How to Fight Spreadsheet Hell with Three Excel Lookup Functions

    Lookup Formulas and Functions Charley Kyd -
    0
    (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...
    Does Excel's INDEX worksheet function work for you only some of the time? Here are ways to learn why your INDEX function isn't working as you expect.

    How to Debug INDEX in Excel

    Lookup Formulas and Functions Charley Kyd -
    0
    "I'm not a very experienced Excel user. Why does = INDEX(...) sometimes work and sometimes not.... :-)?" -- Suzan G. Probably the best way to...
    This simple VBA procedure finds the precise time it takes to calculate a workbook. You can use it to test competing workbook design methods.

    Use Excel VBA to Test Report Calculation Times

    Lookup Formulas and Functions Charley Kyd -
    0
    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,...
    INDEX-MATCH formulas can perform many types of lookups that VLOOKUP can’t. And they're faster. Here are six examples.

    How to Use INDEX-MATCH, Part 3: Six Lookups That VLOOKUP Can’t Do

    Lookup Formulas and Functions Charley Kyd -
    0
    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...
    Most Excel users need to look up data in workbooks. But what's the best lookup method?

    Excel’s VLOOKUP vs INDEX-MATCH Functions

    Lookup Formulas and Functions Charley Kyd -
    0
    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...
    The MATCH function makes the INDEX function useful. This is the second of a three-part series designed to INDEX-MATCH clearer.

    How to Use INDEX-MATCH, Part 2: The MATCH Function

    Lookup Formulas and Functions Charley Kyd -
    0
    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,...
    Load more

    Latest Articles

    Using Excel's LET, SORTBY, and SEQUENCE functions, and dynamic arrays, you easily can list any number of top and bottom results from a Table.

    How to Report Top and Bottom Results Using Dynamic Arrays in Excel

    Array Formulas Charley Kyd -
    0
    In How to Use Excel’s LET Function, I showed several examples of a powerful function that Microsoft added to Excel 365 in the summer...
    The LET function is the most powerful function that Microsoft has released for Excel in years. Here's an introduction to its features.

    How to Use Excel’s LET Function

    Array Formulas Charley Kyd -
    0
    In the summer of 2020, Microsoft introduced the LET function for Excel 365—one of the most-significant new worksheet functions that Microsoft has introduced in...
    You can ratchet down errors in your Excel reports by using an Error Summary Table that uses conditional formatting to alert you to errors.

    How to Set Up an Automatic Error-Checking System in Excel Reports

    Accounting & Finance Charley Kyd -
    0
    Decades ago, I worked as a cost accountant for a large company. But because our department received terrible reports, I wrote my own reports...using...
    Advertisement
    Advertisement
    © ExcelUser, Inc. Copyright © 2004 - 2020 by Charles W. Kyd, all rights reserved.