lookup with multiple returns

[Jay]

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

 

[Dave]

Hi,
If you sort by column A, will this will group all the data in the way you
want?
Dave.

 

[James]

Can you clarify- do you want to have multiple returns when there is
more than one value of B and C for a given value of A, or just one?


I am assuming you have another sheet with a list of A values- and you
want to get the coresponding B and C value(s)

 

[Jay]

What i need to have as a product is a list on another worksheet of the the
data that are in Columns B & C. in other words, I want it to find all the
"112"s in Column A and then place the data that is in the columns B & C. If I
used the Vlookup, it will only produce the data that is associated with the
first "112". 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)

Again, any assistance will be greatly appreciated, and sorry if I am a bit
hazy on presenting the problem. J

 

[T. Valko]

I want it to find all the "112"s in Column A

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

Try it like this:

Assume you enter the formula in cell F3:

=INDEX($C$3:$D$2135,SMALL(IF($B$3:$B$2135=$AW$63,ROW($B$3:$B$2135)),ROWS(F$3:F3))-MIN(ROW(B$3:B$2135))+1,COLUMNS($F3:F3))

Copy across to G3 then down until you get #NUM! errors.

Don't forget to array enter!

 

[Ashish Mathur]

Hi,

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

http://office.microsoft.com/en-us/excel/HA012260381033.aspx

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[T. Valko]

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.

 

[RagDyeR]

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 !
-----------------------------------------------------------------------------------------------

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.

 

[T. Valko]

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).