Search results

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:

• For sales less than $10,000, the commission rate is
...Read more

In this example, the goal is to perform a two-way lookup, sometimes called a matrix lookup. This means we need to create a match on both rows and columns and return the value at the intersection of this two-way match

The core of this formula is INDEX, which is simply retrieving...Read more

In this example, the IFERROR function is used to trap and suppress the #DIV/0! error that occurs when there is no value for Orders (column D). Without IFERROR, the formula C5/D5 would display a #DIV/0! error in E6 and E9.

The IFERROR function takes two arguments: a value (usually entered...Read more

At the core, this formula has a simple pattern like this:

=SUM(first:current)

Where "first" is the first cell in the Total column, and "current" is a reference to a cell in the current row of the Total column.

To get the a...Read more

This is a standard INDEX formula. The only trick to the formula is the use of a structured reference to return a range for the table headers:

Table1[#Headers]

This range goes into INDEX for the array argument, with the index value supplied...Read more

This formula uses structured references to refer to the "Amount" column in each table. The structured references in this formula resolve to normal references like this:

=SUM(Table1[Amount],Table2[Amount])
=SUM(C7...Read more

In this example, the goal is to create a reference to an external workbook with variable information. The easiest way to do this is to assemble  the reference to a range or cell in another workbook as a text value, then use the INDIRECT function to convert the...Read more

The core of this formula is INDEX, which is retrieving a value from the named range "amount" (B5:B13):

=INDEX(amount,row_num)

where row_num is worked out with the MATCH function and some boolean logic...Read more

At the core, this formula uses INDEX to create an expanding reference like this:

INDEX([Color],1):[@Color] // expanding range

On the left side of the colon (:), the INDEX function returns a reference to the first cell in the column...Read more

In this example, the goal is to perform a case-sensitive lookup on the color in the "Color" column. In other words, a lookup value of "RED" must return a different result from a lookup value of "Red". By default, Excel is not case-sensitive and this applies to standard lookup...Read more

When no argument is provided, the ROW function returns the "current row", that is, the row number of the cell that contains it. When a cell reference is provided, ROW returns the row number of the cell. When a range is provided, ROW returns the first row number in the range.

In the...Read more

XLOOKUP can handle arrays natively, which makes it a very useful function when constructing criteria based on multiple logical expressions.

In the example shown, we are looking for the order number of the first order to Chicago over $250. We are constructing a lookup...Read more

Excel supports the wildcard characters "*" and "?", and these wildcards can be used to perform partial (substring) matches in various lookup formulas. However, if you use wildcards with a number, you'll convert the numeric value to a text value. In other words,...Read more

Working from the inside out, we use a standard INDEX and MATCH function to locate the first match of lookup values in column B:

INDEX(data,MATCH(B5,data,0))

The MATCH function gets the position of the value in B5 inside the named range data...Read more

In this example, the goal is to create a dynamic reference to an Excel Table in a formula. In other words, create a formula that can refer to an Excel table by name as a variable. The easiest way to do this in Excel is to assemble the reference as a text value...Read more

This formula relies on concatenation to assemble a valid location for the HYPERLINK function.

In cell D5, the link location argument is created like this:

"#"...Read more

The MATCH function returns the position or "index" of the first match based on a lookup value in a range.

MATCH supports wildcard matching with an asterisk "*" (one or more characters) or a question mark "?" (one character), but only when the third...Read more

This is a more advanced formula. For basics, see How to use INDEX and MATCH.

Normally, an INDEX MATCH formula is configured with MATCH set to look through a one-column range and provide a match based on given criteria. Without concatenating values...Read more

The SUMPRODUCT function multiplies arrays together and returns the sum of products. If only one array is supplied, SUMPRODUCT will simply sum the items in the array. For example, if we give SUMPRODUCT one array...Read more

This formula pulls the customer name and state from the customer table into the order table. The MATCH function is used to locate the right customer and the INDEX function is used to retrieve the data.

Retrieving customer name

Working from the inside out, the MATCH function is...Read more

The goal is to return "Small" when the value in column D is "S" and "Large" when the value in column D is "L". In other words, if the value in column D is "S" return "Small" else return "Large".

If else in Excel

The concept of "If else" in Excel is handled with the...Read more

Working from the inside out, we use MATCH to locate the relative position of the last entry in column C:

MATCH(9.99E+307,C5:C100)

Basically, we are giving MATCH a "big number" it will never find in approximate match mode. In this mode,...Read more

In this example, the goal is to add leading zeros to a given number so that the total number of characters displayed is 5. Sometimes this is referred to as "padding" a number with zeros, because the number of zeros needed is variable. If the original number contains 2 digits, 3 zeros are added....Read more

Note: in more recent versions of Excel, the FILTER function is a better way to solve this problem. The INDEX and MATCH formula explained here is meant for legacy versions of Excel that do not provide the FILTER...Read more

In this example, the goal is to join two one-dimensional ranges together horizontally. This can be done with the CHOOSE function and array constant.

The CHOOSE function

The CHOOSE function is used...Read more

The goal is to return the first text value in each row, ignoring both blank cells and cells that contain numbers. This problem can be solved using the HLOOKUP function. In newer versions of Excel, you can use the XLOOKUP function, which is a more modern lookup function that can look up...Read more

In this example, the goal is to create an "locked" reference that won't change when columns or rows are added or deleted in a worksheet, or during a copy / paste / cut operation.

The INDIRECT function accepts text, and evaluates that text as a reference. As a result, the text is not...Read more