I cannot seem to get Mr. Bebans formula to work....I enter the simple formula
as he described in a cell. It is bringing back only the first occurance. My
question is, is it supposed to bring back a list (in the same cell) separated
by commas? Example...I just want to lookup a certain value in a 2 column
list...1st column is where the matching lookup value is and the second
column is where the state is. Now, there may be multiple occurances of
values with different states associated with it. I would like in one cell to
have something like: MN,TX,AZ. Is this possible? If not please explain how
to get the Beban formula to bring back the multiple occurances..do I need it
in multiple cells? multiple columns or rows?..Thanks!

Deeds wrote:
I cannot seem to get Mr. Bebans formula to work....I enter the simple formula
as he described in a cell. It is bringing back only the first occurance. My
question is, is it supposed to bring back a list (in the same cell) separated
by commas? Example...I just want to lookup a certain value in a 2 column
list...1st column is where the matching lookup value is and the second
column is where the state is. Now, there may be multiple occurances of
values with different states associated with it. I would like in one cell to
have something like: MN,TX,AZ. Is this possible? If not please explain how
to get the Beban formula to bring back the multiple occurances..do I need it
in multiple cells? multiple columns or rows?..Thanks!
The function itself is designed to return to a column the values
associated with the multiple occurrences of the lookup value. E.g.,
with 1,2,3,2,5,6,2,8,9,10 in A1:A10; and MN, TX, AZ in B2, B4, B7,
respectively, if you array enter into a 3-cell column

=VLookups(2,A1:B10,2)

then MN will be returned to the 1st cell, TX to the 2nd cell and AZ to
the 3rd cell. I.e., the function returns an array of the state values
corresponding to the occurrences of the lookup value, in this case 2.

Entered into a single cell, =INDEX(VLookups(2,a1:b10,2),n,1) will return
the state value corresponding to the nth occurrence of the lookup value.
So one way to get the result you are seeking is to enter into a cell
(using the example previously described)

=INDEX(VLookups(2,a1:b10,2),1,1)&","&INDEX(VLookup s(2,a1:b10,2),2,1)&","&INDEX(VLookups(2,a1:b10,2), 3,1)

Perhaps someone will suggest a more elegant (and generalized) solution.

Alan Beban

Thanks Alan! Based on the boards this one seems pretty tough. Your
suggestion of "&" them together works if I know the exact # of occurances.
However, I do not. I need a formula to look in a 2 column list, compare my
lookup value and return the data in column B that column A matches the lookup
value. Whether it occurs 10 times or 1 time...I just need a list in the same
cell of the results. This seems like it would be a pretty typical or common
need...I can't believe MS has not built something in that can handle
this.....Thanks again for your help....let me know if you have other ideas.
Thanks.

"Alan Beban" wrote:

Deeds wrote:
I cannot seem to get Mr. Bebans formula to work....I enter the simple formula
as he described in a cell. It is bringing back only the first occurance. My
question is, is it supposed to bring back a list (in the same cell) separated
by commas? Example...I just want to lookup a certain value in a 2 column
list...1st column is where the matching lookup value is and the second
column is where the state is. Now, there may be multiple occurances of
values with different states associated with it. I would like in one cell to
have something like: MN,TX,AZ. Is this possible? If not please explain how
to get the Beban formula to bring back the multiple occurances..do I need it
in multiple cells? multiple columns or rows?..Thanks!
The function itself is designed to return to a column the values
associated with the multiple occurrences of the lookup value. E.g.,
with 1,2,3,2,5,6,2,8,9,10 in A1:A10; and MN, TX, AZ in B2, B4, B7,
respectively, if you array enter into a 3-cell column

=VLookups(2,A1:B10,2)

then MN will be returned to the 1st cell, TX to the 2nd cell and AZ to
the 3rd cell. I.e., the function returns an array of the state values
corresponding to the occurrences of the lookup value, in this case 2.

Entered into a single cell, =INDEX(VLookups(2,a1:b10,2),n,1) will return
the state value corresponding to the nth occurrence of the lookup value.
So one way to get the result you are seeking is to enter into a cell
(using the example previously described)

=INDEX(VLookups(2,a1:b10,2),1,1)&","&INDEX(VLookup s(2,a1:b10,2),2,1)&","&INDEX(VLookups(2,a1:b10,2), 3,1)

Perhaps someone will suggest a more elegant (and generalized) solution.

Alan Beban

Hi,

The following procedure will give you the resukt in multiple rows of the
same column.

Assuming your data is in range A1:B7

Ashish 100
Sanjay 200
Pongal 300
Ashish 400
Rajesh 500
Suresh 600
Ashish 700

In A10, enter Ashish,

In B10, enter the following array formula (Ctrl+Shift+Enter)

=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$ 10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$ 7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1 )),2))

Now copy the formula down.

Regards,

Ashish Mathur

"Deeds" wrote:

I cannot seem to get Mr. Bebans formula to work....I enter the simple formula
as he described in a cell. It is bringing back only the first occurance. My
question is, is it supposed to bring back a list (in the same cell) separated
by commas? Example...I just want to lookup a certain value in a 2 column
list...1st column is where the matching lookup value is and the second
column is where the state is. Now, there may be multiple occurances of
values with different states associated with it. I would like in one cell to
have something like: MN,TX,AZ. Is this possible? If not please explain how
to get the Beban formula to bring back the multiple occurances..do I need it
in multiple cells? multiple columns or rows?..Thanks!