Lookup the Smallest Value in a 2D Array in Google Sheets

We can use a combination of BYROW and SMALL with FILTER or XLOOKUP to look up the smallest value in a 2D array in Google Sheets.

What does looking up the smallest value in a 2D array mean?

Assume you have sales dates in a column (range A2:A11) and sales quantities from different regions in the next few columns (B2:E11). The task is to find the first, second, and third smallest values from the range B2:E11 and return the corresponding dates from A2:A11.

Lookup the smallest value in a 2D array in Google Sheets — Lookup the smallest value in a 2D array

We will discuss how to achieve this in this Google Sheets tutorial.

Step 1: Find the Smallest Value in a 2D Array

To find the smallest value in the range B2:E11, use the following SMALL formula in Google Sheets:

=SMALL(B2:E11, 1)

Enter this formula in cell G2.

Finding the nth smallest value in a 2D array

To find the second smallest value, replace 1 with 2. For the third smallest value, replace 1 with 3, and so on.

For this example, we will look up the first smallest value in the 2D array. The formula will return 50. If you manually check the range, you’ll see this value is present in cells D3 and C9.

How do you find the corresponding dates?

Step 2: Match the Smallest Value in Each Row

Next, compare the smallest value in the 2D array with the smallest value in each row. Use the following formula in cell H2 and drag it down to H11:

=$G$2=SMALL(B2:E2, 1)

If the formula returns TRUE, it indicates a match. Therefore, you should look up TRUE to identify the corresponding dates.

Step 3: Lookup the Smallest Value in a 2D Array (Using TRUE for Match)

To look up the smallest value, use FILTER or XLOOKUP.

XLOOKUP will return the date corresponding to the first occurrence of the smallest value, while FILTER can return the dates of all occurrences.

In cell I2, enter the following XLOOKUP formula to get the date corresponding to the smallest value:

=XLOOKUP(TRUE, H2:H11, A2:A11) // returns 15/09/2024

XLOOKUP searches for the key TRUE in H2:H11 and returns the corresponding date from A2:A11.

Alternatively, you can use FILTER:

=FILTER(A2:A11, H2:H11) // returns 15/09/2024 and 21/09/2024

FILTER returns the dates from A2:A11 where the condition in H2:H11 is TRUE.

Lookup the Smallest Value in a 2D Array with an Array Formula

You can use the following approach to avoid using helper ranges and prefer a single formula to look up the nth smallest value in a 2D array.

This method eliminates the need for the helper range H2:H11 in both XLOOKUP and FILTER.

You can use the BYROW and SMALL combination to replace H2:H11 in the formulas:

BYROW(B2:E11, LAMBDA(r_range, SMALL(B2:E11, 1)=SMALL(r_range, 1)))

The BYROW function applies a custom lambda function to each row in the range.

SMALL(B2:E11, 1)=SMALL(r_range, 1) checks if the smallest value in B2:E11 (the 2D array) is equal to the smallest value in each row.

Here, SMALL(B2:E11, 1) is the smallest value in the entire 2D array, and SMALL(r_range, 1) is the smallest value in the current row. r_range represents the current row within B2:E11, and BYROW iterates through each row, adjusting r_range accordingly.

To lookup the smallest value in a 2D array, use the following formulas:

XLOOKUP:

=XLOOKUP(
   TRUE, 
   BYROW(B2:E11, LAMBDA(r_range, SMALL(B2:E11, 1)=SMALL(r_range, 1))), 
   A2:A11
)

FILTER:

=FILTER(
   A2:A11, 
   BYROW(B2:E11, LAMBDA(r_range, SMALL(B2:E11, 1)=SMALL(r_range, 1)))
)

Replace 1 with 2, 3, or the nth position you want to find.

Case-Sensitive Running Count in Google Sheets

Fix Fractional Percentage Formatting Issues in Google Sheets