Let's use this example:
............E.....G
65.......X.....10
66.......Y.....20
67.......Z.....15
68.......A.....17
69.......B.....21
When that formula is copied down a column it returns the value from column E
that corresponds to the nth smallest value in column G.
The first expression:
=IF(ROW(A1)>2,"",
Limits the number of values returned to 2. In the 3rd cell and each cell
the formula is copied to thereafter the result will be a blank cell. As you
drag copy the formula down a column the ROW(A1) function will incremnt like
this:
=IF(ROW(A1)>2,"",
=IF(ROW(A2)>2,"",
=IF(ROW(A3)>2,"",
This evaluates to:
=IF(1>2,"",
=IF(2>2,"",
=IF(3>2,"",
The second expression:
IF(ISERROR(SMALL($G$65:$G$69,ROW(A1))),"",
is used to catch any errors if there might not be a nth smallest number in
column G. For example, if column G contained only a single number then:
SMALL($G$65:$G$69,ROW(A2))
would generate a #NUM! error because there is no 2nd smallest number in
column G. The ROW(A1) function within the SMALL function incremnts just the
same way that I described above.
SMALL($G$65:$G$69,ROW(A1)) = 1st smallest
SMALL($G$65:$G$69,ROW(A2)) = 2nd smallest
SMALL($G$65:$G$69,ROW(A3)) = 3rd smallest
So, if:
IF(ROW(A1)>2 = FALSE *and*
IF(ISERROR(SMALL($G$65:$G$69,ROW(A1))) = FALSE
Then:
INDEX($E$65:$E$69,MATCH(SMALL($G$65:$G$69,ROW(A1)),$G$65:$G$69,0)))
Returns the value from column E that corresponds to the nth smallest value
in column G:
Based on the sample data above the results would be:
X (corresponds to 10 which is the 1st smallest value in column G)
Z (corresponds to 15 which is the 2nd smallest value in column G)
blank (is blank because: =IF(ROW(A3)>2 : =IF(3>2, = TRUE)
blank (is blank because: =IF(ROW(A4)>2 : =IF(4>2, = TRUE)
blank (is blank because: =IF(ROW(A5)>2 : =IF(5>2, = TRUE)
etc
Biff
