By default, XLOOKUP will return the first match in a data set. However, XLOOKUP offers an optional argument called search_mode to control the order in which data is searched. Setting search mode to -1 causes XLOOKUP to search "last to first" or, in other words, search backwards through...Read more
In this example, the goal is to perform a horizontal lookup to determine the correct quantity-based discount. The range G6:L7 contains quantity-based discounts. Column B contains random quantities used when testing the formula. This problem can be easily solved with the XLOOKUP function or...Read more
In the example shown, the table in B4:C13 contains quantity-based discounts. As the quantity increases, the discount also increases. The table in E4:F10 shows the discount returned by XLOOKUP for several random quantities. XLOOKUP is configured to use the quantity in column E to find the...Read more
In the example shown, cell G4 contains the lookup value, "Berlin". XLOOKUP is configured to find this value in the table, and return the population. The formula in G5 is:
=XLOOKUP(G4,B5:B18,D5:D18) // get population
One of the nice benefits of XLOOKUP is it can easily return entire rows or columns as a lookup result. This can be done also with INDEX and MATCH, but the syntax is more complex.
In the example shown, we want to retrieve all values associated with...Read more
In this example, the goal is to get the maximum value in the data for each month listed in column E. The easiest way to do this is with the MAXIFS function, which is designed to return a maximum value based on one or more criteria. In older versions of Excel without the MAXIFS function, you...Read more
In this example, the goal is to calculate the maximum value that occurs in a set of data on a given weekday (i.e. Monday, Tuesday, Wednesday, Thursday, Friday). In the current version of Excel, the simplest approach is to use the FILTER function. In older versions of Excel, you can use a...Read more
The Effective Annual Rate (EAR) is the interest rate after factoring in compounding. In other words, the EAR is the rate actually earned due to the effect of compounding more frequently than once a year (annually).
The...Read more
The FV function calculates compound interest and returns the future value of an investment over a specified term. To configure the function, we need to provide a rate, the number of periods, the periodic payment, and the present value: