Dave Bruns

At the core, this formula is a basic INDEX and MATCH formula. However, instead of using the older MATCH function, we are using XMATCH function, which provides a more powerful...Read more

The goal is to return the first non-blank value in each row from columns B:E, moving left to right. One way to solve this problem is with a series of nested IF statements. Since all cells are contiguous (connected) another way to get the first value is with the XLOOKUP function. Both approaches...Read more

In this example the goal is to return rows in the range B5:E15 that have a specific state value in column E. To make the example dynamic, the state is a variable entered in cell H4. When the state in H4 is changed, the formula should return a new set of records. This is a perfect...Read more

The goal is to lookup a feed rate based on material, hardness, and drill bit diameter. Feed rate values are in the named range data (D6:H16).

This can be done with a two-way INDEX and MATCH formula. One MATCH function works out...Read more

Excel times are numbers and can be summed like other numeric values. In this example, F4:G7 is a summary table, showing the total time logged in each of three states: Standby, Run, and Offline. These values are hardcoded in the range F5:F7.

To sum...Read more

In this example, the goal is to create a clickable link that will result in a ready-to-send email.

The mailto link protocol

The mailto link protocol allows five variables as shown in the table below:

In this example, the goal is to calculate the correct commission for both Agent and Broker based on a 3% commission which is split according to the table in G7:I11, which is named split. Notice the amount going to the Agent and Broker changes as the total amount increases, which...Read more

This formula uses the MIN function as an alternative to the IF function.

Although MIN is frequently used to find the minimum value in a larger set of numbers, it also works fine with just two...Read more

The first thing this formula does is check the date in column D against the date in the header (E4:J4).

=IF($D5<=E$4

Translated: if the date in column D is less than or equal to the date in row E.

Note these are...Read more