What is the Best Way to Return Values from the Next Row in an Array in Excel?

Sometimes, you may want to reference the next row in an array when a logical test is false. For example, you have a table of data and you want to return the value from the first column that matches a certain condition in another column. If the condition is not met, you want to skip the current row and move to the next one.

One way to achieve this is to use the INDEX and MATCH functions together. The INDEX function returns a value from an array based on a given row and column number. The MATCH function returns the relative position of a value in an array that matches a given criterion.

By combining these two functions, you can reference the next row in an array when logic is false. The basic syntax is:

=INDEX (array,MATCH (TRUE,logical_test,0))

where:

• array is the range of cells that contains the values you want to return.
• logical_test is an array formula that evaluates to TRUE or FALSE for each row in the array.
• 0 is the match type that indicates an exact match.

Procedures

To use this formula, follow these steps:

1. Select a cell where you want to enter the formula.
2. Type =INDEX ( and select the range of cells that contains the values you want to return. For example, A2:A10.
3. Type a comma and then MATCH ( to start the MATCH function.
4. Type TRUE, and then enter an array formula that evaluates to TRUE or FALSE for each row in the array. For example, B2:B10="x". This formula checks if the value in column B is equal to “x”.
5. Type a comma and then 0 to indicate an exact match.
6. Type two closing parentheses to end the MATCH and INDEX functions.
7. Press Ctrl+Shift+Enter to enter the formula as an array formula. You should see curly braces around the formula in the formula bar.

The formula will return the value from the first column that matches the condition in the second column. If the condition is not met, it will skip the current row and move to the next one.

Explanation

The formula works by using the MATCH function to find the relative position of the first row that satisfies the logical test. The logical test is an array formula that returns an array of TRUE or FALSE values for each row in the array. For example, if the array is B2:B10 and the logical test is B2:B10="x", the array formula will return something like {FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE}.

The MATCH function then looks for the value TRUE in this array and returns its position. The match type 0 means that it will only return an exact match. For example, if the array formula returns {FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE}, the MATCH function will return 2, which is the position of the first TRUE value.

The INDEX function then uses this position to return the value from the first column of the array. The INDEX function takes the array and the row number as arguments and returns the value at that location. For example, if the array is A2:A10 and the row number is 2, the INDEX function will return the value in cell A3.

Example

Suppose you have a table of data like this:

You want to return the name of the first person who has the status “x”. If no one has the status “x”, you want to return a blank.

You can use the formula:

=INDEX (A2:A10,MATCH (TRUE,B2:B10="x",0))

This formula will return “Alice”, which is the name of the first person who has the status “x”. If you copy the formula down, it will return the names of the next persons who have the status “x”, such as “Charlie” and “Eve”. If there is no match, it will return an error.

To avoid the error, you can wrap the formula with the IFERROR function, which returns a custom value if the formula returns an error. For example, you can use this formula:

=IFERROR (INDEX (A2:A10,MATCH (TRUE,B2:B10="x",0)),"")

This formula will return a blank if there is no match.

Other approaches

There are other ways to reference the next row in an array when logic is false. Here are some alternatives:

• You can use the SMALL and IF functions together. The SMALL function returns the k-th smallest value in an array. The IF function returns a value based on a condition. For example, you can use this formula:

=INDEX (A2:A10,SMALL (IF (B2:B10="x",ROW (B2:B10)-ROW (B2)+1),ROW ()-1))

This formula will return the same result as the previous formula, but it works differently. The IF function returns an array of row numbers for the rows that match the condition. For example, if the array is B2:B10 and the condition is B2:B10="x", the IF function will return something like {2;FALSE;4;FALSE;6;FALSE;FALSE;FALSE;FALSE}.

The SMALL function then returns the k-th smallest value in this array, where k is the current row number minus 1. For example, if the current row is 2, the SMALL function will return 2, which is the first smallest value in the array.

The INDEX function then uses this value to return the value from the first column of the array.

This formula also needs to be entered as an array formula with Ctrl+Shift+Enter.

• You can use the AGGREGATE function, which performs calculations on a list of values, ignoring errors. The AGGREGATE function can perform different operations, such as sum, average, count, min, max, etc. For this case, you can use the operation 15, which is the SMALL function. For example, you can use this formula:

=INDEX (A2:A10,AGGREGATE (15,6,ROW (B2:B10)/(B2:B10="x"),ROW ()-1))

This formula will return the same result as the previous formulas, but it works differently. The AGGREGATE function performs the SMALL function on the array of row numbers divided by the logical test. The logical test returns an array of 1 or 0 for each row in the array. For example, if the array is B2:B10 and the logical test is B2:B10="x", the logical test will return something like {1;0;1;0;1;0;0;0;0}.

The division operation then returns an array of row numbers or errors for each row in the array. For example, if the array is ROW (B2:B10) and the logical test is B2:B10="x", the division operation will return something like {2;#DIV/0!;4;#DIV/0!;6;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}.

The AGGREGATE function then ignores the errors and returns the k-th smallest value in the array, where k is the current row number minus 1. For example, if the current row is 2, the AGGREGATE function will return 2, which is the first smallest value in the array.

The INDEX function then uses this value to return the value from the first column of the array.

This formula does not need to be entered as an array formula.