Formula to find whether or not a number falls between ranges listed in two columns

You can use a simple Excel formula to find whether or not a number falls between a range listed in two other columns on a worksheet.

=LOOKUP(2,1/((A5>=G:G)*(A5<=H:H)),I:I)

The LOOKUP formula looks up the value given in cell A5 first in column G to see if it is greater or equal to a value there, and then in column H to see if it is less than or equal to a value there. It then returns the value listed in the last column, which is here 'I', and is listed at the end of the formula.

Blocks charts in Excel for Office 365

The Tip of the Night for November 1, 2021 discussed how to create block charts in Excel. Don't miss that the Office 365 version of Excel includes an option to automatically create block charts or tree maps.

Select a data range and then on the Insert tab of the ribbon, select Maps . . . More Map Charts, and select Treemap. The parts that compromise the full block are proportionally sized based on the values entered in the chart.

XMATCH formula to get a position in an array

Don't miss the new XMATCH formula in the new Office 365 version of Excel. XMATCH will return the position in a list of the searched for value. So this formula:

=XMATCH(F2,$A$2:$A$11,0)

. . . will find the value in F2 in the array in column A between rows 2 and 10.

If you enter a 2 (instead of a 0 which looks only for an exact match) the XMATCH formula will be able to run a wildcard search if the searched for value ends with an asterisk.