Vertical lookup with a lookup value that returns multiple matches

  • Thread starter Thread starter andreashermle
  • Start date Start date
A

andreashermle

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






- Show quoted text -
Click to expand...

Hi John,

Great job. Exactly what I was looking for.

Thank you very much for your terrific help.

Regards, Andreas
 
Back
Top