VlookupS-Alan Beban

[Deeds]

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!

 

[Alan Beban]

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(VLookups(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

 

[Deeds]

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.

 

[Ashish Mathur]

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]

Thank you all for the ideas....however, to get multiple items to show up in
the same cell...I found some code that works great!...Anyone else, feel free
to give this a try...it does exactly what I would want it to do. Pretty
nice! I have to give credit to NateO from Mr.Excel for this one.
Paste the code in a module, then enter the formula in a cell within the
file...adjust the formula to match your layout.
Here's the code: (the formula follows below)

Public Function foo( _
ByRef rngIn As Range, ByRef nameIn As String) As String

Dim tmpArr() As Variant

With WorksheetFunction
Let tmpArr = .Transpose( _
Evaluate("" & rngIn.Columns(1).Address & "&" _
& rngIn.Columns(2).Address & ""))
Let foo = .Substitute(Join(Filter( _
tmpArr, nameIn), ", "), nameIn, vbNullString)
End With
End Function

Here's the formula:
=foo(A2:B10,D2)

 

[nathan.oliver]

Hello,

You might want to consider foo2() instead for the reasons I provide in
my next post, in the original thread:

http://mrexcel.com/board2/viewtopic.php?p=855666#855666

And, I suppose I can go ahead and answer your question you posted
there, will do so.

Regards,
Nate Oliver

I have to give credit to NateO from Mr.Excel for this one.
Paste the code in a module, then enter the formula in a cell within the
file...adjust the formula to match your layout.
Here's the code: (the formula follows below)

Public Function foo( _
ByRef rngIn As Range, ByRef nameIn As String) As String

{snipped}