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 …
Tag: SMALL function
Top 10 lists in Excel
When analyzing large amounts of data in Excel, often the best approach is to retrieve the top (10) values. Consider the following example: This table contains all of the invoices from January 29th. There are 904 invoices for that day. The values in the invoice column are unique, as those are invoice numbers. The values in the user column are not unique, as some users have multiple invoices on that day, and the values in the team column are not …
SMALL & LARGE functions
The SMALL function returns the k-th smallest number from an array of numbers. The SMALL function syntax is as follows: = SMALL ( array; k ) Array can be any row, column, or a combination of both. k is an integer number representing the position of a number in the array if data in that array would be sorted from the smallest. Consider the following example: If k is 1, the smallest number will be returned. If k is …