Looking for more? Try chatting with the Exceljet Chatbot.
The Excel IFERROR function returns a custom result when a formula generates an error, and a standard result when no error is detected. IFERROR is an elegant way to trap and manage errors without using more complicated nested IF statements.Read more
If VLOOKUP finds more than one match, will you get the first match or the last match? It's a trick question. It depends :) This article explains this confusing topic in detail, with lots of examples.Read more
In this example, the goal is to make a noun plural when the number of items is greater than one. In many cases, a noun can be made plural by adding an "s". However, many nouns have an irregular plural form, and the main challenge is to handle these exceptions.
In...Read more
Excel contains over 500 functions, with more functions added every year. That is a huge number, so where should you start? This guide provides a walkthrough of over 100 important functions in Excel with many examples and links. Click function names for details and more examples....Read more
The XLOOKUP function contains built-in support for wildcards, but this feature must be enabled explicitly by setting match mode to the number 2.
In the example shown, XLOOKUP is configured to match the value entered in cell E5, which may appear...Read more
If you need to group times into buckets that are not the same size (i.e. 12 AM-7 AM, 7 AM-12 PM, etc.) you can use the VLOOKUP function in approximate match mode.
There are several ways to group times in Excel. If you just need to group times by the hour, a pivot...Read more
If you are new to the SUMIFS function, you can find a basic overview with many examples here.
The SUMIFS function is designed to sum numeric values based on one or more criteria. In specific cases however, you may be able to use SUMIFS to "look up...Read more
In this example, the goal is to assign points based on a ranked score. The scores to rank are in column C, and the calculated Rank is in column D. Points are awarded based on the table in G5:H10. Both tblData (B5:E15) and tblPoints (G5:H10) are...Read more
The #REF! error occurs when a reference is invalid. In many cases, this is because sheets, rows, or columns have been removed, or because a formula with relative references has been copied to a new location where...Read more
This is a standard MATCH formula where the lookup values come from column H, the array is the headers in Table1, and match type is zero, to force an exact match.
The only trick to the formula is the use of a structured reference to return a range for the table headers to the MATCH...Read more
The Excel IFNA function is a simple way to handle #N/A errors specifically without catching other errors. The IFNA function allows you to specify a custom value or message to display instead of the #N/A error. If no error #N/A error occurs the formula returns a normal result....Read more
The goal is to calculate discounts on a per-item and per-quantity basis using the discount table at the right in the workbook shown. The purpose of the discount table is to allow each item to have its own set of discounts. Notice that Donuts have a different discount for a quantity of 24. The...Read more
The #N/A error appears when something can't be found or identified. It is often a useful error, because it tells you something important is missing – a product not yet available, an employee name misspelled, a color option that doesn't exist, etc.
However...Read more
In this example, we have a simple worksheet that uses the XLOOKUP function to lookup the name of a U.S. state when a valid two-letter code is provided as a lookup value. The goal is to remove the #N/A error that XLOOKUP returns when it can't find a result. Although you could use the IFNA or...Read more
In this example, the goal is to look up amounts for 1000 invoice numbers in a table that contains 1 million invoices. The catch is that not all of the 1000 invoice numbers exist in the source data. In fact, most of the invoice numbers do not appear in column B. This means we need...Read more
Learn to use Excel formulas and functions to build powerful, efficient spreadsheets. This accelerated video training guides you step-by-step.Read more
Are nested IFs evil? Are they necessary? Are there alternatives? The answer is Yes! This in-depth article explores nested IF formulas in detail, with lots of tips, and several alternatives.Read more
The Excel NA function returns the #N/A error. #N/A means "not available" or "no value available". You can use the NA function to display the #N/A error when information is missing.Read more
In this example, the goal is to lookup data to the left of an ID that appears as the last column in the table. In other words, we need to locate a match in column E, then retrieve a value from a column to the left. This is one of those problems that is...Read more
If the active cell is empty, Excel will extend the selection to the first non-empty cell to the right. If the active cell is non-empty, Excel will extend the selection on the last non-empty cell to the right. On a Mac, the command key (⌘) can be used instead of the control key.Read more
The Excel HLOOKUP function finds and retrieve a value from data in a horizontal table. The "H" in HLOOKUP stands for "horizontal", and lookup values must appear in the first row of the table, moving horizontally to the right. HLOOKUP supports approximate and exact matching, and...Read more
The Excel ISNA function returns TRUE when a cell contains the #N/A error and FALSE for any other value, or any other error type. You can use the ISNA function with the IF function test for #N/A and display a friendly message if the error occurs.Read more
When XLOOKUP can't find a value in a lookup array, it returns an #N/A error. You can use the IFNA function or IFERROR function to trap this error and return a different...Read more
The Excel XLOOKUP function is a powerful tool designed to look up a value in one range and return a corresponding value in another range — it supports approximate and exact matching, wildcards, regular expressions (regex), reverse searches, and lookups in vertical or horizontal ranges....Read more
This example shows how the SUMIFS function can sometimes be used to "lookup" numeric values, as an alternative to more complicated multi-criteria lookup formulas. This approach is less flexible than more general lookup formulas based on ...Read more
Imagine a company that uses a tiered commission structure for its sales team. Each salesperson is assigned a commission rate based on the total sales they have made. The commission tiers are structured like this:
