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.

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.

OT here ... You should always post a closure to discussions in your threads.
Eg: Sumproduct Question
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thanks for the tips, Bob!

I will go through it and try to digest the information.


"Bob Phillips" wrote:

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.