Excel returning more than one value from a lookup table that match the same value

[nmenefee]

I was following the instructions on http://office.microsoft.com/en-us/excel/HA012260381033.aspx
to return all the values that match a corresponding lookup value in a
table and I have gotten it working using their steps. The problem
I'm having is I need the formula to work when I fill it across the
colums instead of down the rows. I want the returned results to
appear on the same row horizonatally next to the lookup number instead
of in several rows beside it. I've been playing with the formula but
I cant seem to get it to cooperate. Anyone have any ideas.


=IF(ISERROR(INDEX(Sheet2!$C$2:$D$2934,SMALL(IF(Sheet2!$C$2:$C$2934=$C
$2,ROW(Sheet2!$C$2:$C$2934)),ROW(1:1)),2)),"",INDEX(Sheet2!$C$2:$D
$2934,SMALL(IF(Sheet2!$C$2:$C$2934=$C$2,ROW(Sheet2!$C$2:$C
$2934)),ROW(1:1)),2))

 

[Peo Sjoblom]

Since you are extracting values from D22934 there is no need to include C
in the index part then add 2 for the D column, also you need to offset the
INDEX part since row() will always count from the first row but if you
change INDEX to start in D1 instead it will work, something like this

=IF(ISERROR(INDEX(Sheet2!$D$1:$D$2934,SMALL(IF(Sheet2!$C$2:$C$2934=$C$2,ROW(Sheet2!$C$2:$C$2934)),COLUMN(A:A)))),"",INDEX(Sheet2!$D$1:$D$2934,SMALL(IF(Sheet2!$C$2:$C$2934=$C$2,ROW(Sheet2!$C$2:$C$2934)),COLUMN(A:A))))

note that it is not the best formula you can use for this, there are better
faster and more robust ways to get this


--


Regards,


Peo Sjoblom

 

[nmenefee]

Thanks doing it like that appears to work. Any tips on a better way
to do it. I was just going off that micorosft example.

-Nick

 

[Peo Sjoblom]

This formula is more robust, also entered with ctrl + shift & enter

=IF(COLUMNS($B1:B1)<=COUNTIF(Sheet2!$C$2:$C$2934,$C$2),INDEX(Sheet2!$D$1:$D$2934,SMALL(IF(Sheet2!$C$2:$C$2934=$C$2,ROW(Sheet2!$C$2:$C$2934)-COLUMN(Sheet2!$C$2)+3),COLUMNS($B1:B1))),"")


test them side by side

then select column A in the same sheet that holds the formulas and insert a
new column, every time you insert a new column the first formula will change
the result and eventually there will just be blank cells returned while the
above formula still returns the correct result, also it has less function
calls so it should be faster


--


Regards,


Peo Sjoblom