Row 3 to row 2135 is 2,133 rows, so ...
Row(1:2133) is *all* that's needed ... short and sweet!
IMHO. that's much easier to explain to any OP!

If you want to use that method then you should also include the caveat that
inserting new rows can break the formula.

IMHO, using ...ROW($B$3:$B$2135)),ROWS(F$3:F3))-MIN(ROW(B$3:B$2135))+1...
both accounts for row insertions and is the most "user-proof" method. And,
as an added bonus, you don't have to figure out that ROW(27:93) is 67 rows
or ROW(1:67).

--
Biff
Microsoft Excel MVP


"RagDyeR" wrote in message
...
It's much more comprehensible, simpler, easier, and shorter, to just state
up front that the Row() function is simply creating buckets for the size
of
the array.

Row 3 to row 2135 is 2,133 rows, so ...
Row(1:2133) is *all* that's needed ... short and sweet!

IMHO. that's much easier to explain to any OP!
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"T. Valko" wrote in message
...
If the data doesn't start on row 1 then you have to calculate the offset
in
order to match the correct *relative* row number of the INDEX function.

That's what the problem was with the OP's formula:

Using the formula i have now, i am getting a result from two rows further
down on my list.
=INDEX($B$3:$D$2135,SMALL(IF($B$3:$D$2135=$AW$63 ,ROW($B$3:$D$2135)),ROW(1:1)),2)

If the data doesn't start on row 1 then you have to make the adjustment
to:

ROW($B$3:$D$2135)

So that the returned array corresponds to the "row positions" of the INDEX
function. The INDEX function starts at 1 while the ROW function starts at
3
so it'll be off by 2 as the OP noted.


--
Biff
Microsoft Excel MVP


"Ashish Mathur" wrote in message
...
Hi,

I have posted a solution to a similar problem at the following link

http://office.microsoft.com/en-us/ex...260381033.aspx

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Jay" wrote in message
...
hello and thanks to all. I have a question concerning a lookup with
mult
returns.
I have a listing that keys on column A and I want to list the
corresponding
values of columns B & C. Column A has multiples of the same "Key"

A B C
111 Contract 600
111 purchases 500
112 other costs 100
111 labor 200
112 Contract 500

any help will be greatly appreciated
--
Cheers!