How to look up the next smaller item or the next larger item

The XLOOKUP function searches a range or an array for a match and returns the corresponding item from a second range or array. The syntax of the XLOOKUP function is as follows: = XLOOKUP ( lookup_value ; lookup_array ; return_array ; if_not_found ; match_mode ; search_mode ) The lookup value can be either a text or a number, inputted directly into the function or as a cell reference.   When looking up numbers, other than match type 0, exact match,

How to use less than or greater than MATCH

We can use the INDEX function to designate a cell range (array) from which we want to retrieve data and, at the same time, use the MATCH function to specify the address of a cell in that cell range (array), i.e., row and column.   The MATCH function returns the relative position of the item in the range, either a row or a column. The relative position is returned in the form of an integer number, such as 1, 2,

Combining FILTER with XLOOKUP

The FILTER function allows us to filter a range of data based on criteria defined for specified corresponding arrays. By combining FILTER with XLOOKUP, that corresponding array and/or filtering criteria can be returned from (un)related tables. We can FILTER, and in the end, SUM, values based on criteria that are not present in the table we are returning values from.   In the following example, we will show how to use XLOOKUP in order to return filtering criteria: This table

Combining FILTER with INDEX MATCH

The FILTER function allows us to filter a range of data based on criteria defined for specified corresponding arrays. By combining FILTER with INDEX MATCH, that corresponding array and/or filtering criteria can be returned from (un)related tables. We can FILTER, and in the end, SUM, values based on criteria that are not present in the table we are returning values from.   In the following example, we will show how to use INDEX MATCH in order to return filtering criteria:

UNIQUE function

The UNIQUE function replicates and extends Excel’s Remove Duplicates feature in formula form. The UNIQUE function allows us to return a list of unique values in a list or range. By default, the UNIQUE function will return multiple values, which will be placed in the neighboring cells (to the bottom and/or to the right). I.e., we are dealing with dynamic array formulas here.   The syntax of the UNIQUE function is as follows: = UNIQUE ( array ; [by_col] ;

SUMIFS criteria: INDEX MATCH or XLOOKUP criteria

Using the SUMIFS function, we can sum all of the values in a defined column (or row) that meet one or more criteria. SUMIFS criteria can reference cells, contain values or text, contain logical tests, or contain formulas and functions. By nesting the INDEX MATCH combination or the XLOOKUP function as SUMIFS criteria, we can return values based on criteria that are not present in the table we are returning values from.   Consider the following example: This table contains (repeating)

Lookup the 2nd, the 3rd, or the nth value

Both the MATCH function and the XLOOKUP function look only for the first (last) available match in the array. This is perfectly reasonable in most cases, as we are expecting to deal with unique identifiers when looking up data. However, sometimes we will have to look up the second, third, fourth, or nth value.   Consider the following example: Our table contains a list of contract numbers in column B and contact email addresses in column A. Contract numbers are

Lookup with multiple criteria

If we have to look up items in tables where we can’t use unique identifiers (there are no names located in a table column that contains data in all of the rows, and that data is non-repeating), we will probably have to resort to matching multiple criteria in multiple columns.   Consider the following example: Each year, multiple game tournaments are held in repeating cities, organized by various repeating organizers. Items in the column City are not unique, nor are

Combining SUMIFS with XLOOKUP

Using the SUMIFS function, we can sum all of the values in a defined column (or row) that meet one or more criteria. When SUMIFS is combined with XLOOKUP, that sum range doesn’t have to be defined anymore; it is now rather specified in the function arguments. By combining SUMIFS with XLOOKUP, we can then sum all of the values that meet multiple criteria in different rows and columns and do this in a simple way, avoiding complex and resource-intensive

XLOOKUP function

We’ve previously covered how we can combine INDEX and MATCH functions in order to “look up” data. The XLOOKUP function works much the same way as the INDEX MATCH combination. It is intended to replace (still wildly used) legacy functions with outdated syntax and limited capabilities, such as VLOOKUP, HLOOKUP, and LOOKUP, while retaining accessibility.   The XLOOKUP function searches a range or an array for a match and returns the corresponding item from a second range or array. The