Row() Question
Quite simply, it is comparing each cell in B5:B19 against the value in B21.
Where a match is found it outputs that row index number, otherwise it
outputs 20 ( a value greater than any row number).
SMALL is then used against that array of values to get the nth smallest row,
n being the value in C21. This nth smallest row index is used to INDEX into
range B5:B19 to get a value.
ROW(B5:B19)-ROW(B5)+1 is used to get the row indexes, rather than the rows,
(1,2,3 as against 5,6,7), as it is then used to INDEX into a range starting
at B5, not B1,
But it all seems pointless to me, it is getting the nth smallest row index
in a range, a range determined by a value, and then indexing into the same
range. In other words, the answer can only be the value in B21, or an error.
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
"mldancing" wrote in message
...
I am trying to understand this formula:
=INDEX(B5:C19,SMALL(IF(B5:B19=B21,ROW(B5:B19)-ROW(B5)+1,ROW(B19)+1),C21),2)
but am confused with the portion on
IF(B5:B19=B21,ROW(B5:B19)-ROW(B5)+1,ROW(B19)+1)
Can someone help to explain it please? Thank you very much.
|