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!