Index+small+row+if Classical Function combination Application

The INDEX+SMALL+ROW+IF function combination in Excel can detect all records that meet the same criteria, and is explained by an example:

This article is original, reproduced need to indicate the source, thank you!

Example: Find out all classes and numbers in the first year:

A	B	C	D
1	Grade	Class	Number
2	First-year	Class Two	36
3	First-year	Class Three	38
4	Second Grade	Class One	39
5	First-year	Class One	41
6	Second Grade	Class Three	38
7	Third Grade	Class One	42
8	Third Grade	Class Three	37
9	First-year	Class Four	40

First step: if ($B $: $B $9= "First grade", Row ($2:$9), 10^10): If there is a cell content in the B2-B9 in the first grade, the number of rows (2,3,5,9) is returned, otherwise 10*10 (this is mainly the direction of the large number of values, You'll understand when you talk about small later);

Step Two: SMALL (IF ($A $: $A $9= $D $2,row ($2:$9), 10^10), row (1:1)): The function of the SMALL function is to return the minimum value of K, SAMLL ((2,3,5,9), row (1:1)), row (1:1) function for the selection of the K-small value;

Step Three: Index (A:a,small (IF ($A $: $A $9= $D $2,row ($2:$9), 10^10), ROW (1:1)): The index function takes out the results that match the criteria and confirms by Shif+ctrl+enter

Grade	Class	Number
First-year	Class Two	36
First-year	Class Three	38
First-year	Class One	41
First-year	Class Four	40

Index+small+row+if Classical Function combination Application