Vertical lookup with a lookup value that returns multiple matches
 

Dear Experts:

I would like to perform a vertical lookup with a lookup value that has
multiple matches.
I know that the VLOOKUP function is designed to return only the
corresponding value of the first instance of a lookup value. I would
like to get a work-around that identifies multiple
matches
Example: In cases where the lookup value (Number of Points) occurs
several times, I would like to return all the results for this lookup
value e.g.:
Points Name
2 Smith
1 Gordon
3 John
2 Holten


Looking up '2 Points' should result in:
Smith
Holten

The result should list the corresponding values in one cell with
manual line breaks (ALT+Enter)
I hope this is feasible. Thank you very much in advance for your
professional help.
Regards, Andreas

Vertical lookup with a lookup value that returns multiple matches
 

Separate cells is doable with a formula.

--

HTH

Bob

"andreashermle" wrote in message
...
Dear Experts:

I would like to perform a vertical lookup with a lookup value that has
multiple matches.
I know that the VLOOKUP function is designed to return only the
corresponding value of the first instance of a lookup value. I would
like to get a work-around that identifies multiple
matches
Example: In cases where the lookup value (Number of Points) occurs
several times, I would like to return all the results for this lookup
value e.g.:
Points Name
2 Smith
1 Gordon
3 John
2 Holten


Looking up '2 Points' should result in:
Smith
Holten

The result should list the corresponding values in one cell with
manual line breaks (ALT+Enter)
I hope this is feasible. Thank you very much in advance for your
professional help.
Regards, Andreas

Vertical lookup with a lookup value that returns multiple matches
 

Option Explicit
Sub SAS_filternumbersfornames()
Dim mv As Long
Dim mc As Long
Dim i As Long
Dim ms As String
mv = 2' numberf to look for
mc = 6 'col F
For i = 2 To Cells(Rows.Count, mc).End(xlUp).Row
If Cells(i, mc) = mv Then ms = ms & Cells(i, mc + 1) & Chr(10)
Next i
'MsgBox ms
Cells(1, mc + 2) = ms
End Sub


On Jun 13, 5:02*pm, andreashermle wrote:
Dear Experts:

I would like to perform a vertical lookup with a lookup value that has
multiple matches.
I know that the VLOOKUP function is designed to return only the
corresponding value of the first instance of a lookup value. I would
like to get a work-around that identifies multiple
matches
Example: In cases where the lookup value (Number of Points) occurs
several times, I would like to return all the results for this lookup
value e.g.:
Points Name
2 * * * * Smith
1 * * * * Gordon
3 * * * * John
2 * * * * Holten

Looking up '2 Points' should result in:
Smith
Holten

The result should list the corresponding values in one cell with
manual line breaks (ALT+Enter)
I hope this is feasible. Thank you very much in advance for your
professional help.
Regards, Andreas

Vertical lookup with a lookup value that returns multiple matches
 

On Jun 14, 2:35*pm, Don Guillett Excel MVP
wrote:
Option Explicit
Sub SAS_filternumbersfornames()
Dim mv As Long
Dim mc As Long
Dim i As Long
Dim ms As String
mv = 2' numberf to look for
mc = 6 'col F
For i = 2 To Cells(Rows.Count, mc).End(xlUp).Row
If Cells(i, mc) = mv Then ms = ms & Cells(i, mc + 1) & Chr(10)
Next i
* 'MsgBox ms
Cells(1, mc + 2) = ms
End Sub

On Jun 13, 5:02*pm, andreashermle wrote:



Dear Experts:

I would like to perform a vertical lookup with a lookup value that has
multiple matches.
I know that the VLOOKUP function is designed to return only the
corresponding value of the first instance of a lookup value. I would
like to get a work-around that identifies multiple
matches
Example: In cases where the lookup value (Number of Points) occurs
several times, I would like to return all the results for this lookup
value e.g.:
Points Name
2 * * * * Smith
1 * * * * Gordon
3 * * * * John
2 * * * * Holten

Looking up '2 Points' should result in:
Smith
Holten

The result should list the corresponding values in one cell with
manual line breaks (ALT+Enter)
I hope this is feasible. Thank you very much in advance for your
professional help.
Regards, Andreas- Hide quoted text -

- Show quoted text -

Hi John,

Great job. Exactly what I was looking for.

Thank you very much for your terrific help.

Regards, Andreas