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
In this example, the goal is to look up a price using XLOOKUP with multiple criteria. To be more specific, we want to look up a price based on Item, Size, and Color. At a glance, this seems like a difficult problem because XLOOKUP only has one value for lookup_value and lookup_array...Read more
This formula relies on a specific behavior of INDEX — although it seems that INDEX returns the value at a particular location, it actually returns a reference to the location. In most formulas, you wouldn't notice the difference – Excel simply evaluates the reference and...Read more
In this example, the goal is create a VLOOKUP formula that will return an empty cell when the lookup result is an empty cell.
When VLOOKUP can't find a value in a lookup table, it returns the #N/A error. You can use the IFNA function or...Read more
XMATCH is a modern replacement for the MATCH function. It is a flexible and versatile function with a number of useful features:
XLOOKUP is Excel's modern all‑in‑one lookup function. It lets you search a row or column for a value and retrieve the corresponding value from another range, without the frustrating limitations that plagued older functions like VLOOKUP, HLOOKUP, and LOOKUP. It can even return more than one...Read more
In this example, a conditional formatting rule highlights cells in the range D5:D14 when the value is greater than corresponding values in C5:C14. The formula used to create the rule is:
=$D5>$C5
The rule is applied to the entire range D5:...Read more
In the image shown, the current date is August 24, 2019.
Excel dates are serial numbers, so they can be manipulated with simple math operations. The TODAY function always returns the current date.
Inside...Read more
In this example, the goal is to create a formula that will return TRUE if a date is in the last complete 6 month period, starting in the previous month. This means the date must fall between a calculated start date and end date, which requires two logical tests. The formula uses the AND function...Read more
In the image shown, the current date is August 19, 2019.
Excel dates are serial numbers, so you can manipulate them with simple math operations. The TODAY function always returns the current date. Inside the...Read more