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.
Working from the inside out, the MATCH function is...Read more
In this example, the goal is to look up and retrieve an entire row of values in a set of data. For example, when a value like "Neptune" is entered into cell H5, all values in the range C11:F11 should be returned. For convenience and readability, project (B5:B16) and data...Read more
In this example, the goal is to look up and retrieve an entire column of values in a set of data. For example, when a value like "Q3" is entered into cell H4, all values in the range E5:E16 should be returned. For convenience and readability, quarter (C4:F4) and ...Read more
This formula uses the REPLACE function to replace the first character in a cell with an empty string (""). The arguments for REPLACE are configured as follows:
The gist: this formula "floods" the space between words in a text string with a large number of spaces, finds and extracts the substring of interest, and uses the TRIM function to clean up the mess.
Working from the inside out, the original text in B5 is flooded with spaces using...Read more
Note: In Excel 365, the new SEQUENCE function is a better and easier way to create an array of numbers. The method explained below will work in previous versions.
The core of this formula is a string...Read more
The MAXIFS function returns the largest numeric value in cells that meet multiple conditions, referred to as criteria. Each condition is provided with a separate range and criteria. To define criteria, MAXIFS supports various ...Read more
The MINIFS function returns the smallest numeric value in cells that meet multiple conditions, referred to as criteria. Each condition is provided with a separate range and criteria. To define criteria, MINIFS supports various...Read more
