This can be accomplished with the FILTER function and the INDEX function.
FILTER function
TheFILTER function"filters" data based on a logical test and extracts matching records.
Theif_emptyparameter specifies the value to return if no matches are found.
Note that by itself, FILTER returns all matching data.
INDEX function
TheINDEX functionreturns the value at a given location in a range or array.
you’ve got the option to use INDEX to retrieve individual values, or entire rows and columns.
Simplifying, the original formula becomes:
And INDEX returns row 5 from the data.
The output from FILTER is dynamic.
If source data or criteria change, FILTER will return a new set of results.
you might use INDEX to retrieve individual values, or entire rows and columns.
The rows to return are provided as numbers in separate arguments.
Each number corresponds to the numeric index of a row in the given array.
from a set of numeric data.
More than one condition can be tested by nesting IF functions.
The MIN function ignores empty cells, the logical values TRUE and FALSE, and text values.
ROW Function
The Excel ROW function returns the row number for a reference.
For example, ROW(C5) returns 5, since C5 is the fifth row in the spreadsheet.
When no reference is provided, ROW returns the row number of the cell which contains the formula.
