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