=IF(ROW(A1)2,"",IF(ISERROR(SMALL($G$65:$G$69,ROW( A1))),"",INDEX($E$65:$E$69,MATCH(SMALL($G$65:$G$69 ,ROW(A1)),$G$65:$G$69,0))))

Can someone dicipher this please?

Thanks,

M.A.Tyler

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(12,"",
=IF(22,"",
=IF(32,"",

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(32, = TRUE)
blank (is blank because: =IF(ROW(A4)2 : =IF(42, = TRUE)
blank (is blank because: =IF(ROW(A5)2 : =IF(52, = TRUE)
etc

Biff

"M.A.Tyler" <Great Lakes State wrote in message
...
=IF(ROW(A1)2,"",IF(ISERROR(SMALL($G$65:$G$69,ROW( A1))),"",INDEX($E$65:$E$69,MATCH(SMALL($G$65:$G$69 ,ROW(A1)),$G$65:$G$69,0))))

Can someone dicipher this please?

Thanks,

M.A.Tyler